EXECUTE IMMEDIATE results into Table Type

1
votes

I need to have an EXECUTE IMMEDIATE return its results into an table type (AS TABLE OF)

If the execute immediate returns a single value i am able to save the result using "INTO somevariable"

But, my execute immediate will return a multi column table and no amount of googling have help me figure it out!

First I create the types

CREATE OR REPLACE TYPE T_VALIDITY_RECORD AS OBJECT (
   TIME_COL  DATE,
   VALUE_COL NUMBER
);
/

CREATE OR REPLACE TYPE T_VALIDITY_TABLE AS TABLE OF T_VALIDITY_RECORD;
/

Then I attempt the code

DECLARE
   RET_TABLE   T_VALIDITY_TABLE;
BEGIN

EXECUTE IMMEDIATE 'SELECT my_date,
                          my_numbers  
                   FROM   my_table
                   WHERE  somthing = somthingelse' INTO RET_TABLE;
END;

This is just a really simplified example, the real code does (will do other things)

I've tried various things like BULK COLLECT INTO etc but can't seem to get it working.

I am getting the following error:

Error report
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 5
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

2
oracleplsql

2 Answers

2
votes

You need to BULK COLLECT INTO and you need to collect instances of T_VALIDITY_OBJECT not pairs of NUMBER and DATE values.

Oracle Setup:

CREATE OR REPLACE TYPE T_VALIDITY_RECORD AS OBJECT (
   VALUE_COL NUMBER,
   TIME_COL  DATE
);
/

CREATE OR REPLACE TYPE T_VALIDITY_TABLE AS TABLE OF T_VALIDITY_RECORD;
/

CREATE TABLE my_table ( my_date, my_number, something ) AS
SELECT 1, DATE '2019-01-01', 1 FROM DUAL UNION ALL
SELECT 2, DATE '2019-01-02', 1 FROM DUAL UNION ALL
SELECT 3, DATE '2019-01-03', 1 FROM DUAL;

PL/SQL:

DECLARE
  RET_TABLE   T_VALIDITY_TABLE;
BEGIN
  EXECUTE IMMEDIATE 'SELECT T_VALIDITY_RECORD( my_date, my_number )
                     FROM   my_table
                     WHERE  something = :value'
    BULK COLLECT INTO RET_TABLE
    USING 1;

  FOR i IN 1 .. RET_TABLE.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( i || ': ' || ret_table(i).time_col || ', ' || ret_table(i).value_col );
  END LOOP;
END;
/

Output:

1: 2019-01-01 00:00:00, 1
2: 2019-01-02 00:00:00, 2
3: 2019-01-03 00:00:00, 3

db<>fiddle here

1
votes

Some issues:

You are fetching two columns into a structure that only has one element, made by two values.

Also, if your select can give more than one row, you need a BULK COLLECT.

If you don't need strictly dynamic sql here, a plain SQL query will work:

DECLARE
   RET_TABLE   T_VALIDITY_TABLE;
BEGIN    
    SELECT T_VALIDITY_RECORD(my_date, my_numbers  )
          bulk collect INTO RET_TABLE
   FROM   my_table ;
END;

If you need dynamic sql for other reasons:

DECLARE
   RET_TABLE   T_VALIDITY_TABLE;
BEGIN
    execute immediate ' SELECT T_VALIDITY_RECORD(my_date,
                              my_numbers  )
                        FROM   my_table'   
          bulk collect INTO RET_TABLE;                   
END;