Home » SQL & PL/SQL » SQL & PL/SQL » Nested table as a out parameter in procedure (11g db, windows 7)
Nested table as a out parameter in procedure [message #644356] Tue, 03 November 2015 12:19 Go to next message
aspire
Messages: 18
Registered: September 2015
Location: TVN
Junior Member
i am trying to use a nested table as a out parameter in procedure. It is throwing error when i execute.
---pkg spec
CREATE OR REPLACE PACKAGE  PKG_BBM  IS 
TYPE REC IS RECORD(EMPNO NUMBER, ENAME VARCHAR2(90));
TYPE TAB IS TABLE OF REC;
PROCEDURE PRC1(LV_TAB OUT TAB);
END PKG_BBM;
/
----pkg body
CREATE OR REPLACE PACKAGE BODY PKG_BBM IS
PROCEDURE PRC1(LV_TAB OUT TAB) IS
BEGIN
FOR I IN (SELECT EMPNO,ENAME FROM EMP) LOOP
LV_TAB.EXTEND;
LV_TAB(LV_TAB.LAST).EMPNO := I.EMPNO;
LV_TAB(LV_TAB.LAST).ENAME := I.ENAME;
END LOOP;
END PRC1;
END PKG_BBM;
/
-----------execution part
DECLARE
TA PKG_BBM.TAB ;
BEGIN
PKG_BBM.PRC1(TA);
END;


ORA-06531 --reference to uninitialized collections

thanks in advance
Re: Nested table as a out parameter in procedure [message #644358 is a reply to message #644356] Tue, 03 November 2015 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You must initialize a collection before you can extend it: "LV_TAB := TAB();".

SQL> CREATE OR REPLACE PACKAGE  PKG_BBM  IS
  2  TYPE REC IS RECORD(EMPNO NUMBER, ENAME VARCHAR2(90));
  3  TYPE TAB IS TABLE OF REC;
  4  PROCEDURE PRC1(LV_TAB OUT TAB);
  5  END PKG_BBM;
  6  /

Package created.

SQL> ----pkg body
SQL> CREATE OR REPLACE PACKAGE BODY PKG_BBM IS
  2  PROCEDURE PRC1(LV_TAB OUT TAB) IS
  3  BEGIN
  4    LV_TAB := TAB();
  5  FOR I IN (SELECT EMPNO,ENAME FROM EMP) LOOP
  6  LV_TAB.EXTEND;
  7  LV_TAB(LV_TAB.LAST).EMPNO := I.EMPNO;
  8  LV_TAB(LV_TAB.LAST).ENAME := I.ENAME;
  9  END LOOP;
 10  END PRC1;
 11  END PKG_BBM;
 12  /

Package body created.

SQL> DECLARE
  2  TA PKG_BBM.TAB ;
  3  BEGIN
  4  PKG_BBM.PRC1(TA);
  5  END;
  6  /

PL/SQL procedure successfully completed.
Re: Nested table as a out parameter in procedure [message #644359 is a reply to message #644358] Tue, 03 November 2015 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note there is a better way to do it:
SQL> CREATE OR REPLACE PACKAGE BODY PKG_BBM IS
  2  PROCEDURE PRC1(LV_TAB OUT TAB) IS
  3  BEGIN
  4    SELECT EMPNO,ENAME BULK COLLECT INTO LV_TAB FROM EMP;
  5  END PRC1;
  6  END PKG_BBM;
  7  /

Package body created.

SQL> DECLARE
  2  TA PKG_BBM.TAB ;
  3  BEGIN
  4  PKG_BBM.PRC1(TA);
  5  END;
  6  /

PL/SQL procedure successfully completed.
Re: Nested table as a out parameter in procedure [message #644360 is a reply to message #644358] Tue, 03 November 2015 12:30 Go to previous message
aspire
Messages: 18
Registered: September 2015
Location: TVN
Junior Member
Thank you very much...it is working...
Previous Topic: Complex query (merged)
Next Topic: Update blob Column
Goto Forum:
  


Current Time: Mon Mar 18 21:03:43 CDT 2024