1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
#!/usr/bin/env perl
use strict;
use warnings;
use SQL::SplitStatement;
use Test::More tests => 18;
my $sql_code = <<'SQL';
create or replace type Address_Type
as object
( street_addr1 varchar2(25),
street_addr2 varchar2(25),
city varchar2(30),
state varchar2(2),
zip_code number,
member function toString return varchar2,
map member function mapping_function return varchar2
)
/
create or replace type body Address_Type as
member function toString return varchar2
is
begin
if ( street_addr2 is not NULL )
then
return street_addr1 || ' ' ||
street_addr2 || ' ' ||
city || ', ' || state || ' ' || zip_code;
else
return street_addr1 || ' ' ||
city || ', ' || state || ' ' || zip_code;
end if;
end;
map member function mapping_function return varchar2
is
begin
return to_char( nvl(zip_code,0), 'fm00000' ) ||
lpad( nvl(city,' '), 30 ) ||
lpad( nvl(street_addr1,' '), 25 ) ||
lpad( nvl(street_addr2,' '), 25 );
end;
end;
create table people
( name varchar2(10),
home_address address_type,
work_address address_type
)
/
create or replace type Address_Array_Type as varray(25) of Address_Type
/
alter table people add previous_addresses Address_Array_Type
/
CREATE TYPE varchar2_4000_array AS TABLE OF VARCHAR2(4000)
/
DROP TABLE test_tab
/
CREATE TABLE test_tab (
id NUMBER,
PNOTETEXT VARCHAR2_4000_ARRAY
)
nested table PNOTETEXT store as PNOTETEXT_NEST
;
CREATE INDEX i_test_tab_pk ON test_tab (id)
/
SELECT count(*) from test_tab
/
SELECT id FROM mytable WHERE 4 < id
/
.2
;
SELECT id FROM mytable WHERE 4 < id
/
3
;
SELECT id FROM mytable WHERE 4 < id
/
(3+4)
;
CREATE SEQUENCE TEST_TAB_SEQ MINVALUE 1 MAXVALUE 9999999 START WITH 1 INCREMENT BY 1 NOCACHE
;
DECLARE
vCollection varchar2_4000_array := varchar2_4000_array();
vID NUMBER;
BEGIN
-- get a new id
SELECT TEST_TAB_SEQ.NEXTVAL INTO vID FROM dual;
SELECT pnotetext INTO vCollection FROM test_tab WHERE id = vID;
-- loop round all the collection variable elements and print them out
FOR q IN 1 .. vCollection.count LOOP
dbms_output.put_line(q||' - tab : '||vCollection(q));
END LOOP;
END;
DROP TABLE test_tab
/
SQL
my $splitter;
my @statements;
my @endings;
$splitter = SQL::SplitStatement->new;
@statements = $splitter->split( $sql_code );
cmp_ok(
@statements, '==', 16,
'Statements correctly split'
);
$splitter->keep_extra_spaces(1);
$splitter->keep_empty_statements(1);
$splitter->keep_terminator(1);
$splitter->keep_comments(1);
@statements = $splitter->split( $sql_code );
is(
join( '', @statements ), $sql_code,
'SQL code correctly rebuilt'
);
@endings = qw|
)
end
)
Address_Type
Address_Array_Type
VARCHAR2(4000)
test_tab
PNOTETEXT_NEST
(id)
test_tab
2
3
)
NOCACHE
END
test_tab
|;
$splitter->keep_extra_spaces(0);
$splitter->keep_empty_statements(0);
$splitter->keep_terminators(0);
$splitter->keep_comments(0);
@statements = $splitter->split( $sql_code );
like( $statements[$_], qr/\Q$endings[$_]\E$/, 'Statement ' . ($_+1) . ' check' )
for 0..$#endings;
|
