RE: Thoughts in /* standardized SQL remarks */

From: David Kurtz <info2_at_go-faster.co.uk>
Date: Fri, 17 Mar 2006 21:42:36 -0000
Message-ID: <CKEAJBMGFEOCDBFILPJDMEGMHHAA.info2@go-faster.co.uk>

I do this sometimes in PeopleSoft. I even have a PL/SQL script that adds the comments automatically to the meta data from which the SQL statements are generated.
I only put in a string that will tell me where the SQL comes from. Then I can go there and read any comments.

I recommend that you keep the comment short and static. Short because itis going into the SGA, don't let the comment thing get out of hand. Static because if the comment changes, Oracle will not reuse the statement in the cached in the SGA.

regards

David Kurtz
Go-Faster Consultancy Ltd.

tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk

mailto:david.kurtz_at_go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba   -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
  Sent: 17 March 2006 21:28
  To: post.ethan_at_gmail.com; Oracle Discussion List   Subject: RE: Thoughts in /* standardized SQL remarks */

Someone remind me, which form of comment marker survives all the various inbound filters better?

I was thinking it was leading -- rather than bracketed /* */, and which one you choose can explain lost hints as well, right?

Of course if you're building systems, then you can store queries and one of the parameters can be the caller identification string and the others are the data values (or arrays), and you don't have to worry about the source of the query and you minimize what gets tossed through the network to a routine name and data. If different calling sources for the same stored query have a use pattern associated with parameters that probably need different peeks, tossing in a literal string for the caller identification parameter instead of a bind variable can make it hash differently, too. Just be careful on that slippery slope.

This, of course, does nothing for truly ad hoc reporting (but does support parameterized pre-planned report queries).

Regards,

  mwf
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ethan Post

Sent: Friday, March 17, 2006 3:45 PM To: Oracle Discussion List
Subject: Thoughts in /* standardized SQL remarks */

Reading through some very long and complicated SQL trace files today, I find I frequently have to go grep the code to try to find out where the SQL originated from. Do you think a comment in the SQL should be standardized for our development staff?

SELECT /* module, about, date modified */

what are your thoughts?

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

Received on Fri Mar 17 2006 - 15:42:36 CST