Re: Executing Stored Procedure Using REF CURSOR

From: Jan Korecki <Jan.Korecki_at_contactor.se>
Date: Fri, 21 Nov 2003 12:40:20 +0100
Message-ID: <3fbdf9d8$0$97836$57c3e1d3@news3.bahnhof.se>

M_Belk wrote:

> I have successfully create and compiled a package with a REF CURSOR.
> I have also create and compiled a procedure that uses the REF CURSOR.
> However, when I attempt to run the procedure, I get the following:
> Input truncated to 1 characters
> resultset rpt_cursor%rowtype;
> *
> ERROR at line 2:
> ORA-06550: line 2, column 22:
> PLS-00320: the declaration of the type of this expression is
> incomplete or
> malformed
>
> I have tried using sample code straight from a training guide and get
> the same error message when the resultset is being declared.
>
> I would appreciate any help that you can provide.
>
> The package code:
> create or replace package report_pkg
> AS TYPE Rpt_type is REF CURSOR;
> END report_PKG;
> /
>
> The procedure code:
> create or replace procedure Inv_Report_proc
> ( rpt_cursor in out report_pkg.rpt_type)
> AS
> 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;
> /
>
> The PL/SQL to run the procedure:
> SET SERVEROUTPUT ON
> declare rpt_cursor report_pkg.Rpt_type;
> resultset rpt_cursor%rowtype;
> begin
> inv_report_proc(rpt_cursor);
> fetch rpt_cursor into resultset;
> while rpt_cursor%found loop
> dbms_output.putline(resultset.site);
> fetch rpt_cursor into resultset;
> end loop;
> end;

Hi!
My guess is that your training guide sample shows an example with a strongly typed refcursor. You use a weakly typed cursor in your example.

Here is an example with both strongly and weakly typed ref cursors:

CREATE OR REPLACE PACKAGE testrefcur
AS

• With a stongly typed ref rursor the returntype is known at complietime TYPE all_obj_refcur_type_strong IS REF CURSOR RETURN all_objects%ROWTYPE;

--weak ref cursor
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_type IS RECORD (object_name all_objects.OBJECT_NAME%TYPE);                                                   

PROCEDURE showAllObjects_Strong;
PROCEDURE showAllObjects_weak;

PROCEDURE getAllObjects_strong(allObj_refcur OUT all_obj_refcur_type_strong);
PROCEDURE getAllObjects_weak(allObj_refcur OUT all_obj_refcur_type_weak);

END testrefcur;
/

CREATE OR REPLACE PACKAGE BODY testrefcur AS

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

              SELECT * FROM all_objects WHERE ROWNUM <11;         

END getAllObjects_strong;

PROCEDURE getAllObjects_weak(allObj_refcur OUT all_obj_refcur_type_weak) IS
BEGIN
   OPEN allObj_refcur FOR

              SELECT object_name FROM all_objects WHERE ROWNUM <11;         

END getAllObjects_weak;         

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
IS

rpt_cursor	all_obj_refcur_type_weak;
resultset 	Sample_rec_type;

BEGIN
dbms_output.put_line('Weakly typed');

   getAllObjects_weak( 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_weak;

END testrefcur;

/

test in sqlplus
SQL> set serveroutput on size 100000;

SQL> exec testrefcur.showallobjects_strong; Strongly

/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList
/103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder
/10501902_BasicFileChooserUINe
/105072e7_HttpSessionBindingEv
/106ba0a5_ArrayEnumeration
/106faabc_BasicTreeUIKeyHandle
/10744837_ObjectStreamClass2

PL/SQL-procedur är utförd utan fel.

SQL> exec testrefcur.showallobjects_weak; Weakly typed

/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList
/103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder
/10501902_BasicFileChooserUINe
/105072e7_HttpSessionBindingEv
/106ba0a5_ArrayEnumeration
/106faabc_BasicTreeUIKeyHandle
/10744837_ObjectStreamClass2

PL/SQL-procedur är utförd utan fel.

Hope it helps.

Regards,
Janne! Received on Fri Nov 21 2003 - 05:40:20 CST