Insert collection data into a table using a procedure (kindly read the question for detailed explanation)

Question

I would like to insert the collection from the out parameter of a different proc into a table using a procedure. Let me give u the code to explain clearly.

Create type t1 is object
   Name varchar2 (10),
    Address varchar2 (50),
    Pin number);

create type t is table of t1;

create procedure PS1 (P1 out t)
Is 
 TE t := t();
 Begin
  Te.extend();
 Select t1(name, address,pin) bulk 
 collect into TE
 From table1;
-- note: table1 has same table structure as object t1;
End;

Now I have two requirements. For testing purpose, i would like to print one of the data in the collection.

 Declare
 A t := t();
 Begin
 a.extend ();
 PS1(a);
 DBMS.output.put_line(a(1).pin);
 -- after completing this a validation will check if the 
 -- pin has null values.
   If a(1).pin is not null
   Then 
    Insert into t2  values ( 
      a(1).name, a(1).address, b 
      a(1).pin);
    End if;
   --note: table t2 is also of the same structure of object t1;
    End;

Here my code is giving an error as reference to uninitialised collection. But as you can see, i have initialised the collection in declaration itself.

Kindly help me on this.

Answer 1

Here's how.

Sample tables:

SQL> SELECT * FROM table1;

NAME       ADDRESS              PIN
---------- ------------- ----------
CLARK      NEW YORK            7782
KING       NEW YORK            7839
MILLER     NEW YORK            7934

t2 is empty, but has the same structure as table1:

SQL> CREATE TABLE t2
  2  AS
  3     SELECT *
  4       FROM table1
  5      WHERE 1 = 2;

Table created.

Types:

SQL> CREATE OR REPLACE TYPE t1 IS OBJECT
  2  (
  3     name VARCHAR2 (10),
  4     address VARCHAR2 (50),
  5     pin NUMBER
  6  );
  7  /

Type created.

SQL> CREATE OR REPLACE TYPE t IS TABLE OF t1;
  2  /

Type created.

Procedure (note differences; you don't have to initialize nor extend anything. Also, in your code, you're populating te but never returned anything because out parameter's name is p1, not te):

SQL> CREATE OR REPLACE PROCEDURE ps1 (p1 OUT t)
  2  IS
  3  BEGIN
  4     SELECT t1 (name, address, pin)
  5       BULK COLLECT INTO p1
  6       FROM table1;
  7  END;
  8  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     a  t;
  3  BEGIN
  4     ps1 (a);
  5
  6     FOR i IN a.FIRST .. a.LAST
  7     LOOP
  8        DBMS_OUTPUT.put_line (
  9           a (i).name || ', ' || a (i).address || ', ' || a (i).pin);
 10
 11        IF a (i).pin IS NOT NULL
 12        THEN
 13           INSERT INTO t2 (name, address, pin)
 14                VALUES (a (i).name, a (i).address, a (i).pin);
 15        END IF;
 16     END LOOP;
 17  END;
 18  /
CLARK, NEW YORK, 7782
KING, NEW YORK, 7839
MILLER, NEW YORK, 7934

PL/SQL procedure successfully completed.

t2 table's contents:

SQL> SELECT * FROM t2;

NAME       ADDRESS              PIN
---------- ------------- ----------
CLARK      NEW YORK            7782
KING       NEW YORK            7839
MILLER     NEW YORK            7934

SQL>