I have a type table_array which is a table of t_timestamp records. This record is as below:
TYPE t_timestamp IS RECORD (
table_name VARCHAR2(50),
table_id VARCHAR2(50),
table_timestamp TIMESTAMP(6)
);
I later declare an instance of this table_array as below:
l_tables table_array := table_array();
I would like to create a procedure add_to_record() to easily add to this table_array by doing something like the following:
l_tables.extend;
add_to_record(l_tables, 'table_name', 'CRE_SHRE_CLASS');
add_to_record(l_tables, 'table_id', share_class_id);
add_to_record(l_tables, 'table_timestamp', l_table_timestamp);
Inside add_to_record(), I initially tried using EXECUTE IMMEDIATE to dynamically insert into to the record based on the passed parameters like below:
PROCEDURE add_to_record (tables IN OUT table_array, key IN VARCHAR2, value IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE '
SELECT
' || value || '
INTO tables(tables.last).' || key || '
FROM dual'
;
END add_to_record;
However, this didn't work as the correct syntax for inserting into a variable using EXECUTE IMMEDIATE is EXECUTE_IMMEDIATE some_query INTO some_variable. This leaves me in a predicament though. Because I can do the following:
l_select_value_from_dual_query := '
SELECT
' || value || '
FROM dual'
;
But the variable that I want to insert this value into is itself dynamic, and from what I can see, the INTO some_variable part of an EXECUTE IMMEDIATE query cannot be dynamic. So the following doesn't work:
EXECUTE IMMEDIATE l_select_value_from_dual_query INTO tables(tables.last).' || key || ';
Is there any way around this? I'd like to avoid some sort of IF-THEN-ELSE if possible.