RE: Can you execute a function in an Oracle comment/hint?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <JApplewhite@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 May 2007 10:06:22 -0700
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@xxxxxxxxxxxxx [mailto:JApplewhite@xxxxxxxxxxxxx]
Sent: Thursday, May 03, 2007 7:12 PM
To: Allen, Brandon
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxxxx
cc
oracle-l@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
05/03/2007 07:25 PM
Please respond to
Brandon.Allen@xxxxxxxxxxx
To
<oracle-l@xxxxxxxxxxxxx>
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.
Other related posts:
