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 <wisernet100_at_yahoo.com> wrote:
> O'Reilly and PL/SQl Programming almost ALWAYS means
> the author is
> Steven Feuerstein
>
>
> --- dgoulet_at_vicr.com 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 <john.dunn_at_sefas.co.uk>
> > 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:iain.nicoll_at_calanais.com]
> > > 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: mkb125_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Fri Sep 06 2002 - 16:08:29 CDT
