1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

about cursor datatype

Discussion in 'SQL PL/SQL' started by oracle123, Dec 17, 2009.

  1. oracle123

    oracle123 Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    hello friends..

    can anyone tell me that 'does cursor has a datatype?'

    Thanx
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That depends upon the context of the question. Are you passing a cursor to another procedure? In those situations you can declare a datatype or use the SYS_REFCURSOR supplied type. An example of declaring a cursor datatype is shown below:

    Code (SQL):
    CREATE OR REPLACE package my_package IS
        TYPE refcursor IS REF cursor;

        PROCEDURE proc1(p_job IN varchar2, p_cur IN OUT refcursor);
    END;
    /

    CREATE OR REPLACE package body my_package IS
        PROCEDURE proc1(p_job IN varchar2, p_cur IN OUT refcursor) AS
            l_query varchar2(255);

        BEGIN
            l_query := 'select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where job = '''||p_job||'''';
            OPEN p_cur FOR l_query;
        END;
    END;
    /

    SHOW errors

    SET serveroutput ON SIZE 1000000

    DECLARE
        TYPE rcursor IS REF cursor;
        emptab rcursor;
        emprec emp%rowtype;
    BEGIN
        my_package.proc1('CLERK',emptab);

        loop
            fetch emptab INTO emprec;
            exit WHEN emptab%notfound;
            dbms_output.put_line(emprec.ename||' with employee number '||emprec.empno||' works in department number '||emprec.deptno);
            dbms_output.put_line('Hired on '||emprec.hiredate);
        END loop;

    END;
    /


     
     
    oracle123 likes this.
  3. oracle123

    oracle123 Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hello Sir,
    Thanks for giving me valuable information..