Re: REF CURSORS and DYNAMIC SQL

From: Phil Spencer <phil.spencer_at_ubs.com>
Date: Thu, 13 Aug 1998 13:03:04 +0200
Message-ID: <35D2C7E8.A07C742B@ubs.com>

Hi Joeren,

My initial question was a little brief...

Here is the basis of the code I want to achieve. The example is grossly simplified the real query involves 13 independent search criteria and a 5 way union!

The problem is that the line

        dbms_sql.bind_variable(dbms_sql_cursor,':csr',csr);

doesn't work because dbms_sql.bind_variable does not support ref cursors.

The code shown may not be syntactically correct - I was playing around trying to find a solution.

Phil

create or replace package body pkg_dyne as

        function where_term(colexp in varchar2, parm in varchar2) return varchar2 is

		term	varchar2(8192);
	begin
		if parm is null then
			term	:= ' ';						-- No term required
		elsif instr(parm,'%')=0 then
			term	:= ' and ('||colexp||' = '''||parm||''')';	-- Use '='
		else
			term	:= ' and ('||colexp||' like '''||parm||''')';	-- Use 'like'
		end if;
		return term;
	end;

	procedure find(
		io_x_csr	in out	x_csr_type,
		in_y		in	y_type,
		in_z		in	z_type,


		...


	) is

		dbms_sql_cursor	integer;
		ret		integer;
		WHERE_CLAUSE	VARCHAR2(8192);
		statement	VARCHAR2(8192);
		csr		x_csr_type;
	begin
		WHERE_CLAUSE	:= ' WHERE 0=0';

		WHERE_CLAUSE	:= WHERE_CLAUSE || WHERE_TERM('Y',	IN_Y);
		WHERE_CLAUSE	:= WHERE_CLAUSE || WHERE_TERM('Z',	IN_Z);


		...



		dbms_output.put_line ('where_clause = ['||where_clause||']');

		statement	:= 
			' declare'
		||	' 	csr pkg_dyne.x_csr_type;'
		||	' begin'
		||	' 	open csr for'
		||	' 		select y, z from t1'
		||			WHERE_CLAUSE
		||	'	;'
		||	' end;';

	--	dbms_output.put_line ('statement = ['||statement||']');

		dbms_sql_cursor	:= dbms_sql.open_cursor; 
		dbms_sql.parse(dbms_sql_cursor,statement,DBMS_SQL.V7);
		dbms_sql.bind_variable(dbms_sql_cursor,':csr',csr);
		ret := dbms_sql.execute(dbms_sql_cursor);
		dbms_sql.close_cursor(dbms_sql_cursor);

	end find;

end pkg_dyne;
/

Jeroen van Sluisdam wrote:

> 
> Hi Phil,
> 
> I'm not sure i understand exaclty what you mean but I used ref cursor
> with fixed
> statements in the following way:
> 
> type a ref cursor;
> b a;
> 
> if xxxx then
>    open b for
>     select yyyy
>    .....
> else
>    open b for
>    select zzzz
> end if
> loop
>    fetch b
> end loop
> 
> Hope this helps.
> 
> Jeroen
> 
> Phil Spencer wrote:
> 
> > Does anyone know a way to bind a REF cursor created by a Dynamic SQL
> > statement in ORACLE 7.3
> >
> > TIA
> >
> > Phil

Received on Thu Aug 13 1998 - 06:03:04 CDT