Patrik Beck Patrik Beck - 1 month ago 6
SQL Question

Fetching data into local table type

Overall, I need to call a function that takes table type as an argument. I need to fill it up before calling, and that's where I have problem.

The types are defined as follows:

create or replace TYPE MY_OBJ IS OBJECT(field1 NUMBER(10), field2 NUMBER(10), field3 VARCHAR2(2));
create or replace TYPE "MY_OBJ_ARRAY_TYPE" IS TABLE OF MY_OBJ;


My procedure follows:

DECLARE
-- ...
v_temp_table MY_OBJ_ARRAY_TYPE;
-- ...
BEGIN
v_temp_table := MY_OBJ_ARRAY_TYPE();

-- the following is incorrect
SELECT field1, field2, field3
BULK COLLECT INTO v_temp_table
FROM table1;

-- ... call the function here with v_temp_table as parameter
END;


The SELECT INTO approach:

SELECT INTO TABLE(v_temp_table) (field1, field2, field3)
VALUES (f1, f2, f3)
FROM table1;


I am not sure what the correct syntax should be, the
v_temp_table
is not being recognized as a table.

Answer

try

  SELECT NEW MY_OBJ( field1, field2, field3)
    BULK COLLECT INTO v_temp_table
    FROM table1;