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

From: Bob Krause <rjkrause_at_ix.netcom.com>
Date: 1996/12/16
Message-ID: <593v20$act@dfw-ixnews12.ix.netcom.com>#1/1

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 Received on Mon Dec 16 1996 - 00:00:00 CST