Getting the SQL_ID/HASH_VALUE with DBMS_SQL_TRANSLATOR

This little script will read in a SQL file and return to you the SQL_ID and HASH_VALUE of a statement without running it.  It uses DBMS_SQL_TRANSLATOR.SQL_ID and .SQL_HASH to get these values.  The tricky part was to make sure the file is in the same format as what the optimization process uses when computing these values.  Key is that the file is in UNIX format, meaning there is only a linefeed at the end of each line.  Also the optimizer strips out trailing spaces or tabs (white space) before calculating these values as well.  

It was this second one that took me awhile to figure out.  I didn’t know at first that the optimizer was doing this.  Once I knew what was going on it was rather simple to fix.  (Isn’t that always the case?  It’s finding the real problem that is the problem!)  My solution is rather brute force, but I didn’t want to truly “parse” the statement, as in go thru it character by character.   So I instead just look for a space or tab with a line feed after it, if there is at least one of either I replace “both” thru out the entire CLOB.  Like I said not eloquent but it gets the job done.  Part of the issue was I also had to leave the spacing alone inside a line.  The optimizer only strips off the white space at the end of the line, not within the line. 

The PL/SQL engine is different; it reduces ALL white space to a single space and removes all line feeds.  Hence it turns your SQL statement into one long string.   But it does all this before it sends the SQL to the optimizer.  I’m not trying to mimic the PL/SQL engine with this script.

So here it is.  If your find any flaws with this please let me know.  I’ve tested it on several statements and on UNIX and Windows, so far it has been correct with each test.  If you like this and use this please keep the header information to give me credit for it is all I ask.  Thanks!

For those who were at the Great Lakes Oracle Conference this week, #GLOC15, here is the working version I told you I was still working on in my session about execution plans.

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

-- Exmple

-- Select * from emp

-- /

--

-- May 2015 RVD initial coding

--

set termout on

set tab off

set verify off

set serveroutput on

column directory_path format a100 wrap

prompt Current setting of SQL_FILE_LOCATION:

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;

    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) , '');

        -- 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,'/');

        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;

/

Here is an example:

SQL> get sqlid_test.sql

  1  SELECT e.employee_id, e.last_name, j.job_title, d.department_name

  2  FROM employees e, jobs j,   departments d

  3  WHERE e.department_id IN (10,20)

  4  AND j.job_id = e.job_id

  5  AND e.department_id = d.department_id

  6  ORDER BY e.last_name

  7* /

SQL>

SQL> @hgetsqlid

Current setting of SQL_FILE_LOCATION:

DIRECTORY_PATH

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

C:\OP

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

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

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

The SQL ID is ap3yukgck02pd

Hash value is 3642755757

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

SQL> select sql_id, hash_value from v$sql where sql_text like 'SELECT e.employee_id, e.last_name,%';

SQL_ID             HASH_VALUE

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

ap3yukgck02pd      3642755757

SQL>