You Asked
I want to delete values from various tables. These table name are in a column field. Will this be possible .
For example lets say Table A has column as SID. And these SID are the table names too. I want to pick values from SID and delete values from these tables. Lets say Table A has value 123. Then my delete statement that need to be generated is Delete from A123.
and Connor said...
Some plsql should do the job
SQL> create table t ( tname varchar2(30) );
Table created.
SQL>
SQL> insert into t values ('TAB1');
1 row created.
SQL> insert into t values ('TAB2');
1 row created.
SQL>
SQL> set serverout on
SQL> declare
2 del_stmt varchar2(1000);
3 begin
4 for i in ( select tname from t ) loop
5 del_stmt := 'delete from '||i.tname;
6 dbms_output.put_line(del_stmt);
7 execute immediate del_stmt;
8 end loop;
9 end;
10 /
delete from TAB1
delete from TAB2
PL/SQL procedure successfully completed.
SQL>
SQL>
Is this answer out of date? If it is, please let us know via a Comment