CODEWITHSUNDEEP
oracle-database
FeoJun
FeoJun

Reputation: 153

How commit multiple SQL statements in Oracle?

How commit multiple SQL statements in Oracle? I tried to do like this :

BEGIN

TRUNCATE TABLE test;
        

ALTER TABLE test 
    ADD (a1 VARCHAR2(10),
        a2 VARCHAR2(2),
        a3 VARCHAR(2));
        
END;    
COMMIT;

But I have got an error:

    ORA-06550: line 3, column 10:
    PLS-00103: The character "TABLE" was encountered while one of the following was expected:    
:= . ( @ % ;

I wanted to use it like I always do in Postgres:

begin;
SQL1;
SQL2;
SQL3;
commit;

Upvotes: 0

Views: 667

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

First of all, you can not execute the DDL statement in the PLSQL block.

Second, you don't need to commit to DDL statements. An implicit commit is executed before and after the DDL statement.

If you want to execute DDL statement in PLSQL block then use dynamic SQL

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE test';

EXECUTE IMMEDIATE 'ALTER TABLE test ADD (a1 VARCHAR2(10),a2 VARCHAR2(2), a3 VARCHAR(2))';
        
END;

Then your DDL will be executed in SQL Context.

Upvotes: 3

Related Questions