Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 07, 2021 - 5:56 am UTC

Last updated: January 07, 2021 - 3:54 pm UTC

Version: 12.1

You Asked

Hi Chris, Connor,

I have an application SQL written in below format

select  /* Q_ORDERS_001 */ order_id from orders;


However, when I queried gv$sql to see the sql information, I couldn't find the excat match of the sql.
In gv$sql I got an entry with the string select order_id from orders;

Could you please help to understand the reason why /* Q_ORDERS_001 */ was omitted.

and we said...

By any chance did you run this statement in PL/SQL?

This canonicalizes SQL, which removes unnecessary whitespace, standardizes case, and removes comments:

create table t (
  c1 int
);

begin
  for rws in (
    select /* plsql */* from t plsql
  ) loop
    null;
  end loop;
end;
/

select sql_text from v$sql
where  lower ( sql_text ) like 'select%'
and    lower ( sql_text ) like '%t plsql%'
and    sql_text not like '%v$sql%';

SQL_TEXT                 
SELECT * FROM T PLSQL 


Or is it possible that the commented version of hasn't been run "for a while" so is no longer cached, but the uncommented version was run recently?

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.