6 Replies Latest reply: Jun 3, 2008 8:29 AM by user556475 RSS

    EA1: View contents of ref cursor when running a procedure

    497762
      Hi, One of the most anticipated, hoped for, features does still not appear to be in 1.5 : View contents of ref cursor when running a procedure. This had a lot of support and is something we really need.
      Is there a chance it will make the final release (PLEASE !) ?
        • 1. Re: EA1: View contents of ref cursor when running a procedure
          Vadim Tropashko-Oracle
          Didn't Turlock mentioned that it is already there? Here is the test (EA2; EA1 not tested):

          reate or replace package pivot
          as
          type rc is ref cursor;
          procedure testproc1( p_cursor in out rc );
          end;
          /


          create or replace package body pivot
          as
          procedure testproc1( p_cursor in out rc ) is
          l_stmt long;
          begin
          l_stmt := 'select empno, ename, sal from emp';
          open p_cursor for l_stmt;
          end;
          end;
          /

          variable x refcursor
          exec pivot.testproc1( :x );
          print x

          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          EMPNO ENAME SAL
          ---------------------- ---------- ----------------------
          7369 SMITH 800
          7499 ALLEN 1600
          7521 WARD 1250
          7566 JONES 2975
          7654 MARTIN 1250
          7698 BLAKE 2850
          7782 CLARK 2450
          7788 SCOTT 3000
          7839 KING 5000
          7844 TURNER 1500
          7876 ADAMS 1100
          7900 JAMES 950
          7902 FORD 3000
          7934 MILLER 1300
          • 2. Re: EA1: View contents of ref cursor when running a procedure
            633608
            could you show an example the same as above with an input parameter in the procedure? and how to assihn a vlaue when calling the procdure.

            Thanks,
            Brad
            • 3. Re: EA1: View contents of ref cursor when running a procedure
              Vadim Tropashko-Oracle
              create or replace PACKAGE body pivot
              AS

              PROCEDURE tst3 (
              c1 IN OUT rc,
              c2 OUT rc,
              c3 OUT rc,
              empno in INTEGER ) IS
              stmt1 LONG;
              stmt2 LONG;
              stmt3 LONG;
              stmt4 LONG;
              BEGIN
              stmt1 := 'select ename from emp where empno='|| empno;
              OPEN c1 FOR stmt1;
              stmt2 := 'select ''a'' from dual';
              OPEN c2 FOR stmt2;
              stmt3 := 'select sysdate from dual';
              OPEN c3 FOR stmt3;
              END;

              END;

              variable x refcursor
              variable y refcursor
              variable z refcursor
              exec pivot.tst3( :x, :y, :z, 7369 );
              print x

              anonymous block completed
              x
              ------------------------------------
              ENAME
              ----------
              SMITH *
              • 4. Re: EA1: View contents of ref cursor when running a procedure
                633608
                variable x refcursor
                variable y refcursor
                variable z refcursor
                exec pivot.tst3( :x, :y, :z, 7369 );
                print x

                thank you for the above, but where I am having trouble is assigning a value to a variable and using the variable in the parameter list, I always get a script error if do something like this ( i don't have my access to oracle at the moment so I am doing this from memory):

                variable emp_no number
                emp_no := 123 <=== doesn't like me assigning values

                exec myproc(emp_no)

                I realize I can just put 123 in the parameter list, but I'd like to know if I can use a variable as an input parameter

                Thanks again,
                Brad
                • 5. Re: EA1: View contents of ref cursor when running a procedure
                  Turloch O'Tierney-Oracle
                  Hi user630605,

                  Try setting the bind variable in an anonymous block.
                  [You can also set your input ref cursor in another procedure/anonymous block]

                  -Turloch

                  create or replace procedure myproc99(myempno IN OUT number ) as
                  begin
                  myempno:=1690 + myempno;
                  end;
                  /
                  show warnings
                  variable emp_no number
                  begin
                  :emp_no := 1;
                  end;
                  /
                  exec myproc99(:emp_no);

                  print emp_no

                  output:

                  procedure myproc99(myempno Compiled.
                  anonymous block completed
                  anonymous block completed
                  emp_no
                  ----
                  1691
                  • 6. Re: EA1: View contents of ref cursor when running a procedure
                    user556475
                    I must be missing something really obvious, because I cannot my package with refcursors to work for me in SQL Developer.

                    Exactly where, and how, would I execute this in SQL Developer?

                    This is what I execute in SQL Plus, and it works perfectly:

                    variable x refcursor
                    variable y refcursor
                    begin
                    report_pkg.tax_report1(2008,1,2008,2,:x,:y);
                    end;
                    /
                    print x
                    print y

                    I assume this is to be done in the SQL Worksheet window -- maybe I'm in the wrong spot altogether?

                    When I try to run this in SQL Developer, I always get "Invalid SQL Statement". I've tried various different permutations, but I'm still getting nowhere. What really obvious thing am I missing?

                    This is in both SQL Developer 1.2 and 1.5.