Re: Dynamic SQL Query In PL/SQL Stored Procedure, How To????

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/12/17
Message-ID: <32b5f877.2558789_at_dcsun4>#1/1


You need to use dbms_sql to achieve what you are trying to do

good examples exist at http://govt.us.oracle.com, follow the link to downloadable utilities. You'll be looking for the owa extensions.

On Mon, 16 Dec 1996 16:50:54 GMT, rjkrause_at_ix.netcom.com (Bob Krause) wrote:

>Hello,
> I am using stored procedures and in one I have to construct a dynamic
>SQL statement. The dynamic piece is the where clause. The application
>gets values from a html page and based on these values the where
>clause is constructed.
>
>I have create a ref cursor in the proc spec:
>
> type search_curtype is ref cursor return search_result_rectype;
>
>Then in the proc body:
>
> procedure search(tst1 in varchar2, tst2 in varchar2,
> tst3 in varchar2)
> is
> result_rec search_result_rectype;
> search_curvar search_curtype;
> sql_statement varchar2(100) := '';
> begin
> sql_statement := get_tst3_sql(tst3);
>
> open search_curvar for
> select *
> from table1, table2
> where table1.col1_code = tst1
> and table1.col1_code = table2.col1_code
> || sql_statement;
>
> fetch search_curvar into result_rec;
>......etc.
>
>function get_tst3_sql(tst3 in varchar2)
>return varchar2
>is
> sql_statement varchar2(100) := '';
>begin
> sql_statement := ' and tst1.col2 = ' || tst3;
> return sql_statement;
>end;
>
>This piece shoule be concatenated onto the sql statement in the
>procedure?
>
>Can anyone share a code snippet for something similiar to this?
>
>Thanks,
>Bob Krause
>e-mail: BobK_at_selectcorp.com
> rjkrause_at_ix.netcom.com
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

  • Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Dec 17 1996 - 00:00:00 CET

Original text of this message