Yes. -----Original Message----- Sent: Friday, September 06, 2002 4:28 PM To: Multiple recipients of list ORACLE-L
Kevin, Are you saying then, that by default, any static statement that is executed within PL/SQL will not have be re-parsed eg sp_proc(var in varchar2) as begin select last_name from emp where last_name = var; end; If that's the case, I wont have to change much code. mkb --- "Toepke, Kevin M" <[EMAIL PROTECTED]> wrote: > Actually its easy. Any variable declared in PL/SQL > and referenced in a > non-dynamic SQL statement is a bind variable. > > In the following example (#1), some_var is an output > bind-variable and > other_var is a input bind variable. PL/SQL does > manipulation on the > statement and will send something like the following > (#2) to the database > > #1 > DECLARE > some_var NUMBER(1); > other_var NUMBER(1) > BEGIN > SELECT 1 > INTO some_var > FROM my_table > WHERE my_column = other_var; > END; > > #2 > SELECT 1 FROM MY_TABLE WHERE MY_COLUMN = :1 > > Kevin > -----Original Message----- > Sent: Friday, September 06, 2002 1:59 PM > To: Multiple recipients of list ORACLE-L > > > 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 > > OPTIMIZING > === 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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).
