Home » SQL & PL/SQL » SQL & PL/SQL » GLOBAL CURSOR ON A PACKAGE. (Oracle Database, 10g, Windows Server)
icon4.gif  GLOBAL CURSOR ON A PACKAGE. [message #545679] Thu, 01 March 2012 11:44 Go to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

Hello,

I´m having a trouble creating a db package. I´ve changed my job and now i´m working full on DB instead of Forms Developer.

So my trouble is that i want to create some like a global cursor on DB package, i know you can create global variables and use it on all the procedures/function inside the package. But how about cursors?

My problem:


  • I really dont´know how to declare into the spec.
  • I really don´t know how to use it on body.


My actual code:

I have the structure below:

PackageSpec:
CREATE OR REPLACE PACKAGE [i]MyPackageName[/i]
IS
CURSOR [i]myGlobalCursor[/i](par1 IN VARCHAR2, par2 IN DATE)(
);

PROCEDURE [i]MyProcedure1[/i](par1 IN VARCHAR2, par2 VARCHAR2);
PROCEDURE [i]MyProcedure2[/i](par1 IN VARCHAR2, par2 VARCHAR2);
--All Package Procedures/Functions
.
.
.
END;
/


PackageBody:
CREATE OR REPLACE PACKAGE BODY [i]MyPackageName[/i]
AS
CURSOR [i]myGlobalCursor[/i](par1 IN VARCHAR2, par2 IN DATE)(
)
IS
SELECT COUNT(*)
FROM MyTable
WHERE Field1 = par1
AND Field2 = par2
;

GlobalVar1 NUMBER;
GlobalVar2 NUMBER;
GlobalCursorVar NUMBER;

PROCEDURE [i]MyProcedure1[/i](par1 IN VARCHAR2, par2 VARCHAR2) IS
GlobalVar1 := par1;
GlobalVar2 := par2;
BEGIN

OPEN myGlobalCursor(par1, par2);
FETCH myGlobalCursor
INTO GlobalCursorVar;
CLOSE myGlobalCursor;

--Here goes all the procedure
.
.
.
END;

PROCEDURE [i]MyProcedure2[/i](par1 IN VARCHAR2, par2 VARCHAR2) IS
GlobalVar1 := par1;
GlobalVar2 := par2;
BEGIN

OPEN myGlobalCursor(par1, par2);
FETCH myGlobalCursor
INTO GlobalCursorVar;
CLOSE myGlobalCursor;

--Here goes all the procedure
.
.
.
END;

--All Package Procedures/Functions
.
.
.


Thanks to anyone who let me a hand with this trouble.
Re: GLOBAL CURSOR ON A PACKAGE. [message #545680 is a reply to message #545679] Thu, 01 March 2012 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>Thanks to anyone who let me a hand with this trouble.
TROUBLE? What Trouble?

>So my trouble is that i want to create some like a global cursor on DB package,
what exactly is a "global cursor"?
It is related to a unicorn?
Re: GLOBAL CURSOR ON A PACKAGE. [message #545683 is a reply to message #545680] Thu, 01 March 2012 12:07 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

Quote:

>Thanks to anyone who let me a hand with this trouble.
TROUBLE? What Trouble?

Quote:


My problem:


* I really dont´know how to declare into the spec.
* I really don´t know how to use it on body.

Read...
Quote:

>So my trouble is that i want to create some like a global cursor on DB package,
what exactly is a "global cursor"?
It is related to a unicorn?

Quote:

some like a global cursor on DB package, i know you can create global variables and use it on all the procedures/function inside the package

And, read..

P.D. I never say that Global Cursors exist, just want to know if its possible use the concept of the global variables in a cursor, some like "Global Cursor"... I´m just asking for that, and if they exist i´m asking how to use it... Simple!
And please: If your reply wont help, dont do it... Thanks Professionals
Thanks!
Re: GLOBAL CURSOR ON A PACKAGE. [message #545685 is a reply to message #545683] Thu, 01 March 2012 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use REF CURSOR type for this.

Regards
Michel
Re: GLOBAL CURSOR ON A PACKAGE. [message #545686 is a reply to message #545679] Thu, 01 March 2012 12:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
So what's the issue? Yes, you can create global cursor variable:

CREATE OR REPLACE
  PACKAGE MyPackageName
    IS
      CURSOR myGlobalCursor(par1 IN VARCHAR2)
        IS
          SELECT  ENAME
            FROM  EMP
            WHERE JOB = par1;
      PROCEDURE MyProcedure1(par1 IN VARCHAR2);
      PROCEDURE MyProcedure2(par1 IN VARCHAR2);
END;
/
CREATE OR REPLACE
  PACKAGE BODY MyPackageName
    IS
      PROCEDURE MyProcedure1(par1 IN VARCHAR2)
        IS
        BEGIN
            FOR v_rec IN myGlobalCursor(par1) LOOP
              DBMS_OUTPUT.PUT_LINE(v_rec.ename);
            END LOOP;
      END;
      PROCEDURE MyProcedure2(par1 IN VARCHAR2)
        IS
        BEGIN
            FOR v_rec IN myGlobalCursor(par1) LOOP
              DBMS_OUTPUT.PUT_LINE(v_rec.ename);
            END LOOP;
      END;
END;
/
SQL> SET SERVEROUTPUT ON
SQL> EXEC MyPackageName.MyProcedure1('SALESMAN');
ALLEN
WARD
MARTIN
TURNER

PL/SQL procedure successfully completed.

SQL> EXEC MyPackageName.MyProcedure1('MANAGER');
JONES
BLAKE
CLARK

PL/SQL procedure successfully completed.

SQL> 


SY.
Re: GLOBAL CURSOR ON A PACKAGE. [message #545690 is a reply to message #545686] Thu, 01 March 2012 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, there is no need of a "global" cursor as you don't use the "global" (and above all "external" property).
The usefulness of a global variable is that it is assigned (opened) and used (fetched) in different parts of the program/package.
Here you cannot refer the "global" cursor anywhere but in the package and more you cannot fetch it anywhere but in the procedure it is opened.

Regards
Michel

[Updated on: Thu, 01 March 2012 12:49]

Report message to a moderator

Re: GLOBAL CURSOR ON A PACKAGE. [message #545695 is a reply to message #545690] Thu, 01 March 2012 13:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Michel Cadot wrote on Thu, 01 March 2012 13:48
Here you cannot refer the "global" cursor anywhere but in the package and more you cannot fetch it anywhere but in the procedure it is opened.


Not really. As long as it is declared as public (in package specification) we can reference it outside the package. Moreover, we can create records based on it:

SQL> CREATE OR REPLACE
  2    PACKAGE MyPackageName
  3      IS
  4        CURSOR myGlobalCursor(par1 IN VARCHAR2)
  5          IS
  6            SELECT  ENAME
  7              FROM  EMP
  8              WHERE JOB = par1;
  9  END;
 10  /

Package created.

SQL> DECLARE
  2      v_rec MyPackageName.myGlobalCursor%ROWTYPE;
  3  BEGIN
  4      OPEN MyPackageName.myGlobalCursor('SALESMAN');
  5      LOOP
  6        FETCH MyPackageName.myGlobalCursor
  7          INTO v_rec;
  8        EXIT WHEN MyPackageName.myGlobalCursor%NOTFOUND;
  9        DBMS_OUTPUT.PUT_LINE(v_rec.ename);
 10      END LOOP;
 11  END;
 12  /
ALLEN
WARD
MARTIN
TURNER

PL/SQL procedure successfully completed.

SQL> 


SY.
Re: GLOBAL CURSOR ON A PACKAGE. [message #545696 is a reply to message #545695] Thu, 01 March 2012 13:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
And one more usage of global cursor would be code maintenace. If we have multiple procedures/functions utilizing same piece of business logic expressed in SELECT statement we can declare it globally (as public or private depending on situation) and reference it where needed. This way if business logic slightly changes - same element number and types is select list - all we need is to modify global cursor.

SY.
Re: GLOBAL CURSOR ON A PACKAGE. [message #545698 is a reply to message #545696] Thu, 01 March 2012 13:59 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

Thanks, You just solved all my doubts.
Re: GLOBAL CURSOR ON A PACKAGE. [message #545699 is a reply to message #545696] Thu, 01 March 2012 14:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
And this example shows cursor can be open by one PL/SQL block and then fetched by other PL/SQL block and closed by third PL/SQL block:

SQL> EXEC OPEN MyPackageName.myGlobalCursor('SALESMAN');

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      v_rec MyPackageName.myGlobalCursor%ROWTYPE;
  3  BEGIN
  4      LOOP
  5        FETCH MyPackageName.myGlobalCursor
  6          INTO v_rec;
  7          EXIT WHEN MyPackageName.myGlobalCursor%NOTFOUND;
  8        DBMS_OUTPUT.PUT_LINE(v_rec.ename);
  9      END LOOP;
 10  END;
 11  /
ALLEN
WARD
MARTIN
TURNER

PL/SQL procedure successfully completed.

SQL> EXEC CLOSE MyPackageName.myGlobalCursor;

PL/SQL procedure successfully completed.

SQL> 


SY.

[Updated on: Thu, 01 March 2012 14:03]

Report message to a moderator

Re: GLOBAL CURSOR ON A PACKAGE. [message #545705 is a reply to message #545679] Thu, 01 March 2012 14:29 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
@syakobson:


I gave a try, I think Micheal was meant the below (I just think)...

scott@orcl> create or replace package pkg_call
  2  is
  3  procedure p_call;
  4  end pkg_call;
  5  /

Package created.

Elapsed: 00:00:00.03


 scott@orcl> create or replace package body pkg_call
  2  is
  3  
  4     procedure p_call
  5     is
  6      v_name varchar2(20);
  7      begin
  8  
  9      open pkg_global.c1('SMITH');
 10      fetch pkg_global.c1.ename into v_name;
 11      dbms_output.put_line(v_name);
 12    end p_call;
 13  
 14  end pkg_call;
 15  /


Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.04
scott@orcl> show errors;
Errors for PACKAGE BODY PKG_CALL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
10/11    PLS-00225: subprogram or cursor 'C1' reference is out of scope


Regards,
Pointers
Re: GLOBAL CURSOR ON A PACKAGE. [message #545706 is a reply to message #545705] Thu, 01 March 2012 14:31 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
sorry that was copy paste error..please ignore my previous post...let me repost the stuff.
Re: GLOBAL CURSOR ON A PACKAGE. [message #545707 is a reply to message #545706] Thu, 01 March 2012 14:42 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
I think the below was meant by Micheal ( I just think).

scott@orcl> create or replace package pkg_global
  2      is
  3        cursor c1(p_name in varchar2)
  4        is
  5          select *
  6            from emp
  7            where ename=p_name;
  8     end pkg_global;
  9  /

Package created.

Elapsed: 00:00:00.32

scott@orcl> create or replace package pkg_call
  2      is
  3      procedure p_call;
  4     end pkg_call;
  5    /

Package created.

Elapsed: 00:00:00.04


scott@orcl> create or replace package body pkg_call
  2      is
  3      
  4         procedure p_call
  5         is
  6          v_name varchar2(20);
  7         begin
  8      
  9          open pkg_global.c1('SMITH');
 10         fetch pkg_global.c1.ename into v_name;
 11         dbms_output.put_line(v_name);
 12       end p_call;
 13   
 14     end pkg_call;
 15     /

Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.06
scott@orcl> show errors
Errors for PACKAGE BODY PKG_CALL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/8     PL/SQL: SQL Statement ignored
10/14    PLS-00225: subprogram or cursor 'C1' reference is out of scope



Regards,
Pointers
Re: GLOBAL CURSOR ON A PACKAGE. [message #545712 is a reply to message #545707] Thu, 01 March 2012 16:00 Go to previous message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
And what is:

fetch pkg_global.c1.ename into v_name;


That's not the way how you fetch:

create or replace
  package body pkg_call
    is
      procedure p_call
        is
           v_rec pkg_global.c1%rowtype;
        begin
            open pkg_global.c1('SMITH');
            fetch pkg_global.c1 into v_rec;
            dbms_output.put_line(v_rec.ename);
      end p_call;
end pkg_call;
/

Package body created.

SQL> 


SY.
Previous Topic: NEWBIE: Trying to kill one users sessions using a procedure
Next Topic: When used variable in a open cursor statement
Goto Forum:
  


Current Time: Fri Oct 24 13:07:28 CDT 2014

Total time taken to generate the page: 0.11202 seconds