5 Replies Latest reply on Dec 6, 2012 12:45 PM by BluShadow


      Can we define cursor inside with out package? If so how to call the cursor?
        • 1. Re: Cursors
          Frank Kulash
          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
              r     SYS_REFCURSOR;     -- Cursor to be returned
              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:
          EXEC     :rc := emps_in_dept (10);
          PRINT  :rc
          `    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

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

            One exaple is an anonymous block:
              cursor a is select 1 a from dual;
              b number;
              open a;
                fetch a into b;
                exit when a%notfound;
              end loop;

            • 3. Re: Cursors
              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
                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;

                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
                  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.