CODEWITHSUNDEEP
oracle-databaseplsql
javanoob
javanoob

Reputation: 6410

How to return collection type from plsql stored procedure

I have the below stored procedure in PL/SQL:

CREATE OR REPLACE PROCEDURE sample_procedure AS 
DECLARE 
    TYPE list_of_names_t 
      IS TABLE OF emp.emp_index%TYPE; 
    ignoreIndexes LIST_OF_NAMES_T := List_of_names_t();
BEGIN
-- Logic here which fills the values in the collection ignoreIndexes   
END;

When in invoke this stored procedure from outside like below:

    SET SERVEROUTPUT ON
    EXEC sample_procedure
    -- Line YY

@ Line YY, I want to retrieve the records from emp table where index is not there in the ignoreindexes collection which was prepared in the stored procedure.

1) How to return the nested table ignoreindexes which was created in stored procedure to the outside world so that i can use the indexes in that table

Thanks in Advance

Upvotes: 2

Views: 22265

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

First, they type would need to be declared outside the procedure in order for the type definition to be visible to code outside the procedure. You can either declare the type in SQL

CREATE TYPE list_of_names_t
    AS TABLE OF NUMBER;

or you can declare it in PL/SQL

CREATE OR REPLACE PACKAGE types_package
AS
  TYPE list_of_names_t
    IS TABLE OF emp.emp_index%type;
END;

Your procedure would then have to either use and return the SQL type

CREATE OR REPLACE PROCEDURE sample_procedure( 
  p_ignore_indexes OUT list_of_names_t 
) 
AS 
BEGIN
  -- Logic here which fills the values in the collection p_ignore_indexes 
END;

or the PL/SQL type

CREATE OR REPLACE PROCEDURE sample_procedure( 
   p_ignore_indexes OUT types_package.list_of_names_t 
) 
AS 
BEGIN
  -- Logic here which fills the values in the collection p_ignore_indexes 
END;

Of course, if the purpose of your code is to return a collection, it would make more sense to write a function than a procedure

CREATE OR REPLACE FUNCTION sample_function
  RETURN types_package.list_of_names_t
AS 
  ignore_indexes types_package.list_of_names_t;
BEGIN
  -- Logic here which fills the values in the collection ignore_indexes 
  RETURN ignore_indexes;
END;

When you call the procedure, you would do something like

DECLARE
  l_ignore_indexes types_package.list_of_names_t;
BEGIN
  l_ignore_indexes := sample_function; 
  -- Do something with l_ignore_indexes
END;

or

DECLARE
  l_ignore_indexes types_package.list_of_names_t;
BEGIN
  sample_procedure( l_ignore_indexes );
  -- Do something with l_ignore_indexes
END;

Upvotes: 8

Related Questions