CODEWITHSUNDEEP
oracle-databaseplsql
Niraj Choubey
Niraj Choubey

Reputation: 4040

How to populate an array in an oracle stored procedure?

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 Varray(4) )
IS

BEGIN

 SELECT id 
   INTO filefields  
   FROM tablename;

END;

Upvotes: 0

Views: 26236

Answers (3)

andr
andr

Reputation: 1646

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.

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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

Upvotes: 3

Dave Costa
Dave Costa

Reputation: 48121

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;

Upvotes: 1

Related Questions