SP return Cursor

From: Björn Wächter <bwc_at_p3-solutions.de>
Date: Fri, 02 Dec 2005 11:00:22 +0100
Message-ID: <3vakc2F14q6thU1@news.dfncis.de>

Hello all,

I'm using Oracle 9i and I have some stored procedures returning Cursors. I'd like to test a SP by just calling it an see if there is a result in the cursor. The problem is that in some cases I don't know the format of the result cursor or in some cases the cursor has a lot of rows. I don't want to fetch into a lot of variables with the correct types.
This is such an example:

---

DECLARE

     crs_kpi                 Usertype.ref_cursor;

BEGIN SP_WEB_MES(

P_I_SCALE => 'H',
P_V_MULTISOURCE => 'NORMAL',
P_V_TYPE => 'NORMAL',
P_V_STATUS => 1,

CRS_DATA => crs_mes );

/* --error--
for v_mes in crs_mes loop

     dbms_output.put_line(v_mes.ID);         

end loop;
*/

END;

---

But this is not working. I know there is a row ID. Is there a wach to fetch only the ID?

The Usertype.ref_cursor is defined as:

---

CREATE OR REPLACE PACKAGE usertype AS

    TYPE ref_cursor IS REF CURSOR;

END usertype;
/

---

Received on Fri Dec 02 2005 - 04:00:22 CST