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" <ktoepke_at_trilegiant.com> 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 <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
> > 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: 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Toepke, Kevin M
INET: ktoepke_at_trilegiant.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 Mon Sep 09 2002 - 07:33:19 CDT
