RE: Can you execute a function in an Oracle comment/hint?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 4 May 2007 13:25:40 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45071FDC0F@NT15.oneneck.corp>

This isn't exactly what I had in mind, and I think it will only work in SQL*Plus, but it's getting close:

SQL> alter session set nls_date_format = 'DD-MON-YY hh24:mi:ss';

Session altered.

SQL> select /*+ &_DATE */ 1 from dual;
old 1: select /*+ &_DATE */ 1 from dual new 1: select /*+ 04-MAY-07 13:20:25 */ 1 from dual

SQL> select hash_value, sql_text from v$sql where sql_text like '%1 from dual';

HASH_VALUE SQL_TEXT

2312264275 select /*+ 04-MAY-07 13:20:25 */ 1 from dual

SQL> select /*+ &_DATE */ 1 from dual;
old 1: select /*+ &_DATE */ 1 from dual new 1: select /*+ 04-MAY-07 13:24:25 */ 1 from dual

SQL> select hash_value, sql_text from v$sql where sql_text like '%1 from dual';

HASH_VALUE SQL_TEXT

2312264275 select /*+ 04-MAY-07 13:20:25 */ 1 from dual 4293063447 select /*+ 04-MAY-07 13:24:25 */ 1 from dual

This could also be done with _USER, or any of the other 8 predefined variables, as described below, but _DATE is the only one that can give a unique hash_value every time:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12 017.htm#sthref2145

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 04 2007 - 15:25:40 CDT