PL SQL- Using Dynamic SQL to Generate Delete Statements

Question

I want to create a stored procedure using PL SQL that allows me to find all tables that contain a specific column, and then delete records from those tables that have a specific value in that column.

For example, I want to find all tables that have the column "year" and then delete all records from all of those tables that have the year "2012"(this year will be a parameter that will be entered upon execution)

My attempt at this has been to create a stored procedure, use a cursor to get all of the tables that have this column of "year" and then loop through that cursor using Dynamic SQL which will generate my Delete Statements that I can execute.

CREATE OR REPLACE PROCEDURE year_deletion
(
p_year  NUMBER --Input of the year for records to be deleted 
)
IS
CURSOR c1 --Cursor that will find all tables that have the YEAR column
IS
    SELECT owner, table_name 
    FROM all_tab_columns 
    WHERE column_name = 'YEAR'
    AND owner = 'GTP';

BEGIN
FOR i IN c1 LOOP    --Loop through all of the tables that the cursor found, generate a SQL statement for each table that will delete all of the records that have the year of p_year
   EXECUTE IMMEDIATE ('SELECT * FROM' ||i.table_name|| 'WHERE YEAR = '||p_year||';'); 
END LOOP;

END;

Disclaimer: I am using a Select * From instead of a DELETE * From for testing purposes, I will change this when it this procedure executes correctly.

So far this stored procedure compiles correctly, but gives me an error during execution that a FROM keyword was expected but not found. Is this the best method to use for my purpose?

You'll need to add space after FROM so instead of ...FROM', it should be FROM ' — Hawk
– Hawk, Commented Nov 3, 2015 at 1:11
|| operator will stick i.table_name to FROM, and then you'll have one word like FROMi.table_name, which of course is not what the compiler looking for — Hawk
– Hawk, Commented Nov 3, 2015 at 1:13
Also you have to add a space in 'WHERE YEAR =... it should be ' WHERE YEAR =' — Aitor
– Aitor, Commented Nov 3, 2015 at 8:24
Also you may add a Exception block inside the loop and at least print errors in that cases. for example, if you have some table in wich YEAR is a String and have String values, your sql will fail and the lopp will stop in that point. — Aitor
– Aitor, Commented Nov 3, 2015 at 8:32

Wernfried Domscheit · Accepted Answer · 2015-11-03 07:48:22Z

3

Is must be like this:

EXECUTE IMMEDIATE 'DELETE FROM ' ||i.table_name|| ' WHERE YEAR = :theYear' USING p_year;

Note the space after FROM and before WHERE.

You cannot simply replace DELETE by SELECT ... for testing because for SELECT you must have an INTO clause.

Your entire procedure can be like this

CREATE OR REPLACE PROCEDURE year_deletion(p_year IN NUMBER) IS

   CURSOR c1 IS
   SELECT owner, table_name 
   FROM all_tab_columns 
   WHERE column_name = 'YEAR'
      AND owner = 'GTP';

   res NUMBER;

BEGIN
   FOR i IN c1 LOOP
      EXECUTE IMMEDIATE 
         'SELECT COUNT(*) FROM ' ||i.table_name|| ' WHERE YEAR = :theYear' INTO res USING p_year;
      DBMS_OUTPUT.PUT_LINE (res ||' rows will be deleted from table '||i.table_name );
      EXECUTE IMMEDIATE 
         'DELETE FROM ' ||i.table_name|| ' WHERE YEAR = :theYear' USING p_year; 
   END LOOP;
END;

edited Nov 3, 2015 at 7:48

answered Nov 3, 2015 at 7:06

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

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

Comments

Avrajit Roy · Accepted Answer · 2015-11-04 12:43:53Z

0

Hello you can try the below code. It will surely help you out.

CREATE OR REPLACE PROCEDURE year_deletion(
    p_year IN  NUMBER --Input of the year for records to be deleted
  )
IS
BEGIN
  FOR i IN (SELECT owner,
      table_name
    FROM all_tab_columns
    WHERE column_name = 'YEAR'
    AND owner         = 'GTP')
  LOOP --Loop through all of the tables that the cursor found, generate a SQL statement for each table that will delete all of the records that have the year of p_year
    EXECUTE IMMEDIATE 'DELETE FROM ' ||i.table_name|| ' WHERE YEAR = '||p_year;
  END LOOP;
END;

answered Nov 4, 2015 at 12:43

Avrajit Roy

3,3131 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

PL SQL- Using Dynamic SQL to Generate Delete Statements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related