Results 1 to 7 of 7

Thread: Ref Cursors

  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Question Unanswered: Ref Cursors

    Friends,
    I got an example online using ref cursors.Now how i can execute the
    procedure open_emp_cv from sqlplus:



    CREATE PACKAGE emp_data AS
    TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
    choice IN NUMBER);
    END emp_data;

    CREATE PACKAGE BODY emp_data AS
    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
    choice IN NUMBER) IS
    BEGIN
    IF choice = 1 THEN
    OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
    ELSIF choice = 2 THEN
    OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
    ELSIF choice = 3 THEN
    OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
    END IF;
    END open_emp_cv;
    END emp_data;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails, Read The Fine Manual
    http://download.oracle.com/docs/cd/B...b14261/toc.htm
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2007
    Posts
    6

    Plz help solving this one

    I have gone through but how to solve this at the moment.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com has many, many fine coding examples
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Are you looking for something like this?
    Code:
    SQL> variable abc refcursor;
    SQL> exec emp_data.open_emp_cv (:abc, 2);
    
    PL/SQL procedure successfully completed.
    
    SQL> print abc;
    
         EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
    ---------- ---------- --------- ---------- -------- ---------- ----------
        DEPTNO
    ----------
          7566 JONES      MANAGER         7839 02.04.81       2975
            20
    ...

  6. #6
    Join Date
    Jan 2007
    Posts
    6
    Now i use something different:


    CREATE or replace PACKAGE emp_data2 AS
    TYPE EmpCurTyp2 IS REF CURSOR ;
    PROCEDURE open_emp_cv (emp_cv IN EmpCurTyp2);
    person emp%ROWTYPE;
    END emp_data2;

    CREATE OR REPLACE PACKAGE BODY emp_data2 AS
    PROCEDURE open_emp_cv (emp_cv IN EmpCurTyp2) IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('-----');
    DBMS_OUTPUT.PUT_LINE('Here are the names from the result set ');
    LOOP
    FETCH emp_cv INTO person;
    EXIT WHEN emp_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Name = ' || person.ename ||' id' || person.empno);
    END LOOP;
    END open_emp_cv;
    end emp_data2;


    To execute this i try:

    DECLARE
    CURSOR c1 is select * from emp;
    BEGIN
    Emp_data2.OPEN_EMP_CV(c1);
    END;


    and i get
    *
    ERROR at line 4:
    ORA-06550: line 4, column 1:
    PLS-00306: wrong number or types of arguments in call to 'OPEN_EMP_CV'
    ORA-06550: line 4, column 1:
    PL/SQL: Statement ignored


    Plz guide me,i am new to PL/SQL..

  7. #7
    Join Date
    Jan 2007
    Posts
    6

    ThanX Little Foot !!!!!!!!

    Yes buddy !!! was looking for that

Posting Permissions

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