Split Multiple SQL Statement into individual SQL Statement

[yipych]

I have a block of SQL commands coming as plain text. It might be one SQL statement or several. I want to split multiple SQL commands so I can run them one at a time.

I am thinking of splitting them using delimiter like semicolon. But the problem is that for PL/SQL block, there will be a semicolon at the end of each line, if I split it using semicolon, the entire PL/SQL block will be divided into many syntactically incorrect pieces.

Any ideas? Thanks!

[Arjay]

Use a different delimitor or pass them as an array of individual strings.

Passing a single string that needs to be parsed should be avoided if possible because it typically is a good source for bugs.

[yipych]

Thanks for your reply.

In PL/SQL, there would be multiple semicolon in a block, see below. It seems that using semicolon would work for select, insert, update, and delete because they end up with just one and only one semicolon. But for the plsql block, it contains multiple semicolon.

I've write an function that separate the text into unit like 'select', '1', 'from', 'dual', ';' and etc. Any ideas on next step?

Thanks!



select 1 from dual;

insert into dual select 1 from dual;

declare
v_date date;
begin
select sysdate into v_date from dual;
exception
when others then
null;
end;
/