stored procedure in oracle.

Question

How to use array( Varray) in store procedure. Actually,i have make a stored procedure from which i retrieve a list of elements.

For example:

create or replace procedure GetTargetFields ( fileformat  in varchar2,
                                              filefields out Vrray(4) )
IS

BEGIN

 SELECT id 
   INTO filefields  
   FROM tablename;

END;

Answer 1

Two things:

You need to declare a named type -- you can't use VARRAY directly in a parameter declaration. (Unless this has changed in 11g.)

You need to use BULK COLLECT to use a single query to populate a collection.

Example:

CREATE TYPE fieldlist AS VARRAY(4) OF NUMBER;
CREATE PROCEDURE GetTargetFields( filefields OUT fieldlist )
  AS
  BEGIN
    SELECT id BULK COLLECT INTO filefields FROM tablename;
  END;

Answer 2

Hi Niraj,

use BULK COLLECT INTO:

SQL> CREATE OR REPLACE TYPE vrray_4 AS VARRAY(4) OF VARCHAR2(10);
  2  /

Type created
SQL> CREATE OR REPLACE PROCEDURE GetTargetFields(fileformat IN VARCHAR2,
  2                                              filefields OUT vrray_4) IS
  3  BEGIN
  4     SELECT dummy BULK COLLECT INTO filefields FROM dual;
  5  END;
  6  /

Procedure created
SQL> DECLARE
  2     x vrray_4;
  3  BEGIN
  4     GetTargetFields(NULL, x);
  5  END;
  6  /

PL/SQL procedure successfully completed

Also make sure that your query doesn't return more than 4 rows (for a VARRAY(4)) or you will run into ORA-22165

Answer 3

Hello, Niraj. You should use the principles Vincent provided, but I suggest you use nested table type instead of varray in case you don't need exactly varray type in your logic. This will save you from ORA-22165 error if the query returns more then 4 rows - nested tabled will be automatically expanded to the size needed. You define nested table type as follows:

declare
  type TStrTab is table of varchar2(10);
  fStrTab TStrTab := TStrTab();
begin
  select ... bulk collect into fStrTab from...
end;

More information about PL/SQL collection types can be found in official Oracle PL-SQL User's Guide and Reference Chapter 5.