Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I use Table type Variable in Procedure
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