| Subject: | Two possible improvements for the splitter |
Hi,
I have to use case which pose problem to SQL::SplitStatement.
The one is very simple: I have a 13MB file with approximately 80k
statements in it (all extremely simple insert statements). This file
takes more than 2hour to be processed on a moderately powerfull hardware
and with a RAM usage of approximately 1GB (which is allocated at the
beginning of the processing and then which does not grow anymore). This
seems quiet long and maybe something can be improved in the algorithm
(but I did not take a look myself so I am not very helpfull here...).
The other problem is with some Oracle code looking like what I am
attaching here (splitstatement_oracle.sql). The original code contained
some third-party IP so I edited it and the result may be invalid Oracle
SQL but the important part is with the statement separator:
when submitting the 'alter session' statement, the ';' must be removed
from the statement for it to be understood by the driver (DBD::Oracle).
But, for the 'create package' or 'create procedure' statement to be
valid (to my limited understanding, but I am not very knowledgeable
about Oracle SQL) the final ';' (preceeding the '/') must be conserved
in the statement. For the 'create table' statement, the presence of the
final ';' seems to be neither required nor forbidden.
This code is provided by some software vendor and I don't know if it is
standard with Oracle SQL to format it that way, but it seems that a good
way to process it would be to remove the statement separator when it is
a single ';' but to keep the ';' if it is followed by a '\' on its on
line (which is then the real separator). I did not find an exact rule as
to what needs to be keeped.
However, this can not be achieved with the current option in
SQL::SplitStatement.
Maybe there could be a new option here like
'keep_semicolon_in_multiline_terminators' to achieve this...
Thanks again,
Mathias
| Subject: | splitstatement_oracle.sql |
ALTER SESSION SET NLS_NUMERIC_CHARACTERS =',.';
CREATE TABLE table1 (
t1_id INTEGER NOT NULL
);
create or replace package mytype
as
type cursorType is ref cursor return invoice_line_item_rpt_view%ROWTYPE;
end mytype;
/
create or replace procedure myproc
(curRet IN OUT mytype.cursorType)
AS
BEGIN
open curRet for
select 1 from sys.dual;
END myproc;
/
CREATE TABLE table2 (
t2_id INTEGER NOT NULL
);