How to find the SQL_ID of a SQL statement? This is a question that comes up again and again in my practice. About a couple of years ago I already wrote a German blog posting about it. (If you know German you can find it here :)) Because I still receive this kind of question – here is the English version of my posting that you may use. Actually you may use this solution regardless of the Oracle database version you are developing and working with.
In fact there is a wide range of possible solutions to answer the question how to get the SQL_ID of a statement. You can find the SQL_ID in AWR or ASH reports or query V$ views like V$SQL etc. One interesting recent enhancement of the Oracle tool SQL*Plus with Oracle Database 18c (and later of course) even provides a very simple solution. With “SET FEEDBACK ON SQL_ID” the SQL_ID can be printed automatically after the statement execution. The following code gives an example:
SQL> set feedback on SQL_ID SQL> select sysdate from dual; SYSDATE -------------------------- 04-May-2022 09:48:03 1 row selected. SQL_ID: 7h35uxf5uhmm1
The SQL_ID is assigned to the predefined SQL*Plus variable _SQL_ID. This variable can be used like any other predefined variable, as you can see in the following example.
SQL> SELECT sql_text FROM v$sql WHERE sql_id = '&_sql_id'; old 1: SELECT sql_text FROM v$sql WHERE sql_id = '&_sql_id' new 1: SELECT sql_text FROM v$sql WHERE sql_id = '7h35uxf5uhmm1' SQL_TEXT -------------------------------------------------------------------------------- select sysdate from dual
However what is the answer to the initial question: Is there a way to get the SQL_ID of a statement WITHOUT executing the statement first? Also for this there is a solution. Try out the PL/SQL Package DBMS_SQL_TRANSLATOR – it dows the trick!
What is DBMS_SQL_TRANSLATOR used for? Let me give some brief background information about it: Various client-side applications, designed to work with non-Oracle Databases, cannot be used with Oracle Database without significant alterations because different syntaxes are used to express SQL queries and statements. Starting with Oracle Database 12c, there is a mechanism called SQL translation framework which translates the SQL statements of a client program from a foreign (non-Oracle) SQL dialect into the SQL dialect used by the Oracle Database SQL compiler.
The package DBMS_SQL_TRANSLATOR provides the interface to create, configure and use so called SQL translation profiles. If you check the related functions and procedures in the documentation, you will find a function called SQL_ID, which calculates the SQL_ID of an SQL statement in an SQL translation profile. It can be used without a translation profile and calculates the corresponding SQL_ID for an SQL statement with a single call.
And here is an example:
SQL> set serveroutput on
SQL> r
1 declare
2 p_id varchar2(1000);
3 begin
4 p_id:=dbms_sql_translator.sql_id('select sysdate from dual');
5 dbms_output.put_line(p_id);
6* end;
7h35uxf5uhmm1
PL/SQL procedure successfully completed.
Done!
Note: if you use the Oracle commandline tool SQLcl and the special ALIAS command you can even define your own command with it. Thanks to my colleague Norbert Leiendecker for this addition!
And here is the example to create a new command getid with the help of ALIAS.
SQL> alias getid=declare 2 p_id varchar2(1000); 3 argu varchar2(1000); 4 begin 5 p_id:=dbms_sql_translator.sql_id(:argu); 6 dbms_output.put_line(p_id); 7 end; 8* / SQL> set serveroutput on SQL> getid 'select sysdate from dual' 7h35uxf5uhmm1
By the way, if you search a bit the internet, you will find other examples – written in python or PL/SQL to compute the SQL_ID out of the SQL_TEXT.