FEtching into an object throws inconsistent datatypes: expected - got -

Question

Is it possible to iterate over a cursor without the need for an intermediate TABLE as type of Object?

I have the following EVENT_TABLE setup in my schema

create TABLE EVENT_TABLE (
       UUID INTEGER, 
       EVENT_TYPE VARCHAR2(10)
);

INSERT INTO EVENT_TABLE values( 1, 'START');
INSERT INTO EVENT_TABLE values( 2, 'RUNNING');
INSERT INTO EVENT_TABLE values( 2, 'COMPLETE');


CREATE OR REPLACE TYPE EVENT_OBJ AS OBJECT (
       UUID INTEGER, 
       EVENT_TYPE VARCHAR2(10)
);
/

COMMIT;

declare 
    r EVENT_OBJ;
    TYPE cur_typ IS REF CURSOR;
    EVENT cur_typ;
BEGIN
  OPEN event FOR 'select * from EVENT_TABLE';
  LOOP
    FETCH event INTO r;
    EXIT WHEN event%NOTFOUND;
    DBMS_OUTPUT.put_line('NEW_UID:-' || R.UUID);
  END LOOP;
END;
/

when I execute the anonymous block at the end I get the following exception

ORA-00932 inconsistent datatypes: expected - got -

I've seen examples where a query is fetched into an object before but I can't seem to get it to work.

By creating a temporary table as follows, the anonymous block completes and the DBMS output as expected is produced?

declare 
    r EVENT_OBJ;
    TYPE cur_typ IS REF CURSOR;
    EVENT cur_typ;

    -- Temp table 
    TYPE EVENT_TBL IS TABLE OF EVENT_OBJ;
    lt_evt_tbl EVENT_TBL;

BEGIN
  select EVENT_OBJ (UUID,EVENT_TYPE ) bulk collect into lt_evt_tbl from EVENT_TABLE;
   FOR indx IN 1..lt_evt_tbl.COUNT
   LOOP
    DBMS_OUTPUT.put_line('NEW_UID:-' || lt_evt_tbl(indx).UUID);
  END LOOP;
END;
/

William Robertson William Robertson · Accepted Answer · 2018-02-26T12:23:23

You need to construct an event_obj object for each row returned from the table:

declare
    r event_obj;
    event sys_refcursor;
begin
    open event for
        select event_obj(uuid, event_type) from event_table;

    loop
        fetch event into r;
        exit when event%notfound;
        dbms_output.put_line('NEW_UID: ' || r.uuid);
    end loop;
end;

When you just select plain columns from a plain relational table, you get back a plain record type, not an object, and fetch into won't implicitly it cast to one.

By the way, your lt_evt_tbl in the second example is a collection, not a temp table.

Also, there is no need to define your own weak ref cursor type, as sys_refcursor is already provided; and although you can certainly use native dynamic SQL for your ref cursor, you don't need to, and static code is easier to work with, so I have made it static in the example above.

Even simpler version:

begin
    for r in (
        select event_obj(uuid, event_type) as event from event_table
    )
    loop
        dbms_output.put_line('NEW_UID: ' || r.event.uuid);
    end loop;
end;

FEtching into an object throws inconsistent datatypes: expected - got -

1 Answers