Re: bind variables and dynamic ref cursors

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Mon, 26 May 2008 19:51:27 -0400
Message-ID: <49d668000805261651l635c7570rc6d0d1ba37a2d9b4@mail.gmail.com>

I like using session's context in such cases...

SQL> create table t (n1 number, n2 number, n3 number);

Table created

SQL> insert into t values (null, null, null);

1 row inserted
SQL> insert into t values (1, null, null);

1 row inserted
SQL> insert into t values (1, 1, null);

1 row inserted
SQL> insert into t values (1, 1, 1);

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> create or replace function f(

  2      p_n1    number default null,
  3      p_n2    number default null,
  4      p_n3    number default null
  5  ) return sys_refcursor is
  6      l_rf    sys_refcursor;
  7      l_sql    varchar2(4000);
  8      l_where    varchar2(200);
  9  begin
 10      l_sql:='select * from t';

 11
 12

 13      if (p_n1 is not null)
 14      then
 15          dbms_session.set_context('f_context', 'n1', p_n1);
 16
 17          l_where:=' and n1=sys_context(''f_context'', ''n1'')';
 18      end if;
 19
 20      if (p_n2 is not null)
 21      then
 22          dbms_session.set_context('f_context', 'n2', p_n2);
 23
 24          l_where:=l_where||' and n2=sys_context(''f_context'', ''n2'')';
 25      end if;
 26
 27      if (p_n3 is not null)
 28      then
 29          dbms_session.set_context('f_context', 'n3', p_n3);
 30
 31          l_where:=l_where||' and n3=sys_context(''f_context'', ''n3'')';
 32      end if;
 33
 34      if (l_where is not null)
 35      then
 36          l_sql:=l_sql||' where '||substr(l_where, 5);
 37      end if;
 38
 39      open l_rf for l_sql;
 40
 41      return l_rf;

 42 end;
 43 /

Function created

SQL> create context f_context using f;

Context created

SQL> variable rf refcursor;
SQL> exec :rf:=f();

PL/SQL procedure successfully completed.

SQL> print rf;

        N1 N2 N3
---------- ---------- ----------

         1
         1          1
         1          1          1

SQL> exec :rf:=f(1);

PL/SQL procedure successfully completed.

SQL> print rf;

        N1 N2 N3
---------- ---------- ----------

         1
         1          1
         1          1          1

SQL> exec :rf:=f(1,1);

PL/SQL procedure successfully completed.

SQL> print rf;

        N1 N2 N3
---------- ---------- ----------

         1          1
         1          1          1

SQL> exec :rf:=f(1,1,1);

PL/SQL procedure successfully completed.

SQL> print rf;

        N1 N2 N3
---------- ---------- ----------

         1 1 1

SQL> select sql_text
  2 from v$sql
  3 where sql_text like 'select * from t%';

SQL_TEXT

---

select * from t where n1=sys_context('f_context', 'n1') select * from t where n1=sys_context('f_context', 'n1') and n2=sys_context('f_c
ontext', 'n2')

select * from t where n1=sys_context('f_context', 'n1') and n2=sys_context('f_c
ontext', 'n2') and n3=sys_context('f_context', 'n3')

select * from t

6 rows selected.

On Mon, May 26, 2008 at 6:31 PM, Rick Ricky <ricks12345_at_gmail.com> wrote:

> I am writing stored procedures that return ref cursors to developers. For
> one query they have the option to send me 2 or 3 parameters. So this means I
> need 2 or 3 where clauses. using a dynamic ref cursor, how do i use 2 or 3
> bind variables since the number of where cols = statements will vary?
>
> I know how to use dynamic sql. I am not sure how to get this to work with
> ref cursors where the number of bind variables vary ?
>

-- 
Alex Fatkulin,
The Pythian Group,
http://www.pythian.com/blogs/author/alexf

--
http://www.freelists.org/webpage/oracle-l

Received on Mon May 26 2008 - 18:51:27 CDT