SQL & PL/SQL

Hi All,

It might not be the best solution but this is a starting point for me. I have tables: x1_old, x2_old... x1_new, x2_new...
When I receive new tables, that is x1, x2... 3 things must be done:
1. x1_old, x2_old must be dropped
2. x1_new, x2_new must be renamed to x1_old, x2_old
3. x1, x2 tables can be renamed to x1_new, x2_new.

I hope this makes sense.

I managed to drop tables dynamically(step 1). But cannot apply similar approach to renaming(step 2 and 3).

Following code gives error: ORA-00900: invalid SQL statement

DECLARE
x NVARCHAR2(50) := '';
y NVARCHAR2(50) := '';
BEGIN

--Rename tables that end with 'NEW' to 'OLD'
FOR x IN (SELECT table_name
FROM all_tables
WHERE owner = 'OWNER_X'
AND SUBSTR(table_name, -3) = 'NEW')
LOOP
y := SUBSTR(x.table_name, 1, INSTR(x.table_name, 'NEW') - 1) || 'OLD';
EXECUTE IMMEDIATE 'rename ' || x.table_name || ' to ' || y;
END LOOP;
END;
/

I have read that I cannot use DDL with Execute imeediate. Any workarounds? Or different approach?

Thanks