RE: PL/SQL help

Return-path: Envelope-to: oracle-l@orafaq.net Delivery-date: Sat, 21 Sep 2002 03:34:41 +0800 Received: from newsfeed.cts.com ([209.68.248.164]) by jean.onefuse.com with esmtp (Exim 3.36 #1) id 17sTY1-00089P-00 for oracle-l@orafaq.net; Sat, 21 Sep 2002 03:34:41 +0800 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA87753; Fri, 20 Sep 2002 12:34:50 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004D58D2; Fri, 20 Sep 2002 12:20:07 -0800 Message-ID: Date: Fri, 20 Sep 2002 12:20:07 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Paula_Stankus@doh.state.fl.us Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Paula_Stankus@doh.state.fl.us Subject: RE: PL/SQL help Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C260DA.CE18E430" ------_=_NextPart_001_01C260DA.CE18E430 Content-Type: text/html Content-Transfer-Encoding: quoted-printable RE: PL/SQL help

Go to Tom Kyte's sight and look for contexts - he = explains a way to build sql statements using native dynamic sql and = refcursors and contexts where the select statement and predicate is = variable and the output might also be variable. I get there by = searching www.yahoo.com for asktom

-----Original Message-----
From: Ron Thomas [mailto:rthomas@hypercom.com]
Sent: Friday, September 20, 2002 2:06 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: PL/SQL help

David-

I've thought of both dbms_sql and dummy conditions = too. Oracle Application reports use the dummy
conditions all the time.

I'm off to see if you can set cursor_sharing at the = session level and not use bind params.

I'm assuming no one else has figured this out either = due to the lack of response.

Ron Thomas
Hypercom, Inc
rthomas@hypercom.com
Each new user of a new system uncovers a new class = of bugs. -- Kernighan

          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;
          &nb= sp;           = root@fatcity.com         &n= bsp;           &n= bsp;           &n= bsp;           &n= bsp;           &n= bsp;           &n= bsp;
          &nb= sp;           &nb= sp;           &nb= sp;            = To:       = ORACLE-L@fatcity.com        &nbs= p;           &nbs= p;           &nbs= p;
          &nb= sp;           = 09/20/02 01:28 AM        = cc:           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;
          &nb= sp;           Please = respond to        Subject: RE: = PL/SQL = help           &n= bsp;           &n= bsp;           &n= bsp;
          &nb= sp;           = ORACLE-L          &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;
          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;
          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;

Ron

We've run into this problem and haven't really found = a satisfactory answer.
It sounds like a job for dbms_sql, but the docs say = its slower than native
dynamic sql. Has anyone tested this? One = other alternative I can think of
(but have never tried) is to use dummy conditions in = the sql; something
like: -

c_sql :=3D 'select col1 from atable where col2 =3D :1 = and :2 is null and :3 is
null';

--
David Lord

> -----Original Message-----
> From: Ron Thomas [mailto:rthomas@hypercom.com]
> Sent: 19 September 2002 19:53
> To: Multiple recipients of list ORACLE-L
> Subject: PL/SQL help
>
>
>
> I am building a dynamic sql statement which = will contain
> varying number of bind variables = depending
> on user selection criteria. As an = example, the sql statement may be:
>
> c_sql :=3D 'select col1 from atable where = col2 =3D :1' ;
>
> or it may be
>
> c_sql :=3D 'select col1 from atable where = col2 =3D :1 and col2
> between :2 and :3' ;
>
> or it may be ... etc. I am trying to = avoid ugly code such as:
>
> IF case1 THEN
>   OPEN csr FOR c_sql USING var1 = ;
> ELSIF case2 THEN
>   OPEN csr FOR c_sql USING var1, = var2, var3 ;
> ELSIF .....
> END IF ;
>
> Once the sql statement is created, it will be = opened/closed
> multiple times, so I want to use bind
> variables to avoid parsing.
>
> So I thought, hum, sounds like a job for = EXECUTE IMMEDIATE,
> but in the various incarnations I tried,
> could not get it to work. The FM have not = been much help
> (still looking tho).
>
> What am I missing?
>
> Thanks,
> Ron Thomas
> Hypercom, Inc
> rthomas@hypercom.com
> Each new user of a new system uncovers a new = class of bugs.
> -- Kernighan
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ron Thomas
>   INET: rthomas@hypercom.com
>
> Fat City Network Services    -- = 858-538-5051 http://www.fatcity.com
> San Diego, = California        -- Mailing list = and web hosting services
> = ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send = an E-Mail message
> to: ListGuru@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).
>

***************************************************************= *******
This message (including any attachments) is = confidential and may be
legally privileged. If you are not the = intended recipient, you should
not disclose, copy or use any part of it - please = delete all copies
immediately and notify the Hays Group Email Helpdesk = at
email.helpdesk@hays.plc.uk
Any information, statements or opinions contained in = this message
(including any attachments) are given by the = author. They are not
given on behalf of Hays unless subsequently = confirmed by an individual
other than the author who is duly authorised to = represent Hays.

A member of the Hays plc group of companies.
Hays plc is registered in England and Wales number = 2150950.
Registered Office Hays House Millmead Guildford = Surrey GU2 4HJ.
***************************************************************= *******

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lord, David - CSG
INET: David.Lord@hayscsg.com

Fat City Network Services -- = 858-538-5051 http://www.fatcity.com
San Diego, = California -- Mailing list = and web hosting services
---------------------------------------------------------------= ------
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@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: Ron Thomas
INET: rthomas@hypercom.com

------_=_NextPart_001_01C260DA.CE18E430-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Paula_Stankus@doh.state.fl.us Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).