Can I have multiple statements in a native query in JPA 2

Question

I want to execute the following query to conditionally drop a temporary table.

String dropSql = "BEGIN EXECUTE IMMEDIATE 'DROP TABLE " + tmpTblName + "';" +
    " EXCEPTION" +
    " WHEN OTHERS THEN" +
    " IF SQLCODE!=-942 THEN" +
    " RAISE;" +
    " END IF;" +
    " END";

And then execute it as a native query like this:

JPA.em().createNativeQuery(dropSql)
            .executeUpdate();

However, JPA will not let me or rather, the oracle db won't. Apparently the ; get escaped or misinterpreted somehow.

Caused by: java.sql.SQLException: ORA-06550: line 1, column 120:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.

Will I have to live with using separate queries to achieve my goal or is there some trick to this?

Answer 1

You missed a semicolon after the END block finally.

String dropSql = "BEGIN EXECUTE IMMEDIATE 'DROP TABLE " + tmpTblName + "';" +
    " EXCEPTION" +
    " WHEN OTHERS THEN" +
    " IF SQLCODE!=-942 THEN" +
    " RAISE;" +
    " END IF;" +
    " END;";