Is it true that REF CURSOR only available in PL/SQL Version 2.3+?
I tried to use it in version 1.2 but doesn't work.
"M_Belk" <mona.r.belk_at_lmco.com> wrote in message
news:514b11a9.0311220628.3ebd1b56_at_posting.google.com...
> 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:38:18 CST
