This discussion is archived
5 Replies Latest reply: Dec 6, 2012 4:45 AM by BluShadow RSS

Cursors

968368 Newbie
Currently Being Moderated
Can we define cursor inside with out package? If so how to call the cursor?
  • 1. Re: Cursors
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Bizz wrote:
    Can we define cursor inside with out package? If so how to call the cursor?
    I'm not sure what you mean by "inside" here.

    You can define and use cursors in any PL/SQL (anonymous blocks, stand-alone procedures and functions, triggers, ...) the same way you define and use them in packages.

    In SQL*Plus, you can use the VARIABLE command to define a cursor, and PRINT to display it.

    Here's an example of defining a cursor in a stand-alone function, not part of a package:
    CREATE OR REPLACE FUNCTION  emps_in_dept 
    (   in_deptno  IN  scott.emp.deptno%TYPE
    )
    RETURN     SYS_REFCURSOR
    IS
        r     SYS_REFCURSOR;     -- Cursor to be returned
    BEGIN
        OPEN  r  FOR  SELECT    empno
                         ,         ename
                ,         deptno
                FROM         scott.emp
                WHERE         deptno  = in_deptno
                ORDER BY  ename;
    
        RETURN  r;
    END  emps_in_dept;
    /
    SHOW ERRORS
    Here's an example of defining, setting and viewing a cursor in SQL*Plus, using the function above:
    VARIABLE  rc  REFCURSOR
    EXEC     :rc := emps_in_dept (10);
    
    PRINT  :rc
    Output:
    `    EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7782 CLARK              10
          7839 KING               10
          7934 MILLER             10
    Edited by: Frank Kulash on Dec 5, 2012 3:28 PM
    Added examples.
  • 2. Re: Cursors
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    I do not understand your question. "inside" what?
    You can define cursors outside packages.

    One exaple is an anonymous block:
    declare
      cursor a is select 1 a from dual;
      b number;
    begin
      open a;
      loop
        fetch a into b;
        exit when a%notfound;
        DBMS_OUTPUT.PUT_LINE(b);
      end loop;
    end;
    Regards,

    Peter
  • 3. Re: Cursors
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Bizz wrote:
    Can we define cursor inside with out package? If so how to call the cursor?
    Cursors resides in the SQL engine as "programs". The code segment (compiled/parsed SQL) of this cursor program resides in the SQL Shared Pool. The data segment of this cursor program resides in the session's UGA.

    A single copy of a SQL cursor should exist in the Shared Pool. E.g.
    select count(*) from emp where deptno = :1
    A 100 sessions can use and share that same cursor code - with each of these sessions using different bind variables (for counting employees in different departments). And with each of these 100 sessions having their own UGA that contains the state of that cursor.

    So what then exists in the client side - in languages like PL/SQL and Java and .Net that uses SQL cursors? A cursor handle and cursor call interface exist - allowing this programming language to create/open cursors, execute cursors, and use cursors, that exist on the database server. This interface in the client languages communicates with the standard SQL cursor interface (as described by the Oracle Call Interface) of the SQL engine.

    A client like PL/SQL, have a number of client cursor interfaces that you can use to communicate with the SQL cursor. There is an implicit cursor interface, an explicit cursor interface, the execute immediate interface, the ref cursor interface, and the DBMS_SQL cursor interface.

    Each of these client interfaces can be used against the SAME SQL cursor in the server's SQL Shared Pool.

    The choice of which client cursor interface to use to deal with the SQL cursor, depends entirely on what your requirements are.

    Two of PL/SQL cursor call interfaces allows you to create a "global" client cursor interface that you can use in different packages and procedures in your sessions - ref cursors and DBMS_SQL cursors.
  • 4. Re: Cursors
    Suman Rana Explorer
    Currently Being Moderated
    I think Bizz point to say declaring a cursor variable in Package specification and the answer is NO..

    For example

    CREATE OR REPLACE PACKAGE pkg_tst AS
         refcur     SYS_REFCURSOR;
    END pkg_tst;

    Error:
    PLS-00994: Cursor Variables cannot be declared as part of a package

    Cause: An attempt was made to declare a cursor variable in a package specification, which is not allowed. Although REF CURSOR types can be defined in a PL/SQL block, subprogram, or package, cursor variables can be declared only in a block or subprogram.

    Action: Move the cursor variable declaration into a PL/SQL block or subprogram.
  • 5. Re: Cursors
    BluShadow Guru Moderator
    Currently Being Moderated
    Suman Rana wrote:
    I think Bizz point to say declaring a cursor variable in Package specification and the answer is NO..
    I don't think that's what the OP was asking.

    I think the question was more along the lines of...

    Just as we can create table, types etc. on the database e.g.
    CREATE TABLE mytable (...);
    CREATE TYPE mytype AS ....;
    can we create a cursor definition in the same way e.g.
    CREATE CURSOR mycur AS SELECT ....
    and if so, how can we use that cursor.

    The answer to that, is No we cannot create cursor definitions directly on the database in that way.
    However, we can create views...
    CREATE VIEW myview AS SELECT ...
    and select from those, although we cannot OPEN... FETCH... CLOSE... a view, or use it like "FOR i IN myview LOOP..." like we can do with a cursor definition in PL/SQL code.

    That really comes down to a misunderstanding of what the difference is between a cursor definition and a cursor.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points