Output a string in EXECUTE IMMEDIATE in Oracle PL/SQL

Question

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:

Is the problem really about "output" specifically? Also you should not include the tags in the question title. — William Robertson
– William Robertson, Commented Jun 2, 2017 at 11:32

Wernfried Domscheit · Accepted Answer · 2017-06-02 12:20:26Z

3

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;

edited Jun 2, 2017 at 12:20

answered Jun 2, 2017 at 7:36

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

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

4 Comments

Wernfried Domscheit Over a year ago

Yes, I anticipated this. I assumed your code just as a snippet.

Mudit Sharma Over a year ago

DECLARE vqs varchar(1000) := ''; BEGIN FOR i IN (SELECT d.table_name FROM user_tables d WHERE (LOWER(d.table_name) LIKE 'tblequityreturnstest_%')) LOOP vqs := 'SET TIMING ON; SELECT a.Ticker, FLAnnReturn(ReturnData(a.EquityReturn, '||'Daily'||')) AS AnnReturn FROM '||i.table_name||' a GROUP BY a.Ticker ORDER BY 1; SET TIMING OFF;'; EXECUTE IMMEDIATE vqs; END LOOP; END; /

William Robertson Over a year ago

For something like this it can be simpler to construct the required DDL in the cursor, or else add a dummy column to the cursor and populate that procedurally in the loop, rather than declaring a separate variable. btw you don't need to assign null to a new string variable, as it's already null by default.

William Robertson Over a year ago

@MuditSharma if you have additional code please add it to the question or start a new one. Just noticed the SET TIMING IN in your dynamic SQL string, which is a SQL*Plus command. SQL*Plus is a separate command-line tool and PL/SQL can't host out to it, or anything else.

Collectives™ on Stack Overflow

Output a string in EXECUTE IMMEDIATE in Oracle PL/SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related