Re: Create Stored Proc returning table

A copy of this was sent to Masseglia <patrick.masseglia_at_renault.com> (if that email address didn't require changing) On Fri, 17 Sep 1999 11:32:19 +0200, you wrote:

>Hi,
>
>When I try to create the following stored proc:
>
>-- PACKAGE
>CREATE OR REPLACE PACKAGE GLPAYS AS
> TYPE paystabtyp IS TABLE OF BM50_PVTE%ROWTYPE
> INDEX BY BINARY_INTEGER;
>
> PROCEDURE lire_pays (idpays IN CHAR, tab_pays out paystabtyp);
>END GLPAYS;
>/
>
>
>-- PACKAGE BODY
>CREATE OR REPLACE PACKAGE BODY GLPAYS AS
>
> PROCEDURE lire_pays (idpays IN CHAR, tab_pays OUT glpays.paystabtyp) IS
>
> code_pays CHAR(6);
> lib_pays CHAR(20);
> recpays glpays.paystabtyp;
> CURSOR C1(idpays char) IS
> SELECT *
> FROM BM50_PVTE
> WHERE BM50ID_PVTE > idpays;
> BEGIN
> OPEN c1(idpays);
> FETCH c1 INTO recpays;
> tab_pays(1) := recpays;
> dbms_output.put_line('tabpays : ' || tab_pays(1));
> END lire_pays;
>
>END GLPAYS;
>/
>
>
>In order to work with a table of data instead of cursor, Oracle 8.04
>Return me errors
>about type mismatch beetween FETCH cursor and INTO variable.
>IS somebody can help me?
>

you are fetching a ROW into a TABLE.

Try

fetch c1 into recpays(1);

>Patrick Masseglia.:
>

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation