Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Fix Inconsistent Data type Error While Populating nested table on Object

Papi_SwanJun 7 2018 — edited Jun 7 2018

Hi ..

I have created an Object type and nested table based on this object type and populating data to the nested table variable .

here getting the inconsistent data type error .

here is the code :

CREATE TYPE dept_obj AS OBJECT

(deptno NUMBER(2),

dname  VARCHAR2(14),

loc    VARCHAR2(13)

);

CREATE TYPE ty_dept_tbl IS TABLE OF dept_obj;

DECLARE

  l_stmt   VARCHAR2(1000);

 

 

  cur_ref SYS_REFCURSOR ;

 

  v_ty_tbl_dept ty_dept_tbl :=ty_dept_tbl();

 

  v_int   NUMBER :=0;

BEGIN

   l_stmt  := 'SELECT * FROM dept ';

  

  

    OPEN cur_ref FOR l_stmt ;

   

   

   

   FETCH cur_ref BULK COLLECT INTO  v_ty_tbl_dept;

    

   

   

   

    CLOSE cur_ref;

    FOR v_cnt IN 1..v_ty_tbl_dept.COUNT LOOP

     

      DBMS_OUTPUT.PUT_LINE(v_ty_tbl_dept(v_cnt).deptno);

   

    END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('The error is '||sqlerrm);

END ;

Getting this error The error is ORA-00932: inconsistent datatypes: expected - got -

Please suggest me how to fix this ..

The current version of Oracle is Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

I am sorry for being repeatative  on this as I am practicing this for some project requirement .

Will really appreciate if any docs in regards to OORDBMS and Bulk collect be shared with me .

Thanks in advance

With immense gratitude .

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 5 2018
Added on Jun 7 2018
10 comments
1,321 views