Say I have a sql script that looks like this:
--split statement 1 ALTER TABLE abs ADD (make VARCHAR2(2 byte), model varCHAR2(12 BYTE), built_on DATE, serial varchar2(123 BYTE)); / --split statement 2 declare begin null; end; / --split statement 3 insert into test (v,a,c) values ('1','jjoe;','232'); --split statement 4 create or replace function BLAH_BLAH(i_in varchar2) as l_one varchar2(12); l_two varchar2(12); l_three varchar2(12); begin l_one := 1; l_two := 3; insert into test (v,a,b) values ('1','jjoee;','232'); exception when no_data_found then l_three := 3; end; /
In principle, a script can have DML, DCL, DDL, and anonymous blocks. I want to be able to split each statement and execute it individually, but, of course, so that they are displayed.
I was thinking about using regex, and I believe the logic should be something like this:
1) if the line starts with create | alter | drop | declare, gets everything from the beginning of this line to a semi-colony, followed by aa new line, followed by a slash (the key here is that in the case of an anonymous block, we must ignore DML until we get to the end).
2) if the line starts with an insert | delete | update | merge (again, ignored if we are already in the block that applies to requirement 1), get everything from the beginning of this line to the half-colony, which is followed by a new line without a slash.
So far I have come up with this in Python:
sql_commands = re.split('(?i)(((create|alter|drop|merge)+)(?m);(\s*)\n(\s*))|(;(\s*)\n(\s*)/)',sql_script)
but every time I try to move forward with other requirements, the regular expression doesn’t work (and in fact the output looks like funk as it is) and it becomes complicated enough for me to get lost.
I would like it to be done either in Python or in Java (Java, I suppose, would actually be preferable if it's an oracle db)
This should not be a regular expression if the regular expression really does not match this task. My ultimate goal is to separate each statement and run it individually so that I can catch any errors that arise and gracefully handle them.