How do I use Table type Variable in Procedure

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 27 Sep 2001 19:11:10 -0400
Message-ID: <3BB3B20E.10D7@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 Received on Thu Sep 27 2001 - 18:11:10 CDT