Execute Immediate with multiple SQL Statements

Question

Here is my PL/SQL.

execute immediate '
create table Alex_Test2(col1 varchar2(100));
/
drop table Alex_Test2;
/
';

In the dynamic SQL, a table is created and then dropped immediately. The funny thing is that the table is successfully created and dropped but I got error.

Error starting at line 1 in command:
execute immediate '
Error report:
ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "; END;" when expecting one of the following:

   := . ( @ % ;
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
table ALEX_TEST2 created.
table ALEX_TEST2 dropped.

Error starting at line 6 in command:
'
Error report:
Unknown Command

The SQL statements actually typed by user. I cannot avoid the symbols like ; and /. How can I fixed this problem? Or how can I bypass this error?

However, I still need to keep the log. I expect the log is just

table ALEX_TEST2 created.
table ALEX_TEST2 dropped.

Thanks!

Answer 1

You are unable to manipulate the query string before executing it? If you don't have the ability to do that then what do you have the ability to do?

Sounds like you just need to do a find and replace on the query string before running it.