PLSQL Define A Cursor Dynamically

Question

I am trying to create a procedure that accepts two table names as parameters, and then copies the rows from one table into another, one at a time. I know bulk insert and SELECT INTO are better ways to do this, but bulk insert isn't working because the table triggers throw mutation errors when I insert more than one row at once.

I've seen other answers that recommend using dynamic SQL, but I'm stuck on how to define the cursor that way.

CREATE OR REPLACE PROCEDURE TABLE_INSERT(
    donor_t IN VARCHAR2,
    empty_t IN VARCHAR2
    )
AS
    CURSOR C1 IS 
        SELECT * FROM donor_t;
BEGIN
    FOR row IN C1
    LOOP
        INSERT INTO empty_t VALUES row;
    END LOOP;
END;

When compiled as written above, compiler throws ORA-00942: table or view does not exist. When compiled with table names hard-coded in, this function inserts the rows as expected, without errors.

Basically what you are trying to do is an insert as select, why not do that instead? Processing data row by row is very bad for performance. — Cyrille MODIANO
– Cyrille MODIANO, Commented Jun 22, 2017 at 13:14
One option is to look at your triggers and consider if they can be disabled for the bulk operation and re-enabled afterwards. Obviously your bulk operation will need to deal with the business logic performed by the triggers. Otherwise try the Oracle docs for examples - docs.oracle.com/cd/B28359_01/appdev.111/b28370/… — BriteSponge
– BriteSponge, Commented Jun 22, 2017 at 13:16
@BriteSponge Didn't even occur to me to see if I could disable the triggers and re-enable them after insert; that solution worked like a charm. — Nick Reed
– Nick Reed, Commented Jun 22, 2017 at 14:03

Cyrille MODIANO · Accepted Answer · 2017-06-22 13:19:07Z

1

try this instead:

CREATE OR REPLACE PROCEDURE TABLE_INSERT(
donor_t IN VARCHAR2,
empty_t IN VARCHAR2
)
AS 
  V_SQL VARCHAR2(1000);
BEGIN
    V_SQL := 'INSERT INTO ' || empty_t || ' SELECT * FROM ' ||  donor_t;
    EXECUTE IMMEDIATE V_SQL;
END;
/

answered Jun 22, 2017 at 13:19

Cyrille MODIANO

2,4163 gold badges24 silver badges34 bronze badges

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

1 Comment

Nick Reed Over a year ago

That's a good solution for a dynamic insert statement, but it's unfortunately still causing the trigger to throw mutation errors since it's not one-at-a-time inserts.

Collectives™ on Stack Overflow

PLSQL Define A Cursor Dynamically

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