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

Thanks Jack, but unfortunately I can't use dynamic SQL in my situation. My problem is one of bind variable peeking and inappropriate plan sharing for a COTS app (Baan ERP). Baan provides a couple of undocumented parameters for overriding their standard hints and injecting your own, so I'm trying to inject a text string into the comment/hint section of certain queries that are having this problem, but rather than injecting a simple comment or hint, I'd like to inject a function that would cause every execution of the query to have a different hash value and therefore be hard parsed into its own cursor, having its bind variables peeked at to enable proper optimization and the most efficient explain plan.  

I thought for sure I saw someone, on this list I think, provide a way of doing this a few months ago. IIRC, they were also using it to inject something into the queries of a COTS app, maybe Crystal Reports, and I think they were using it to identify where the queries were coming from. I'll continue my search and let y'all know if I find it.  

From: JApplewhite_at_austinisd.org [mailto:JApplewhite_at_austinisd.org] Sent: Thursday, May 03, 2007 7:12 PM
To: Allen, Brandon
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org Subject: Re: Can you execute a function in an Oracle comment/hint?

Couldn't leave it alone. This works:
Begin
Execute Immediate 'Select /* ' ||
sys_context('USERENV','SESSION_USER')|| ' */ SysDate From Dual ' ; End ;
/

Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk) / 512.935.5929 (pager)

I'll just sit back in the shade while everyone gets laid that's what I call 'intelligent design'. -- God ("Origin of Species": Chris Smither)

Jack Applewhite/CAC/AISD

05/03/2007 08:59 PM

To
Brandon.Allen_at_OneNeck.com
cc
oracle-l_at_freelists.org, oracle-l-bounce_at_freelists.org Subject
Re: Can you execute a function in an Oracle comment/hint?Link
<Notes://n236m04/86256BAD005BCD39/DABA975B9FB113EB852564B5001283EA/4297B
10F02876275862572D100024FBB>          How about
Execute Immediate 'Select ''/* ' ||
sys_context('USERENV','SESSION_USER')|| ' */' , SysDate From Dual ;

I may not have the single quotes just right, but I'm headed for the shower, so you can do the testing. Heck, this may not even be close! ...but it looks good to me.

Hope it helps.

Jack C. Applewhite - Database Administrator

"Allen, Brandon" <Brandon.Allen_at_OneNeck.com> Sent by: oracle-l-bounce_at_freelists.org

05/03/2007 07:25 PM
Please respond to
Brandon.Allen_at_OneNeck.com

To
<oracle-l_at_freelists.org>

cc
Subject
Can you execute a function in an Oracle comment/hint?         

I'm pretty sure I saw someone demonstrate the syntax for this before, but I've looked everywhere and can't find it now. What I'm trying to do is execute a function in the in-line comment of a query, like this:

select /* sys_context('USERENV', 'SESSION_USER') */ sysdate from dual;

But, it's not working. The above query gets executed, but the sys_context function is not being executed, it's just being treated as a comment. What I want is for this query to show up as:

select /* SCOTT */ sysdate from dual

In v$sql.sql_text.

Is this possible, or am I just imagining having seen this before?

Thanks,
Brandon

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