How to get SQL_ID without execution?

 

How to get SQL_ID without execution?

How to find the SQL_ID of a SQL 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

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

10-FEB-23 06:21:07

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 .

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.

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

  declare

  p_id varchar2(1000);

  begin

  p_id:=dbms_sql_translator.sql_id('select sysdate from dual');

   dbms_output.put_line(p_id);

  end;

7h35uxf5uhmm1

PL/SQL procedure successfully completed.

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