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.

Bob Jarvis - Слава Україні · Accepted Answer · 2014-12-11 18:24:41Z

2

The problem is that you're trying to put items of type EMPLOYEE%ROWTYPE into a collection of type EMP_DIS, which is a nested table type which holds EMP_TYPE objects. You'll need to convert the EMPLOYEE rows into EMP_TYPE objects in order to store them in a collection of type EMP_DIS.

Share and enjoy.

answered Dec 11, 2014 at 18:24

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nizam Over a year ago

I just did a small change in proc 2. Replace STR:='select * from EMPLOYEE'; with STR:='select EMP_TYPE (EMP_NAME,EMP_ID,SALARY,DEPT_ID) from EMPLOYEE'; and it works fine. But can you tell me what the above statement means.

Nizam Over a year ago

Also fetching in java three things are happening. 1)Array arr = CallableStatement.getArry(1) 2) making Object[] array of first with arr.getArray(). 3) Then for each Object convert it into Struct and do getAttributes(). There is any simple approach. Sorry I am asking java in this thread.

Bob Jarvis - Слава Україні Over a year ago

I suggest that you should open a new question to get this sort of Java advice.

Community · Accepted Answer · 2017-05-23 12:12:48Z

0

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

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Dec 11, 2014 at 22:48

hol

8,4615 gold badges37 silver badges63 bronze badges

1 Comment

hol Over a year ago

Very good, glad my answer helped you. It is not mandatory but if my or the other answer did answer you question you can accept the answer and you can also upvote answers to indicate the answer is of some value.

Collectives™ on Stack Overflow

Nested table declared as Object type in oracle

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related