Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    US
    Posts
    10

    Unanswered: Return a Resultset out of a Procedure

    I need to pass a whole resultset of a cursor as an output parameter to my Stored Procedure . How would i do that and a small example will be of great help.

  2. #2
    Join Date
    May 2003
    Posts
    87
    Use reference cursors !!

    Code:
    create or replace package test_pkg as
      type rc is ref cursor;
      procedure open_cv(i_dept_no in number, emp_cv in out rc);
    end;
    /
    
    create or replace package body test_pkg as
      procedure open_cv(i_dept_no in number, emp_cv in out rc) is
      begin
        open emp_cv for
          select emp from emp where dept_no = i_dept_no;
      end;
    end;
    /
    
    var x refcursor
    exec test_pkg.open_cv(10, :x)
    print x

  3. #3
    Join Date
    Jun 2003
    Location
    US
    Posts
    10
    Thanks a lot.It works except for the Print.That's okay.

    Originally posted by dbmadcap
    Use reference cursors !!

    Code:
    create or replace package test_pkg as
      type rc is ref cursor;
      procedure open_cv(i_dept_no in number, emp_cv in out rc);
    end;
    /
    
    create or replace package body test_pkg as
      procedure open_cv(i_dept_no in number, emp_cv in out rc) is
      begin
        open emp_cv for
          select emp from emp where dept_no = i_dept_no;
      end;
    end;
    /
    
    var x refcursor
    exec test_pkg.open_cv(10, :x)
    print x

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    You can also use 'weak cursors' which can have some performance improvements in a multiuser/read only (or even serialised) situation, with these you can use strings as the select statement....

    open curMyCursor for
    'select '||MyColumList||
    'from MyTable'||
    'order by '||OrderString;

    I would suggest though that if you choose to use this method that you don't put 'WHERE' clause statements in as literals. The order by won't affect performance but a where clause will, ie

    This is bad....
    'where value = '||inValue||' and OthValue = '||OtherValue

    This is good
    'where value = :bvValue and OthValue = :bvOtherValue'
    using inValue, inOtherValue

    The benefit being that you can effectively construct dynamic queries but still have them packaged on the database. My preference is not to specify SQL literals in the parameters to your procedure or function call but to specify 'modifiers'.

    Ie... I think this is bad

    function GetCursor( inCol1, inVal1, inOrderBy, outCursor )...
    ....
    open outCursor for
    'select '||inCol1||
    'from tablename'||
    'where Value = '||inVal1||
    'order by inOrderBy;


    But this should be okay
    function GetCursor( inColType, inVal1, inOrderBy, outCursor )...
    ....
    if inColType = 'MODIFYING SOME LIST' THEN
    ColList := 'col1,col2, col3 ';
    elsif inColType = 'READING A DIFFERENT LIST' THEN
    ColList := 'col3, col8, col9';
    else
    raise application_error( -20001, 'Invalid parms in GetCursor'||inColType);
    end if;

    open outCursor for
    'select '||ColList
    'from table '||
    'where Value = :inValue'||
    'order by '||inOrderBy
    using inVal1


    etc. I think you get the idea, just make sure that all where conditions use bind variables.

    Cheers
    Bill

  5. #5
    Join Date
    Jun 2003
    Location
    US
    Posts
    10
    Thanks Bill.I tried it out and it works . I was trying to a CLOB as an o/p parameter to CF, but passing it out as a REF CURSOR helped.

  6. #6
    Join Date
    Dec 2003
    Posts
    42

    cursor as out parameter

    Hi,

    i have the same problem which you have faced, iam getting the ptoblem with out parameeter as a cursor. how can we do this.

    pls send some code for that,

    if possible send full code.

    Thanking you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •