| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic SQL Query In PL/SQL Stored Procedure, How To????
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
 |
 |