Search code examples
sqloracle-databaseplsqltruncate

Truncating multiple tables in a Oracle PL/SQL block fails

Running the following script:

import cx_Oracle

conn = cx_Oracle.connect(user = db_user, password = db_pwd, dsn = 'TULSA').
cur = conn.cursor()

sql = ("""  
        BEGIN
            truncate table shop_ord_temp;
            truncate table shop_ord_item_temp;
            truncate table shop_cust_temp;
        END;
    """)

cur.execute(sql)
cur.close()
conn.close()

Receiving this error while running this anonymous block:

cx_Oracle.DatabaseError: ORA-06550: line 3, column 22:
PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

   := . ( @ % ;
Solution

  • You can use:

    sql = ("""  
        BEGIN
            execute immediate 'truncate table shop_ord_temp';
            execute immediate 'truncate table shop_ord_item_temp';
            execute immediate 'truncate table shop_cust_temp';
        END;
    """)

    This is a PL/SQL feature, not related to Python.