How to count how many sql statements are there in an SQL file?

Question

So, given an SQL file to be executed in Oracle, we are asked to determine how many blocks are to be executed within the SQL file. For example, there is one block in an SQL file containing the following command,

CREATE TABLE customer (id varchar2(42));

two blocks in the following SQL file,

ALTER TABLE customer ADD name varchar2(42);
ALTER TABLE customer DROP COLUMN id;

and three blocks in the following SQL file

CREATE OR REPLACE PROCEDURE printHelloWorld IS
BEGIN
    dbms_output.put_line('Hello World!');
END;
/

INSERT INTO customer VALUES ('ivan');

DROP TABLE customer;

We can't assume anything else about the input, other than the fact it will be executed without any error in Oracle SQLDeveloper.

UPDATE

The purpose of asking the question is to ensure that there would only be one statement, which is to be executed, in the SQL file. I am also open to the answer of this question. It would be even better to be able to create a script to split a multiple-statement SQL file to multiple files.

Answer 1

Not a perfect solution but will work in most cases I think.

First create a duff user with no rights except to create a session.

CREATE USER duff IDENTIFIED BY "password";
GRANT CREATE SESSION TO duff;

Then use this with sqlplus and grep to count the ORA- errors - should be one per statement.

sqlplus duff/password@db < script.sql | grep -c ORA-

If you have ALTER SESSION statements then you need a bit more

sqlplus duff/password@db < script.sql | grep -Ec 'ORA-|Session altered.'

There maybe other exceptions, but I think it gives you a workable solution for little overhead. Be careful that scripts don't switch user - but if you have hard-coded usernames and passwords in your scripts you have other issues.