Oracle/PLSQL nested for loop for inserting values

Question

I get the error

ORA-00984: column not allowed here

when I run this PLSQL script:

BEGIN
    FOR i IN (
        SELECT attribute_id
        FROM odd_attribute
    ) LOOP
        FOR j IN (
            SELECT t1.sourceattribute_id
            FROM odd_sourceattribute t1
            WHERE t1.sourcetable_id IN (
                SELECT t2.sourcetable_id
                FROM odd_sourcetable t2
                WHERE t2.sourcetable_name LIKE 'vr_db_f_%'
            )
        ) LOOP
            EXECUTE IMMEDIATE 'insert into odd_fact_attribute 
                          (attribute_id, sourceattribute_id, statusattribute_name, other_stuff_to_describe_fact) 
                          values 
                          (i.attribute_id, j.sourceattribute_id, NULL, NULL)'
            ;
        END LOOP;
    END LOOP;
END;

The column names seems to be correct and I'm not able to figure out the cause of the error. Appreciate all the help. Thanks.

Kaushik Nayak · Accepted Answer · 2018-08-15 10:45:08Z

3

The error was raised because you are referring to the column names from implicit cursor within quotes of EXECUTE IMMEDIATE, which are going to be treated as table references in static sql.

You may avoid that with the help of placeholders (starting with : ) and a USING statement.

EXECUTE IMMEDIATE 
'INSERT INTO odd_fact_attribute (
        attribute_id,
        sourceattribute_id,
        statusattribute_name,
        other_stuff_to_describe_fact
    ) VALUES (
        :attribute_id,
        :sourceattribute_id,
        NULL,
        NULL
    )'
    USING i.attribute_id,j.sourceattribute_id;

Importantly, there is no need of EXECUTE IMMEDIATE and your PL/SQL block with nested loop is equivalent to a single INSERT statement like this, which is going to be much efficient than your loops.

INSERT INTO odd_fact_attribute (
    attribute_id,
    sourceattribute_id,
    statusattribute_name,
    other_stuff_to_describe_fact
)
    SELECT o.attribute_id,
           t1.sourceattribute_id,
           NULL,
           NULL
    FROM odd_sourceattribute t1
    CROSS JOIN odd_attribute o --use appropriate join condn. if this is not the case
    WHERE t1.sourcetable_id IN (
        SELECT t2.sourcetable_id
        FROM odd_sourcetable t2
        WHERE t2.sourcetable_name LIKE 'vr_db_f_%'
    );

edited Aug 15, 2018 at 10:45

answered Aug 15, 2018 at 10:31

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Delin Mathew Over a year ago

The second option works perfectly. The first option took more than one hour to execute because the expected output consists of around 900,000 rows. Therefore I just terminated it and tried the second option which took about 2 seconds. Lesson learnt: use queries if possible instead of loops! Thank you so much.

Collectives™ on Stack Overflow

Oracle/PLSQL nested for loop for inserting values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related