FAQ
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 ?

Search Discussions

  • Alexander Fatkulin at May 26, 2008 at 11:51 pm
    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 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
  • Gints Plivna at May 27, 2008 at 7:39 am
    See this thread for discussion of various approaches:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279

    Gints Plivna
    http://www.gplivna.eu

    2008/5/27, Rick Ricky :
    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?
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 26, '08 at 10:31p
activeMay 27, '08 at 7:39a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase