PL/SQL help

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Thu, 19 Sep 2002 10:53:24 -0800
Message-ID: <F001.004D4279.20020919105324@fatcity.com>

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 := 'select col1 from atable where col2 = :1' ;

or it may be

c_sql := 'select col1 from atable where col2 = :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_at_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_at_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_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 Thu Sep 19 2002 - 13:53:24 CDT