Select into record dynamically

Viewed 31

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.

0 Answers
Related