DBMS_SQL.NUMBER_TABLE inside update statement throwing invalid data type error

Question

Why does a select work but an update throws invalid data?

DECLARE
    L_NUMBER NUMBER;
    L_NUMBER_TABLE DBMS_SQL.NUMBER_TABLE;
    L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN
    L_NUMBER_TABLE(0) := 1033000;

-- THIS WORKS
SELECT TB.COLUMN_VALUE
INTO L_NUMBER
FROM TABLE(L_NUMBER_TABLE) TB;

-- THIS DOES NOT WORK
--  ERROR AT LINE 1
--  ORA-00902: INVALID DATATYPE
--  ORA-06512: AT LINE 13
UPDATE SCHEMA.REAL_NUMBER_TABLE
SET    REAL_NUMBER_DATE           = L_LAST_PRINTED_DATE
WHERE  EXISTS (  SELECT TB.COLUMN_VALUE
                    FROM TABLE( L_NUMBER_TABLE ) TB
                    WHERE TB.COLUMN_VALUE = REAL_NUMBER_COLUMN );


END;

I'm trying to loop thru a cursor and update the last printed date of the primary sequence found inside the cursor. I tried looping thru the cursor but then when I returned the cursor to the client, it throws an out of index error. So I was forced to make two cursors, one to loop thru and one to return. My goal is to learn what's the easiest and most maintainable way to hold collections for updating tables.

DECLARE
    CURSOR L_ORIGINAL_CURSOR IS SELECT ...

    L_CURSOR_COLUMN_1    PLS_INTEGER;
    L_CURSOR_COLUMN_2    PLS_INTEGER;
    L_CURSOR_COLUMN_3    PLS_INTEGER;
    -- Keep adding or removing the number of columns to match...1/2 09182019515PM
    -- L_CURSOR_COLUMN_4    PLS_INTEGER;
    L_NUMBER_TABLE DBMS_SQL.NUMBER_TABLE;
    L_COUNTER PLS_INTEGER;
    L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN    
    OPEN L_ORIGINAL_CURSOR;
        LOOP
            -- Keep adding or removing the number of columns to match... 2/2 09182019515PM
            FETCH L_ORIGINAL_CURSOR INTO L_CURSOR_COLUMN_1, L_CURSOR_COLUMN_2, L_CURSOR_COLUMN_3; -- , L_CURSOR_COLUMN_4;
                IF L_ORIGINAL_CURSOR%NOTFOUND THEN
                    EXIT;
                END IF;

                IF L_ORIGINAL_CURSOR%FOUND THEN
                        -- CURRENT SOLUTION IS TO UPDATE HERE BOUNCING BETWEEN SQL AND PLSQL ENGINES
                        -- UPDATE ....
                    -- WANTED IMPLEMENTATION
                    L_COUNTER := L_COUNTER + 1;
                END IF;
                -- WANTED IMPLEMENTATION STORE PK IN MY COLLECTION
                L_NUMBER_TABLE(L_COUNTER) := L_CURSOR_COLUMN_1;
        END LOOP;

    -- IF COLLECTION IS BIGGER THAN 0
    IF L_NUMBER_TABLE.COUNT > 0 THEN
        -- SCRIPT BREAKS HERE
        UPDATE ...
        SET ... = L_LAST_PRINTED_DATE
        WHERE EXISTS (  SELECT TB.COLUMN_VALUE
                        FROM TABLE(L_NUMBER_TABLE) TB
                        WHERE TB.COLUMN_VALUE = ...   );
    END IF;
    CLOSE L_ORIGINAL_CURSOR;

    OPEN L_CURSOR FOR SELECT ...
END SP_GET_PM_WORK_ORDERS;
/
SHOW ERRORS;

Currently the inline single sql update statement works but requires multiple engine hops between the SQL and PLSQL engines. Why am I allowed to do selects but not updates with my number table?

DATABASE VERSION: 12.1.0.2.0

MT0 · Accepted Answer

DBMS_SQL.NUMBER_TABLE is a PL/SQL associative array defined in the DBMS_SQL package as:

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

This is a PL/SQL data type and should not work in SQL statements (I've yet to work out why your first statement works).

If you want a data type that works in SQL then you need to be using a collection (without the INDEX BY; also called a nested table data type):

CREATE TYPE number_table IS TABLE OF NUMBER;

Or a fixed-length VARRAY:

CREATE TYPE number_array IS VARRAY(10) OF NUMBER;

For example:

Oracle Setup:

CREATE TABLE real_number_table ( real_number_column, real_number_date ) AS
  SELECT 1033000, DATE '2019-01-01' FROM DUAL;

CREATE TYPE number_table IS TABLE OF NUMBER;

PL/SQL Statement 1:

Then your SQL statement will work with a collection data type:

DECLARE
  L_NUMBER NUMBER;
  L_NUMBER_TABLE NUMBER_TABLE;
  L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN
  L_NUMBER_TABLE := NUMBER_TABLE();
  L_NUMBER_TABLE.EXTEND;
  L_NUMBER_TABLE( L_NUMBER_TABLE.COUNT ) := 1033000;

  UPDATE REAL_NUMBER_TABLE
  SET    REAL_NUMBER_DATE = L_LAST_PRINTED_DATE
  WHERE  EXISTS (
    SELECT TB.COLUMN_VALUE
    FROM TABLE( L_NUMBER_TABLE ) TB
    WHERE TB.COLUMN_VALUE = REAL_NUMBER_COLUMN
  );
END;
/

and then:

SELECT * FROM real_number_table;

outputs:

REAL_NUMBER_COLUMN | REAL_NUMBER_DATE
-----------------: | :---------------
           1033000 | 09-OCT-19

PL/SQL Statement 2:

Or you can simplify it and use the MEMBER OF operator (this only works with collection data types and not VARRAYs):

DECLARE
  L_NUMBER NUMBER;
  L_NUMBER_TABLE NUMBER_TABLE := NUMBER_TABLE( 1033000 );
  L_LAST_PRINTED_DATE DATE := SYSDATE + 1;
BEGIN
  UPDATE REAL_NUMBER_TABLE
  SET    REAL_NUMBER_DATE = L_LAST_PRINTED_DATE
  WHERE  REAL_NUMBER_COLUMN MEMBER OF L_NUMBER_TABLE;
END;
/

and then:

SELECT * FROM real_number_table;

outputs:

REAL_NUMBER_COLUMN | REAL_NUMBER_DATE
-----------------: | :---------------
           1033000 | 10-OCT-19

db<>fiddle here

You final PL/SQL anonymous block could be re-written as:

DECLARE
  L_NUMBER_TABLE NUMBER_TABLE;
  L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN
  SELECT column1
  BULK COLLECT INTO L_NUMBER_TABLE
  FROM   your_table; -- as per L_ORIGINAL_CURSOR

  IF L_NUMBER_TABLE.COUNT > 0 THEN
    UPDATE other_table
    SET    date_column = L_LAST_PRINTED_DATE
    WHERE  number_column MEMBER OF L_NUMBER_TABLE;
  END IF;
END;
/

DBMS_SQL.NUMBER_TABLE inside update statement throwing invalid data type error

Answers (2)

Related Questions