Re: Returning a full cursor record from procedure

The problem is that the cursor rowtype is defined strictly local to the stored procedure. You will have to do one of a few things: 1. Use a package header to define a global public Record type matching the cursor rowtype.
Then the caller can base a variable on the record type, and pass it in as a parameter to your procedure.
2. Make the cursor return a table or view rowtype, that is accessible to the caller. For example:
cursor get_tables is select * from user_tables; Then the caller can base a variable on the table or view rowtype, and pass it in as a parameter to your procedure.
3. Declare the cursor in a package header, so it is public global; then the caller can declare a variable based on the cursor rowtype.

If you need to return more than one row, use a Nested Table (formally known as PL/SQL Table), which is basically an array. If you're using Oracle 7.3 or above, the PL/SQL table can be of a record or rowtype. Then have the procedure populate the array and return it. If you're using 7.2 or before, you're kinda screwed. I don't know if you can use REF CURSOR with VB 5.0. REF CURSOR is a handle to a cursor that you can pass back to the 3GL app to enable persistent access to the cursor.

• Dan Clamage

T'was Brillig,
And the slithy toves
Did gyre and gimbol in the wabe;
All mimsey were the borogroves,
And the mome raths outgrabe.
- Lewis Carroll

meyer_b_at_rocketmail.com wrote in article <6kenlj$un2$1_at_nnrp1.dejanews.com>...
> How can I (or can I?) return a complete set of cursor records from a
> catalogued procedure?
>
> Take the following simple proc:
>
> CREATE OR REPLACE PROCEDURE PROC_DEMO_SELECT
> (p_case_id IN OUT NUMBER )
> IS
> CURSOR cur_demo IS
> SELECT CASE_ID, CASE_ENTRY_DATE
> FROM CASES;
> v_case_record cur_demo%rowtype;
> BEGIN
> OPEN CUR_DEMO;
> LOOP
> FETCH cur_demo INTO v_case_record;
> EXIT WHEN CUR_DEMO%NOTFOUND;
> p_case_id := v_case_record.CASE_ID;
> END LOOP;
> CLOSE CUR_DEMO;
> END;
> /
>
> This compiles fine. What I want to do though, is return the entire
 record
> v_case_record, but I can't figure out any way to put a parameter called
> p_case_record, defined as a RECORD, in the CREATE procedure statement.
 Is
> there anyway to do this?
>
> If it makes a difference, I want to pass the output of this procedure to
> Visual Basic 5.0 as a record set.
>
> Many thanks to anyone who can help.
>
> Bob
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
  Received on Thu May 28 1998 - 00:00:00 CEST