CODEWITHSUNDEEP
oracle-databaseplsql
Mudit Sharma
Mudit Sharma

Reputation: 37

Output a string in EXECUTE IMMEDIATE in Oracle PL/SQL

I am trying to use loop variable as a string and use it directly with EXECUTE IMMEDIATE in PL/SQL loop. Here is my code:

BEGIN
    FOR i IN (
        SELECT table_name FROM user_tables WHERE LOWER(table_name) LIKE 'tblequityreturnstest_%'
    )
    LOOP
        vqs := 'DROP TABLE'||i||''
        EXECUTE IMMEDIATE vqs;
    END LOOP;
END;

But I am getting an error :

Error report -
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || member submultiset
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Upvotes: 0

Views: 1188

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Your syntax is wrong, try

DECALRE    
   vqs VARCHAR2(100);
BEGIN
   FOR i IN (SELECT table_name FROM user_tables WHERE LOWER(table_name) LIKE 'tblequityreturnstest_%') LOOP
      vqs := 'DROP TABLE '||i.table_name;
      EXECUTE IMMEDIATE vqs;
   END LOOP;
END;

or even simpler

BEGIN
   FOR i IN (SELECT table_name FROM user_tables WHERE LOWER(table_name) LIKE 'tblequityreturnstest_%') LOOP
      EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name;
   END LOOP;
END;

Upvotes: 3

Related Questions