Friday, August 18, 2017

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!!

1 comment: