Re: Executing Stored Procedure Using REF CURSOR

From: Jan Korecki <Jan.Korecki_at_contactor.se>
Date: Sat, 22 Nov 2003 19:12:19 +0100
Message-ID: <3fbfa738$0$97841$57c3e1d3@news3.bahnhof.se>

> Janne, I really appreciate your response.  Your post did help me
> understand strong typed versus weakly typed cursors, but I'm still
> missing something.  How do you use a strongly typed cursor if the
> query involves a join between two tables? What do I use in place of 
> "all_objects%ROWTYPE"? This is probably a basic PL/SQL statement, but
> I'm a relatively new to PL/SQL so .... I appreciate your patience.
> 
> Again, thanks in advance for your help,
> Mona

Hi!

I'll give you 2 ansvers. A long and a longer example :-) The first one is a working solution for your problem. The other is a revorked example of my last message. If its to much, let that one slide for now.

Since you are new in pl/sql ill recommend that you look up the oracle dokumentation (at http://otn.oracle.com). The "PL/SQL User's Guide and Reference" is a good place to learn pl/sql.

The direct link for the 9i doc is at:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm

If you dont have a profile at otn you will be prompted to create one. Its free. Otn is a really greate resource. They have the documentation for older oracle releases too.

Now for the examples:

• your package code

CREATE OR REPLACE PACKAGE Report_Pkg
AS

• Your recordset. Since you are using parts of the tables columns you
• have to declare the record type
• in my case all the columntypes are varchar2 in both tables TYPE tableAB_rec IS RECORD( field1 TABLE1.field1%TYPE, field2 TABLE1.field2%TYPE, field3 TABLE2.field3%TYPE);
• Your strongly typed ref cursor will always return tableAB_rec TYPE Rpt_type IS REF CURSOR RETURN tableAB_rec;
• You should always aim to have all procedures and functions in packages
• Its good coding practice. PROCEDURE Inv_Report_proc ( rpt_cursor OUT Report_Pkg.rpt_type);

END Report_Pkg;
/

CREATE OR REPLACE PACKAGE BODY Report_Pkg AS

• removed the in parameter.
• The procedure Inv_Report_proc only returns the ref cursor to the calling program PROCEDURE Inv_Report_proc ( rpt_cursor OUT Report_Pkg.rpt_type) IS BEGIN OPEN rpt_cursor FOR SELECT A.FIELD1 , A.FIELD2 , B.FIELD3 FROM TABLE1 A , TABLE2 B WHERE A.FIELD5 = B.FIELD5 ;

END Inv_report_proc;

END Report_Pkg;
/

• and the test SQL> --The PL/SQL to run the procedure: SQL> SET SERVEROUTPUT ON SQL> declare rpt_cursor report_pkg.Rpt_type; 2 resultset rpt_cursor%ROWTYPE; 3 begin 4 Report_Pkg.inv_report_proc(rpt_cursor); 5 fetch rpt_cursor into resultset; 6 while rpt_cursor%found loop 7 dbms_output.put_line(resultset.field1); 8 fetch rpt_cursor into resultset; 9 end loop; 10 end; 11 / 1 1

PL/SQL procedure successfully completed.

Next is a revoked example of my last message. I also added the use of sys_refcursor (new in 9i). You can let this slide if you think its too much. Check the oracle documentation instead.

CREATE OR REPLACE PACKAGE Testrefcur
AS

• With a stongly typed ref rursor the returntype is known at complietime
• This is one way to do it when all tablecolumns are in the select:
• TYPE all_obj_refcur_type_strong IS REF CURSOR RETURN all_objects%ROWTYPE;
• This is how you have to do it if you dont have all columns in your select. TYPE Sample_strong_rec IS RECORD (object_name all_objects.OBJECT_NAME%TYPE);
• This strongly typed ref cursor always RETURN Sample_strong_rec TYPE all_obj_refcur_type_strong IS REF CURSOR RETURN Sample_strong_rec;

--weak ref cursor. Can return anything
TYPE all_obj_refcur_type_weak IS REF CURSOR;

• with a weakly typed ref cursor you have to show what the record type will be at runtime TYPE Sample_rec IS RECORD (objectName VARCHAR2(30));
• You could for example declare another record with name and owner and still use the same ref cursor. TYPE Sample_rec2 IS RECORD (objectName VARCHAR2(30), objectOwner VARCHAR2(30));
• In 9i you dont have to declare a ref cursor. You can use a sys_refcursor directly in the procedures
• look at the example in the procedures show- and getAllObjects_9i

PROCEDURE showAllObjects_Strong;
-- I added the p_object_type to show you that you can change the resultset
PROCEDURE showAllObjects_weak(p_object_type IN VARCHAR2 DEFAULT 'ALL_OBJECTS');
PROCEDURE showAllObjects_9i(p_object_type IN VARCHAR2 DEFAULT 'ALL_OBJECTS'); END Testrefcur;
/

CREATE OR REPLACE PACKAGE BODY Testrefcur AS

• private procedure declarations PROCEDURE getAllObjects_strong(allObj_refcur OUT all_obj_refcur_type_strong); PROCEDURE getAllObjects_weak(p_object_type IN VARCHAR2, allObj_refcur OUT all_obj_refcur_type_weak);
• note that the sys_Refcursor is not declared in the package specification PROCEDURE getAllObjects_9i(p_object_type IN VARCHAR2, allObj_refcur OUT sys_refcursor);
• end private procedure declarations

PROCEDURE getAllObjects_Strong(allObj_refcur OUT all_obj_refcur_type_strong) IS
BEGIN
   OPEN allObj_refcur FOR

              SELECT object_name FROM all_objects WHERE ROWNUM <11;

END getAllObjects_strong;

PROCEDURE getAllObjects_weak(p_object_type IN VARCHAR2, allObj_refcur OUT all_obj_refcur_type_weak)
IS
BEGIN

	 IF (p_object_type ='ALL_TABLES') THEN
   	 	OPEN allObj_refcur FOR
   	   		 SELECT table_name,owner FROM all_tables WHERE ROWNUM <11;
	 ELSE
	 	OPEN allObj_refcur FOR
   	   		 SELECT object_name FROM all_objects WHERE ROWNUM <11;
	 END IF;
	 	

END getAllObjects_weak;

• Note that the only difference with the procedure
• getAllObjects_weak is "sys_refcursor" PROCEDURE getAllObjects_9i(p_object_type IN VARCHAR2, allObj_refcur OUT sys_refcursor) IS BEGIN IF (p_object_type ='ALL_TABLES') THEN OPEN allObj_refcur FOR SELECT table_name, owner FROM all_tables WHERE ROWNUM <11; ELSE OPEN allObj_refcur FOR SELECT object_name FROM all_objects WHERE ROWNUM <11; END IF;

END getAllObjects_9i;

PROCEDURE showAllObjects_strong
IS

rpt_cursor	all_obj_refcur_type_strong;
resultset   rpt_cursor%ROWTYPE;	  --note the differens with the weakly typed

BEGIN
dbms_output.put_line('Strongly');

   getAllObjects_Strong( rpt_cursor);
   FETCH rpt_cursor INTO resultset ;
   WHILE rpt_cursor%FOUND LOOP

   	  dbms_output.put_line(resultset.object_name);
	  FETCH rpt_cursor INTO resultset;

   END LOOP; END showAllObjects_strong;

PROCEDURE showAllObjects_weak(p_object_type IN VARCHAR2 DEFAULT 'ALL_OBJECTS')
IS

rpt_cursor	all_obj_refcur_type_weak;
resultset 	Sample_rec;
resultset2 	Sample_rec2;

BEGIN
dbms_output.put_line('Weakly typed');

   getAllObjects_weak(p_object_type, rpt_cursor);

   IF (p_object_type='ALL_TABLES') THEN

            FETCH rpt_cursor INTO resultset2 ;

   	 WHILE rpt_cursor%FOUND LOOP
   	 	   dbms_output.put_line(resultset2.objectName || ' ' || 
resultset2.objectOwner);
	  	   FETCH rpt_cursor INTO resultset2;
   	 END LOOP;
   ELSE	
      FETCH rpt_cursor INTO resultset ;
   	 WHILE rpt_cursor%FOUND LOOP
   	 	   dbms_output.put_line(resultset.objectName);
	  	   FETCH rpt_cursor INTO resultset;
   	 END LOOP;

   END IF; END showAllObjects_weak;

• same as above with the exception of sys_refcursor PROCEDURE showAllObjects_9i(p_object_type IN VARCHAR2 DEFAULT 'ALL_OBJECTS') IS rpt_cursor sys_refcursor; resultset Sample_rec; resultset2 Sample_rec2;

BEGIN
dbms_output.put_line('Weakly typed');

   getAllObjects_weak(p_object_type, rpt_cursor);

   IF (p_object_type='ALL_TABLES') THEN

            FETCH rpt_cursor INTO resultset2 ;

   	 WHILE rpt_cursor%FOUND LOOP
   	 	   dbms_output.put_line(resultset2.objectName || ' ' || 
resultset2.objectOwner);
	  	   FETCH rpt_cursor INTO resultset2;
   	 END LOOP;
   ELSE	
      FETCH rpt_cursor INTO resultset ;
   	 WHILE rpt_cursor%FOUND LOOP
   	 	   dbms_output.put_line(resultset.objectName);
	  	   FETCH rpt_cursor INTO resultset;
   	 END LOOP;

   END IF; END showAllObjects_9i;

END Testrefcur;
/

Hope it helps and that you understand my swinglish (swedish-english) ;-)

Regards,
Janne! Received on Sat Nov 22 2003 - 12:12:19 CST