CODEWITHSUNDEEP
sqlplustruncateexecute-immediate
Vishal Saxena
Vishal Saxena

Reputation: 137

execute immediate truncate table in sqlplus

Why does execute immediate 'truncate table trade_economics'; in a sqlplus script give the following error ?

 BEGIN immediate 'truncate table trade_economics'; END;
                    *
    ERROR at line 1:
    ORA-06550: line 1, column 17:
    PLS-00103: Encountered the symbol "truncate table trade_economics" when
    expecting one of the following:
    := . ( @ % ;
    The symbol ":=" was substituted for "truncate table trade_economics" to
    continue.`

Upvotes: 2

Views: 20298

Answers (3)

Edward Bustamante
Edward Bustamante

Reputation: 31

This execution can be included in a procedure

EXECUTE IMMEDIATE ('truncate table name');
--next--
INSERT
    / * + append * /
    INTO table ..

Upvotes: 0

Farid
Farid

Reputation: 21

TRUNCATE is DDL (data definition language). You cannot perform DDL from within PL/SQL. That is, you cannot directly but you can through dynamic SQL.

So

Use this : DELETE FROM

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181350

You need to add execute before immediate in order to make it to work.

Something like:

begin
    execute immediate 'truncate table foo';
end;
/

Upvotes: 5

Related Questions