Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I use Table type Variable in Procedure

Re: How do I use Table type Variable in Procedure

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 28 Sep 2001 05:58:14 -0700
Message-ID: <a20d28ee.0109280458.3cb06c28@posting.google.com>


C Chang <cschang_at_maxinter.net> wrote in message news:<3BB3B20E.10D7_at_maxinter.net>...
> I tried to write a procedure to return multiple records, rather than put
> the procedure in a package. But the Procedure would not complie due to
> the definition of the return variables the PRocedure is as
>
> PROCEDURE GetVendorCity (
> p_city OUT table.column1%TYPE,
> p_state OUT table.column2%TYPE ) IS
> ..
> i NUMBER default 1;
> v_city VARCHAR2(240);
> v_state VARCHAR2(240);
> .. some cursor here more codes
> BEGIN
> .. open cursor
> ... more codes to get v_city and v_state
> LOOP
> i = i+1
>
> p_city(i) := NULL;
> p_state(i) := NULL;
>
> p_city(i) := v_city; -- <- these two has defined above
> p_state(i) := v_state;
>
> END LOOP;
> .. close cursor;
> END procedureName;
>
> When I compiled the procedure , the error said that I can not construct
> those p_city and p_state in the BEGIN section. However If I changed the
> "table.column1%TYPE" on top by the
> TYPE tbl_city IS TABLE OF table.city%TYPE
> INDEX BY BINARY_INTEGER;
> and put the procedure in package, it compiled OK. My question is Why I
> can not use the "table.column1%TYPE" , which is a reference for the
> output? ANd Table type tbl_city is also a reference. On the other
> hand, If I have to use tbl_city, How do I define it in procedure,
> because the procedure does not have definition Head section, as that of
> Package? Thanks
>
> C Chang

It looks like you are mixing up pl/sql tables (which basically are sparse arrays) and ordinary database tables. In your code your type definition of a pl/sql table uses the definition of an ordinary table. That is simply wrong. And as a pl/sql doesn't recognize forward declarations in parameter lists the TYPE definition has to be declared *before* it is used. Hence, you have to put it in a package, there is no other solution. Do you have the book on pl/sql by Steve Feuerstein. If not, buy it, he addresses pl/sql tables in great detail.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Sep 28 2001 - 07:58:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US