Oracle PL/SQL: how to execute a procedure with a varray as out parameter?

I’m writing a stored procedure that will receive a code and return one or more VARCHAR2 values. The number of values will vary according to the code the procedure receives.

I don’t know if this is the best approach (needless to say I’m a beginner), but I created a user-defined type and a table to store these values.

create or replace type t_desc as varray(15) of varchar2(15);

create table all_values(code int, list t_desc);

insert into all_values values (0, t_desc('a','b','c','d'));
insert into all_values values (1, t_desc('a','b'));

Then, I wrote the procedure as follows:

create or replace procedure teste(v_code int, v_list out t_list) is
begin
    select list into v_list from all_values where code = v_code;
end;

This is where I’m stuck. How should this procedure be executed to return the values I’ve stored in the list column?

Answer

you have to declare a variable and pass it in you proc.

declare
  t  t_desc ;
  i int;
begin
  teste(i, t);
end;

to see the results you can itarate through your varray:

FOR x IN 1..t.COUNT LOOP
  dbms_output.put_line(t(x));
END LOOP;

Advertisement

Answer