PL/SQL ORA-00902: invalid datatype

Question

I am trying to put this piece of code to a stored procedure. But the Update statement is throwing invalid datatype error. Any help or advice would be highly appreciated.

CREATE TABLE "TABLE1" 
(   
    "USER_ID" NUMBER, 
    "COMMENTS" VARCHAR2(20 BYTE)
)

PROCEDURE pr_test
AS
    v_table UserRecordTable;
    v_user_id_list SYS.ODCINUMBERLIST;
BEGIN
    select USER_ID bulk collect into v_table 
    from TABLE1;

    select USER_ID bulk collect into v_user_id_list 
    from TABLE1 
    where USER_ID in (select USERID FROM TABLE(v_table)); -- No problem

FOR i IN 1..v_user_id_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Column1: ' || v_user_id_list(i)); -- printout ok
END LOOP;

UPDATE TABLE1    -- This line throws the error
SET COMMENTS= 'NO NO'
where USER_ID in (select USERID FROM TABLE(v_table));
END pr_der_test;

In Spec

   TYPE UserRecordType IS RECORD (
    USERID TABLE1.USER_ID%TYPE
  ); 
TYPE UserRecordTable IS TABLE OF UserRecordType;

Answer 1

The problem probably relates to records being a PL/SQL data-type and that their support is limited in SQL statements (but increasing in more recent versions).

You can work around the problem using v_user_id_list in the query (which uses SYS.ODCINUMBERLIST, a data-type declared in the SQL scope and not in a PL/SQL scope of a package).

CREATE PACKAGE package_name
AS 
  TYPE UserRecordType IS RECORD (USERID TABLE1.USER_ID%TYPE); 
  TYPE UserRecordTable IS TABLE OF UserRecordType;
  PROCEDURE pr_test;
END;
/

CREATE PACKAGE BODY package_name
AS 
  PROCEDURE pr_test
  AS
    v_table UserRecordTable;
    v_user_id_list SYS.ODCINUMBERLIST;
  BEGIN
    select USER_ID
    bulk collect into v_table 
    from  TABLE1;

    select USER_ID
    bulk collect into v_user_id_list 
    from TABLE1 
    where USER_ID in (select USERID FROM TABLE(v_table)); -- No problem

    FOR i IN 1..v_user_id_list.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Column1: ' || v_user_id_list(i)); -- printout ok
    END LOOP;

    UPDATE TABLE1    -- This line throws the error
    SET COMMENTS= 'NO NO'
    -- where USER_ID in (select USERID FROM TABLE(v_table))
    where USER_ID in (select COLUMN_VALUE FROM TABLE(v_user_id_list))
    ;
  END;
END;
/

Given the sample data:

CREATE TABLE table1(
  user_id  NUMBER,
  comments VARCHAR2(20)
);

INSERT INTO table1 (user_id, comments)
SELECT 1, NULL FROM DUAL UNION ALL
SELECT 2, 'YES' FROM DUAL;

Then:

BEGIN
  DBMS_OUTPUT.ENABLE();
  package_name.pr_test;
END;
/

Outputs:

Column1: 1
Column1: 2

And the table then contains:

USER_ID	COMMENTS
1	NO NO
2	NO NO

fiddle