Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: different SQL approaches

Re: different SQL approaches

From: AK <ak_tiredofspam_at_yahoo.com>
Date: 16 Oct 2004 17:51:03 -0700
Message-ID: <46e627da.0410161651.5651a033@posting.google.com>


>
> And then, of course, there is the issue of bind variables as neither
> statement should ever be written for any reason. What should be written
> is:
>
> DECLARE
>
> rtype sc.rec_type%TYPE;
> ctype sc.code_type%TYPE;
>
> BEGIN
> rtype := 'S';
> ctype := '502';
>
> SELECT ...
> FROM ...
> WHERE ...
> AND sc_rec_type = rtype
> AND sc.code_type = ctype;
>
> END;
> /

what do you mean by "neither statement should ever be written for any reason"?
(if you wrote 'usually' rather than 'ever', I would completely agree)

let's recall that there are quite a few exceptions:

  1. many shops do use CURSOR_SHARING=FORCE, which replaces all the literals with bind variables no matter what
  2. sometimes replacing all the literals with bind variables results in very inefficient execution plans. that's why we sometimes need to use a hint to hava a query recompiled each time, so that the optimizer takes in account the actual literal values every time the query is executed
Received on Sat Oct 16 2004 - 19:51:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US