Re:bind variables
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 ANDUSER_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
Re:bind variables
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 ANDUSER_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 > > that update -- 10% is spent actually DOING it. If you use bind > variables > > -- > > very little time will be spent parsing (you can get that statement > to > > execute in 1/10 of the time). Not only that -- but the concurrency > and > > scalability of your database will go WAY up. > > > > This is the root cause of your issues, this must be
Re:bind variables
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 ANDUSER_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 > that update -- 10% is spent actually DOING it. If you use bind variables > -- > very little time will be spent parsing (you can get that statement to > execute in 1/10 of the time). Not only that -- but the concurrency and > scalability of your database will go WAY up. > > This is the root cause of your issues, this must be fixed -- no questions > about it. > > Vikas Khanna > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Vikas Khanna > 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,
