Getting the SQL_ID for a statement

A while back while I was teaching a class in Dallas, I was chatting with Carlos Sierra about the fact that I was disappointed there wasn’t a function in Oracle to return a SQL_ID for a statement.  Yes, I can get it AFTER I run a statement, but that is kind of closing the door after the horse has escaped. 

I wanted to know the statements SQL_ ID before I ran it so I could easily monitor it.  Carlos wrote up a nifty little routine to do it and you can find it here.

Apparently I was just a little ahead of the curve.  Now in 12c there is a new package called DBMS_SQL_TRANSLATOR.   In it are two routines of interest.

FUNCTION SQL_HASH RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN

FUNCTION SQL_ID RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN

These do pretty much what they say, the return the SQL_ID and the HASH_VALUE for a given statement.  Just a little test to show this:

SQL> DECLARE
  2    sqltext CLOB;
  3    sqlid   VARCHAR2(13);
  4    sqlhash number;
  5  BEGIN
  6    sqltext :=  'SELECT * FROM DUAL';
  7    sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (sqltext);
  8    sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (sqltext);
  9    dbms_output.put_line ('SQL ID is '||sqlid);
 10    dbms_output.put_line ('SQL HASH is '||to_char(sqlhash));
 11  END;
 12  /
SQL ID is 9g6pyx7qz035v
SQL HASH is 3991932091
SQL>
SQL> SELECT * FROM DUAL;

D
-
X

SQL>
SQL> select SQL_ID, HASH_VALUE from v$sql where sql_text like 'SELECT * FROM DUAL';
SQL_ID             HASH_VALUE
------------- ---------------
9g6pyx7qz035v      3991932091

And I want to say a big thanks to Gary Propeck for pointing these out to me!