UPDATE! Getting the SQL_ID in the cloud or not

A few years back I post a block of code that would take as input a sql file with one sql statement in it and return the SQL_ID and HASH_VALUE using DBMS_SQL_TRANSLATOR.  The post is here.  It turns out there was a pretty big flaw in that code.  It was assuming there would only be one slash (/) at the end of the statement.  Ack!  Of course if you use the /*+ for hints or /* for a comment, then it would trim off the file at the first slash it found which is clearly wrong.

So here is the new code:

set termout on heading off feedback off verify off

-- File name hgetsqlid.sql

-- Get the SQLID/HASH for a SQL statement in a file

-- The file must contain only ONE SQL statement

-- The Directory "SQL_FILE_LOCATION" must be defined

-- This is the location where this will read the file from

-- Example

-- CREATE OR REPLACE DIRECTORY SQL_FILE_LOCATION AS '/home/oracle/OP';

-- The file MUST end with a "/" on last line

-- Example:

-- Select * from emp

-- /

--

-- May 2015 RVD initial coding

-- Aug 2017 RVD fixed issue with finding the / in commment or hint would trim the file too short

--              some formating and other minor changes

set tab off

set serveroutput on

column directory_path format a100 wrap

prompt *********************************************

prompt Get SQL_ID and HASH VALUE for a SQL statement

prompt One statement in the file and must end with /

prompt Current setting of SQL_FILE_LOCATION:

prompt *********************************************

select directory_path from dba_directories where directory_name = 'SQL_FILE_LOCATION';

prompt *********************************************

accept hgetsqlid_file prompt 'Enter the full file name (with extension): '

DECLARE

    v_bfile BFILE;

    v_clob  CLOB;

    v_sqlid VARCHAR2(13);

    v_sqlhash number;

    v_slash integer := 0;

    e_noslash exception;

BEGIN

    v_bfile := BFILENAME ('SQL_FILE_LOCATION', '&hgetsqlid_file');

    IF DBMS_LOB.FILEEXISTS (v_bfile) = 1 THEN

        DBMS_LOB.OPEN (v_bfile);

        DBMS_LOB.CREATETEMPORARY (v_clob, TRUE, DBMS_LOB.SESSION);

        DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));

        DBMS_LOB.CLOSE (v_bfile);

        -- remove all carrage returns (ASCII 13) from the clob

        -- each line must end with only a line feed (ASCII 10)

        v_clob := replace(v_clob, CHR(13) , '');

        -- trims off training spaces at the end of the file

        v_clob := rtrim(v_clob);

        -- trim off anything else at the end back to the /

        while (dbms_lob.substr(v_clob,1,(DBMS_LOB.GETLENGTH (v_clob)))) <> '/'

          loop

           DBMS_LOB.TRIM (v_clob,(DBMS_LOB.GETLENGTH(v_clob))-1);

          end loop;

        -- remove any trailing spaces or tabs (ASCII 9)

        while DBMS_LOB.INSTR (v_clob, ' '||CHR(10)) > 0 or

              DBMS_LOB.INSTR (v_clob, CHR(9)||CHR(10)) > 0

             loop

             v_clob := replace(v_clob, ' '||CHR(10), CHR(10));

             v_clob := replace(v_clob, CHR(9)||CHR(10), CHR(10));

             end loop;

        -- Find the / at the end of the file

        v_slash := DBMS_LOB.INSTR (v_clob,'/',DBMS_LOB.GETLENGTH(v_clob));

        IF v_slash = 0 THEN RAISE e_noslash; END IF;

        -- remove the line with the slash and everything after it

        DBMS_LOB.TRIM (v_clob,v_slash-2);

        v_sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (v_clob);

        v_sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (v_clob);

        dbms_output.put_line ('*************************');

        dbms_output.put_line ('The SQL ID is '||v_sqlid);

        dbms_output.put_line ('Hash value is '||v_sqlhash);

        dbms_output.put_line ('*************************');

    ELSE

        dbms_output.put_line ('** File not found **');

    END IF;

    EXCEPTION

        when e_noslash then

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

        dbms_output.put_line ('Slash not found!');

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

END;

/

set serveroutput off

set heading on

And an example of using the code:

SQL> @hgetsqlid

*********************************************

Get SQL_ID and HASH VALUE for a SQL statement

One statement in the file and must end with /

Current setting of SQL_FILE_LOCATION:

*********************************************

C:\OP

*********************************************

Enter the full file name (with extension): with.sql

*************************

The SQL ID is 2n63z3ab978kn

Hash value is 2526257748

*************************

SQL> @with

       COUNT(*)

---------------

          75875

          81280

SQL> select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid');

PREV_SQL_ID   PREV_CHILD_NUMBER

------------- -----------------

2n63z3ab978kn                 0

SQL>

SQL> get with

  1  select /*+ qb_name(allobjs) */ count(*)

  2    from withlab_allobjects a,

  3  (select /*+ qb_name(owners1) */distinct owner username

  4     from withlab_allobjects ) owners

  5   where a.owner = owners.username

  6  union all

  7  select /*+ qb_name(dbaobjs) */ count(*)

  8    from withlab_dbaobjects d,

  9  (select /*+ qb_name(owners2) */distinct owner username

 10     from withlab_allobjects ) owners

 11*  where d.owner = owners.username

SQL>

Enjoy!!