Re: Executing Stored Procedure Using REF CURSOR

Jan Korecki <Jan.Korecki_at_contactor.se> wrote in message news:<3fbdf9d8$0$97836$57c3e1d3_at_news3.bahnhof.se>...
>
> 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!

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 Received on Sat Nov 22 2003 - 08:28:39 CST