| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: REF CURSORS and DYNAMIC SQL
I can see what you are trying to do, but is it necessary ?
BIND_VARIABLE only works in the WHERE clause
of an SQL statement. Why can you not just construct
the SQL (without a block) and use DBMS_SQL to
execute it.
Also, if you want to bind variables in the WHERE clause do not include a leading ":" in the call to BIND_VARIABLE. It must be included in the WHERE clause to mark the bind variable.
Regards
Phil Spencer wrote:
> 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
--
 |
 |