Re: Help Needed in PL/SQL VARRAYS

George,

CREATE OR REPLACE package arraydatatype as null arr owa.vc arr;
end;
/

Create this package which defaults the array to null then you could use arrays in you procedures.

procedure XXXXX(MyArray in owa.vc arr default arraydatatype.null arr)

is

begin

for i in 1..MyArray.count()

loop

dbms output.put line(MyArray(i));

end loop;

begin

end;

Tom

    George Wilson Mathias wrote in message <37FB72D1.EF96B9F7_at_mci.com>...

    Hello all,
    I need some help in defining arrays. I have a function(at the end) called validate movie which takes an array of movie table. While vlaidating, If I have any validation errors, I need to put those into a new array(error messages, defined in the program).

    Q 1) How do I initialize any array to null or empty ? 
    Q 2) How do I add elements to an array (according to my format) ? 
    Q 3) How do I return an array ? 

    Sample code which does all of these will be very helpful.

    Thanks a tonn
    George Mathias
    george.mathias_at_wcom.com

    DROP TYPE movie table FORCE;
    CREATE OR REPLACE TYPE movie table AS OBJECT     (

       movie id VARCHAR2(6), 
       title    VARCHAR2(30)) 

    DROP TYPE movie array FORCE;
    CREATE OR REPLACE TYPE movie array AS VARRAY(10)

       OF MOVIE TABLE;
/

    DROP TYPE error message table FORCE;     CREATE OR REPLACE TYPE error message table AS OBJECT     (

       error code    VARCHAR2(30), 
       error message VARCHAR2(500) 

    DROP TYPE error messages array FORCE;     CREATE OR REPLACE TYPE error messages array AS VARRAY(10)       OF error message table
/

    CREATE OR REPLACE validate movie(movies movie array)

       RETURN error messages array IS

       error messages error messages array = 
     error messages array 

    (ERROR MESSAGE TABLE('BLA','BLA'));     BEGIN
       return error messages;
    END;
/

--