Nested table declared as Object type in oracle

Question

I have a Table as:

CREATE TABLE EMPLOYEE
  (
    EMP_NAME VARCHAR2(30 BYTE) NOT NULL,
    EMP_ID   NUMBER NOT NULL,
    SALARY   NUMBER NOT NULL,
    DEPT_ID  NUMBER,
    UNIQUE ("EMP_ID"),
    FOREIGN KEY ("DEPT_ID") REFERENCES DEPARTMENT ("DEPT_ID")
  )

Then Created an Object type of Employee table and a nested table of that :

create or replace TYPE EMP_TYPE AS OBJECT (
EMP_NAME VARCHAR2(30 BYTE),
EMP_ID   NUMBER,
SALARY  NUMBER,
DEPT_ID  NUMBER
)

create or replace TYPE EMP_DIS as TABLE OF EMP_TYPE;

Now Created a package as :

create or replace
PACKAGE CURR_TRADE_TEST AS
TYPE EMP_DISPLAY IS TABLE OF EMPLOYEE%ROWTYPE; //Nested Table
PROCEDURE EMP_TRADE(E_T OUT EMP_DISPLAY);      //   proc 1
PROCEDURE EMP_TRD(EMP_TRD OUT EMP_DIS);        //   proc 2
END CURR_TRADE_TEST;

Package body as :

CREATE OR REPLACE
--proc 1
PACKAGE BODY CURR_TRADE_TEST AS
PROCEDURE EMP_TRADE(E_T OUT EMP_DISPLAY) AS
str VARCHAR2(1000);
BEGIN
STR:='select * from EMPLOYEE';
EXECUTE IMMEDIATE(STR) BULK COLLECT INTO E_T;
END EMP_TRADE;
--proc 2
PROCEDURE EMP_TRD(EMP_TRD OUT EMP_DIS) AS
str VARCHAR2(1000);
BEGIN
STR:='select * from EMPLOYEE';
EXECUTE IMMEDIATE(STR) BULK COLLECT INTO EMP_TRD;
END EMP_TRD;
END CURR_TRADE_TEST;

Now When I am calling the proc 1 as shown below getting expected output:

DECLARE
T_D CURR_TRADE_TEST.EMP_DISPLAY;
BEGIN
CURR_TRADE_TEST.EMP_TRADE(T_D);
FOR I IN T_D.FIRST..T_D.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(T_D(I).EMP_NAME||' '||T_D(I).EMP_ID);
END LOOP;
end;

But calling with proc 2 I am getting error:

DECLARE
E_D EMP_DIS;
BEGIN
CURR_TRADE_TEST.EMP_TRD(E_D);
FOR I IN E_D.FIRST..E_D.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(E_D(I).EMP_NAME||' '||E_D(I).EMP_ID);
END LOOP;
end;

Error report:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "ONLINE_PROD_FX_STAGING.CURR_TRADE_TEST", line 12
ORA-06512: at line 4
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Can anyboy tell me how to display the result for proc2.

Answer 1

The trouble starts within the second procedure. If you try to run it like this you will find out that the bulk collect into EMP_TRD of type EMP_DIS cannot happen.

declare
  str VARCHAR2(1000);
  EMP_TRD EMP_DIS;
BEGIN
  STR:='select * from EMPLOYEE';
  EXECUTE IMMEDIATE(STR) BULK COLLECT INTO EMP_TRD;
END;

If you run the above you get the same error. Point is that you cannot bulk collect like this. You may want to look at this to get the idea what the difference is: Replace iteration's fetch to BULK COLLECT