John, I'm in exactly the same predicament. I'm also trying to find some examples. I have an older version of Feuerstein book which does talk about using DBMS_SQL package to bind variables. Unfortunately it looks a little messy. I'm now looking at the following link:
http://gethelp.devx.com/techtips/oracle_pro/10min/10min1000.asp which seems to provide a couple examples. This is for 8i and above. If I get anything to work, I'll pass along what I have. hth mkb --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > O'Reilly and PL/SQl Programming almost ALWAYS means > the author is > Steven Feuerstein > > > --- [EMAIL PROTECTED] wrote: > > John, > > > > You would have to ask while I've got the book > at home. But it's > > an Orielly > > book on PL/SQL Programming. Sorry off the top of > my head I can't > > remember the > > author or title. > > > > Dick Goulet > > > > ____________________Reply > Separator____________________ > > Author: John Dunn <[EMAIL PROTECTED]> > > Date: 9/6/2002 7:38 AM > > > > Despite the importance of using bind variables, > the Oracle > > documentation > > seems to make very little reference to how to use > them(for example > > the > > PL/SQL manual) > > > > Can anyone point me at any decent documentation on > the subject of > > using bind > > variables in PL/SQL? > > > > John > > > > > > > > > -----Original Message----- > > > From: Nicoll, Iain (Calanais) > [SMTP:[EMAIL PROTECTED]] > > > Sent: 06 September 2002 15:23 > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: Must Read for Every Developer > and DBA > > > > > > I thought that bind variables were faster but > you always have to > > ensure > > > that > > > if you're accessing by data which may be heavily > skewed and > > histograms > > > would > > > usually help you may not want to use bind > variables as they will > > disable > > > the > > > use of histograms. > > > > > > In saying that it doesn't look as though that > would be the case > > here. > > > > > > Iain Nicoll > > > > > > -----Original Message----- > > > Sent: Friday, September 06, 2002 2:33 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hello Vikas, > > > > > > As You said We should always make use of bind > variables as it > > executes > > > faster as compare to the statements where we do > not > > > make use of bind variables. > > > > > > Q1) Can you please take a more specific example > as how a statement > > can be > > > altered to make use of bind variable. > > > > > > Q2) I made use of SELECT SQL_TEXT FROM V$SQLAREA > WHERE ROWNUM < 5 > > to get > > > few > > > samples for you > > > > > > These are as follows > > > > > > UPDATE CNST_QUEUE SET PROCESS = -1 > ,USER_ID ='A101675' > > > ,DATE_QUEUED = sysdate WHERE AWB_PREFIX > > > = '125' AND AWB_NUMBER = 67557405 AND > AWB_SUFFIX = ' > > ' > > > AND > > > PROCESS = 1 AND USER_ID = 'A101675' > > > > > > UPDATE CNST_QUEUE SET PROCESS = -1 > ,USER_ID = > > 'A101675' > > > ,DATE_QUEUED = sysdate WHERE AWB_PREFIX > > > = '125' AND AWB_NUMBER = 68221156 AND > AWB_SUFFIX = ' > > ' > > > AND > > > PROCESS = 1 AND USER_ID = 'A101675' > > > > > > UPDATE CNST_QUEUE SET PROCESS = -1 > ,USER_ID ='A105722' > > > ,DATE_QUEUED = sysdate WHERE AWB_PREFIX > > > = '125' AND AWB_NUMBER = 67557405 AND > AWB_SUFFIX = > > 'A ' > > > AND > > > PROCESS = 1 AND USER_ID = 'A105722' > > > > > > UPDATE CNST_QUEUE SET PROCESS = -1 > ,USER_ID = > > 'A105722' > > > ,DATE_QUEUED = sysdate WHERE AWB_PREFIX = > '125' AND > > AWB_NUMBER = > > > 67557416 AND AWB_SUFFIX = ' ' AND > PROCESS = 1 AND > > > USER_ID > > > = 'A105722 > > > > > > How can I Introduce bind variables in these > statements ? > > > > > > I may be sending a wrong SAMPLE as I feel I > should apply your > > remove > > > constant function and then send few SQL > statements > > > > > > Warm Regards, > > > Om > > > > > > In your case -- you are NOT using bind > variables. > > > > > > Taking your update statement here: > > > > > > UPDATE CNST_QUEUE SET PROCESS = -1 > ,USER_ID = > > 'A101675' > > > ,DATE_QUEUED = sysdate WHERE AWB_PREFIX > > > = '125' AND AWB_NUMBER = 67557405 AND > AWB_SUFFIX = ' > > ' > > > AND > > > PROCESS = 1 AND USER_ID = 'A101675' > > > > > > that SHOULD BE recoded in the application to > become : > > > > > > update cnst_queue set process = :b1, user_id = > :b2, date_queued = > > sysdate, > > > where awb_prefix = :b3 > > > and awb_number = :b4 > > > and awb_suffix = :b5 > > > and awb_process = :b6 > > > and user_id = :b7; > > > > > > and bind in those values before you execute this > statement. There > > are ways > > > in which it could be done and vary from language > to language and > > > environment > > > to environment but they ALL support it. You > MUST do this. In this > > > case,the > > > first time you execute this statement you need > to parse this > > statement > > > (HARD > > > PARSING) and once the execution plan gets into > the SHARED POOL > > > (V$libraryCache) the other users can use this to > great effect. They > > would > > > not reparse this statement again and again and > but does do the soft > > > parsing > > > of it. So One Parse may lead to MANY executions > instead of 1 > > Parsing <-> 1 > > > Execution. > > > > > > At least 90% of your database execution time is > spent PARSING and > === message truncated === __________________________________________________ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
