 GLOBAL CURSOR ON A PACKAGE. [message #545679] |
Thu, 01 March 2012 11:44  |
 |
itralarcon
Messages: 33
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 #545686 is a reply to message #545679] |
Thu, 01 March 2012 12:24  |
Solomon Yakobson
Messages: 1399
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 |
 |
Michel Cadot
Messages: 54204
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 |
Solomon Yakobson
Messages: 1399
Registered: January 2010
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 01 March 2012 13:48Here 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 |
Solomon Yakobson
Messages: 1399
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 #545699 is a reply to message #545696] |
Thu, 01 March 2012 14:02 |
Solomon Yakobson
Messages: 1399
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 |
pointers
Messages: 324
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 #545707 is a reply to message #545706] |
Thu, 01 March 2012 14:42 |
pointers
Messages: 324
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 |
Solomon Yakobson
Messages: 1399
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.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
