M°O wrote:
> 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
>
>
>
Da! - 1.2!?! Must be in a client tool then - currently
desupported version had V2!
--
Regards, Frank van Bortel
Received on Wed Nov 26 2003 - 15:10:30 CST
