CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Nick Reed
Nick Reed

Reputation: 5059

PLSQL Define A Cursor Dynamically

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.

Upvotes: 0

Views: 237

Answers (1)

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

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;
/

Upvotes: 1

Related Questions