Home » SQL & PL/SQL » SQL & PL/SQL » Multi Level Table Type Output (10G)
Multi Level Table Type Output [message #648796] Thu, 03 March 2016 18:18 Go to next message
vikram_2050
Messages: 10
Registered: June 2005
Location: bangalore
Junior Member
How to retrieve the output from multilevel table type in Oracle

Below is package spec

create or replace PACKAGE pack
AS

TYPE rec IS RECORD (
   name varchar2(240)
      );
TYPE t IS TABLE OF rec;

TYPE rec1 IS RECORD (
col1 varchar2(240),
col2  varchar2(240),
col3 t,
col4 t);


type t1 is TABLE OF rec1;

PROCEDURE proc(
      p_result OUT t1 
            );
end;



In package body proc is defined to populate output variable t1.


DECLARE
  p_result_out pack.t1;
  n_node_type_cnt
BEGIN
  p_result_out:=t1();
  
  pack.proc(p_result_out);

n_node_type_cnt:=p_result_out.first;

--dbms_output.put_line (p_result_out(1).col1 );
/*
 FOR i IN p_result_out.first .. p_result_out.last
 loop
 --dbms_output.put_line (p_result(i).col1 );
 null;
 end loop; */

  end;




encountered with below error message
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 17
06531. 00000 -  "Reference to uninitialized collection"
*Cause:    An element or member function of a nested table or varray
           was referenced (where an initialized collection is needed)
           without the collection having been initialized.
*Action:   Initialize the collection with an appropriate constructor
           or whole-object assignment.







Thanks in advance
Re: Multi Level Table Type Output [message #648797 is a reply to message #648796] Thu, 03 March 2016 19:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9082
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl> CREATE OR REPLACE PACKAGE pack
  2  AS
  3    TYPE rec IS RECORD
  4  	 (name	VARCHAR2(240));
  5    TYPE t IS TABLE OF rec;
  6    TYPE rec1 IS RECORD
  7  	 (col1	VARCHAR2(240),
  8  	  col2	VARCHAR2(240),
  9  	  col3	t,
 10  	  col4	t);
 11    TYPE t1 IS TABLE OF rec1;
 12    PROCEDURE proc
 13  	 (p_result OUT t1);
 14  END pack;
 15  /

Package created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY pack
  2  AS
  3    PROCEDURE proc
  4  	 (p_result OUT t1)
  5    IS
  6    BEGIN
  7  	 p_result := t1();
  8  	 p_result.EXTEND(2);
  9  	 p_result(1).col1 := 'A';
 10  	 p_result(2).col1 := 'B';
 11    END proc;
 12  END pack;
 13  /

Package body created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> DECLARE
  2    p_result_out	pack.t1;
  3  BEGIN
  4    pack.proc (p_result_out);
  5    FOR i IN 1 .. p_result_out.COUNT LOOP
  6  	 DBMS_OUTPUT.PUT_LINE (p_result_out(i).col1);
  7    END LOOP;
  8  END;
  9  /
A
B

PL/SQL procedure successfully completed.


[Updated on: Thu, 03 March 2016 19:25]

Report message to a moderator

Re: Multi Level Table Type Output [message #649442 is a reply to message #648797] Fri, 25 March 2016 13:56 Go to previous message
vikram_2050
Messages: 10
Registered: June 2005
Location: bangalore
Junior Member
Thanks for your post. It was helpful.
Previous Topic: Table LOCK
Next Topic: Multi Set operator (MULTISET UNION DISTINCT) in PL/SQL
Goto Forum:
  


Current Time: Tue Apr 16 00:39:10 CDT 2024