| Subject: | Oracle DDL not recognised |
consider the following
<code>
use strict;
use SQL::SplitStatement;
use Data::Dumper;
my $ddl = q{
create or replace procedure test (num1 number) is
v_test varchar2;
begin
select col1
into v_test
from my_tab;
end;
/
create table my_tab(
col1 varchar2(30),
col2 number
);
insert into my_tab(col1, col2) values ('hello', 3);
};
my $sql_splitter = SQL::SplitStatement->new;
my @statements = $sql_splitter->split($ddl);
print Dumper(@statements);
</code>
The DDL contains 3 statements - create a stored procedure, create a
table and insert date into the table.
The parsed statements are:
$VAR1 = 'create or replace procedure test (num1 number) is
v_test varchar2';
$VAR2 = 'begin
select col1
into v_test
from my_tab;
end';
$VAR3 = '/
create table my_tab(
col1 varchar2(30),
col2 number
)';
$VAR4 = 'insert into my_tab(col1, col2) values (\'hello\', 3)';
Oracle stored objects (procedures, packages, triggers, functions) must
always end with a slash. Statements within these objects must end with a
semi-column.
Standard DDL can end with a semi-colon or a forward slash. Our case
tool always generates slashes, so I prefer them to semi-colons when
working with Oracle.
Ultimately, I'd like to use SQL::SplitStatement to replace my own
homegrown but potentially more brittle splitting of SQL statements in
DBIx::VersionedDDL.
Thanks
Dan