CODEWITHSUNDEEP
sqloracle-database
J.Olufsen
J.Olufsen

Reputation: 13915

Get table name from sub-query to use in delete / update / insert clauses in Oracle in one line

The goal is to use the table name returned from subselect in insert/update/delete statements. It fails to achieve desired results.

If we execute with TABLE (TABLE is oracle syntax word as described in reference)

delete from TABLE
  (select DECODE(this returns table name as string) from REF_TABLE where where_clause);

then: 22905. 00000 - "cannot access rows from a non-nested table item"

If we execute without TABLE:

delete from
  (select DECODE(this returns table name as string) from REF_TABLE where where_clause);

Then it actually deletes from REF_TABLE the record that satisfies when clause.

What is a proper way to pass table name from subselect to outer query?

Doc examples

EXECUTE IMMEDIATE 'delete from table :1' USING (select DECODE(...) from REF_TABLE WHERE where_clause);

PLS-00103: Encountered the symbol "delete from table :1" when expecting one of the following:

:= . ( @ % ; 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Upvotes: 1

Views: 572

Answers (1)

Sandeep
Sandeep

Reputation: 806

You never mention table keyword when executing a DML query for example Insert, update and delete

delete from table_name;
update table_name set field='value';
insert into table_name (field) values ('value');

Table keyword is mentioned when you are executing a DDL statement like Create, Alter, drop and Truncate

Create table table_name (field varchar2(100));
Alter table table_name modify field1 varchar2(1000);
Drop table table_name;
truncate  table table_name;

Hence the below query mentioned by you is correct

delete from
  (select DECODE(this returns table name as string) from REF_TABLE where where_clause);

only limitation with the above query is that you have to use table_name source from a fixed table or view i.e. it cannot be picked from data dictionary views or you will get below error.

SQL Error: ORA-02030: can only select from fixed tables/views
02030. 00000 -  "can only select from fixed tables/views"
*Cause:    An attempt is being made to perform an operation other than
           a retrieval from a fixed table/view.
*Action:   You may only select rows from fixed tables/views.

you can use PL/SQL block similar as mentioned below to delete tables recursively

DECLARE
  CURSOR c
  IS
    SELECT table_name FROM user_tables WHERE table_name LIKE '%SANDEEP26FEB16_2%';
BEGIN
  FOR c1 IN c
  LOOP
    EXECUTE IMMEDIATE 'delete from ' || c1.table_name;
    COMMIT;
  END LOOP;
END;

Upvotes: 1

Related Questions