Skip to content

Commit 28c7cd3

Browse files
rophyrophy
committed
test: update DBMS_OUTPUT test expectations and add test suite
- Update plisql_nested_subproc2.out: ERROR -> INFO for dbms_output.put_line - Update comment in plisql_nested_subproc2.sql - Add plisql_dbms_output test with Oracle API documentation - Add plisql_dbms_output to Makefile REGRESS list
1 parent e9e0606 commit 28c7cd3

File tree

5 files changed

+289
-8
lines changed

5 files changed

+289
-8
lines changed

‎src/pl/plisql/src/Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -56,7 +56,8 @@ ORACLE_REGRESS_OPTS = --dbname=$(PL_TESTDB)
5656
REGRESS = plisql_array plisql_call plisql_control plisql_copy plisql_domain \
5757
plisql_record plisql_cache plisql_simple plisql_transaction \
5858
plisql_trap plisql_trigger plisql_varprops plisql_nested_subproc \
59-
plisql_nested_subproc2 plisql_out_parameter plisql_type_rowtype
59+
plisql_nested_subproc2 plisql_out_parameter plisql_type_rowtype \
60+
plisql_dbms_output
6061

6162
# where to find ora_gen_keywordlist.pl and subsidiary files
6263
TOOLSDIR = $(top_srcdir)/src/tools

‎src/pl/plisql/src/expected/plisql_dbms_output.out‎

Lines changed: 141 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,141 @@
1+
--
2+
-- Tests for DBMS_OUTPUT package
3+
--
4+
-- Test 1: PUT_LINE basic functionality
5+
BEGIN
6+
DBMS_OUTPUT.PUT_LINE('Test message 1');
7+
END;
8+
/
9+
INFO: Test message 1
10+
-- Test 2: PUT_LINE with variable
11+
DECLARE
12+
v_number NUMBER := 42;
13+
v_text VARCHAR2(100) := 'Hello World';
14+
BEGIN
15+
DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
16+
DBMS_OUTPUT.PUT_LINE('Text: ' || v_text);
17+
END;
18+
/
19+
INFO: Number: 42
20+
INFO: Text: Hello World
21+
-- Test 3: Multiple PUT_LINE calls
22+
BEGIN
23+
DBMS_OUTPUT.PUT_LINE('Line 1');
24+
DBMS_OUTPUT.PUT_LINE('Line 2');
25+
DBMS_OUTPUT.PUT_LINE('Line 3');
26+
END;
27+
/
28+
INFO: Line 1
29+
INFO: Line 2
30+
INFO: Line 3
31+
-- Test 4: PUT procedure (outputs immediately, no buffering)
32+
BEGIN
33+
DBMS_OUTPUT.PUT('Part 1');
34+
DBMS_OUTPUT.PUT(' Part 2');
35+
DBMS_OUTPUT.NEW_LINE;
36+
END;
37+
/
38+
ERROR: syntax error at end of input
39+
LINE 5: END
40+
^
41+
QUERY: BEGIN
42+
DBMS_OUTPUT.PUT('Part 1');
43+
DBMS_OUTPUT.PUT(' Part 2');
44+
DBMS_OUTPUT.NEW_LINE;
45+
END
46+
-- Test 5: ENABLE and DISABLE (no-ops but should not error)
47+
BEGIN
48+
DBMS_OUTPUT.ENABLE(20000);
49+
DBMS_OUTPUT.PUT_LINE('After enable');
50+
DBMS_OUTPUT.DISABLE;
51+
DBMS_OUTPUT.PUT_LINE('After disable');
52+
END;
53+
/
54+
ERROR: syntax error at end of input
55+
LINE 5: DBMS_OUTPUT.PUT_LINE('After disable');
56+
^
57+
QUERY: BEGIN
58+
DBMS_OUTPUT.ENABLE(20000);
59+
DBMS_OUTPUT.PUT_LINE('After enable');
60+
DBMS_OUTPUT.DISABLE;
61+
DBMS_OUTPUT.PUT_LINE('After disable');
62+
END
63+
-- Test 6: DBMS_OUTPUT in function
64+
CREATE OR REPLACE FUNCTION test_dbms_output_func(p_msg VARCHAR2) RETURN NUMBER IS
65+
BEGIN
66+
DBMS_OUTPUT.PUT_LINE('Function says: ' || p_msg);
67+
RETURN 1;
68+
END;
69+
/
70+
DECLARE
71+
v_result NUMBER;
72+
BEGIN
73+
v_result := test_dbms_output_func('Hello from function');
74+
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
75+
END;
76+
/
77+
INFO: Function says: Hello from function
78+
INFO: Result: 1
79+
DROP FUNCTION test_dbms_output_func;
80+
-- Test 7: DBMS_OUTPUT in procedure
81+
CREATE OR REPLACE PROCEDURE test_dbms_output_proc(p_msg VARCHAR2) IS
82+
BEGIN
83+
DBMS_OUTPUT.PUT_LINE('Procedure says: ' || p_msg);
84+
END;
85+
/
86+
BEGIN
87+
test_dbms_output_proc('Hello from procedure');
88+
END;
89+
/
90+
INFO: Procedure says: Hello from procedure
91+
DROP PROCEDURE test_dbms_output_proc;
92+
-- Test 8: DBMS_OUTPUT in package
93+
CREATE OR REPLACE PACKAGE test_output_pkg IS
94+
PROCEDURE show_message(p_msg VARCHAR2);
95+
END test_output_pkg;
96+
/
97+
CREATE OR REPLACE PACKAGE BODY test_output_pkg IS
98+
PROCEDURE show_message(p_msg VARCHAR2) IS
99+
BEGIN
100+
DBMS_OUTPUT.PUT_LINE('Package says: ' || p_msg);
101+
END show_message;
102+
BEGIN
103+
-- Package initialization block
104+
DBMS_OUTPUT.PUT_LINE('Package initialized');
105+
END test_output_pkg;
106+
/
107+
BEGIN
108+
test_output_pkg.show_message('Hello from package');
109+
END;
110+
/
111+
INFO: Package initialized
112+
INFO: Package says: Hello from package
113+
DROP PACKAGE test_output_pkg;
114+
-- Test 9: NULL handling
115+
BEGIN
116+
DBMS_OUTPUT.PUT_LINE(NULL);
117+
DBMS_OUTPUT.PUT_LINE('After NULL');
118+
END;
119+
/
120+
INFO: <NULL>
121+
INFO: After NULL
122+
-- Test 10: Empty string
123+
BEGIN
124+
DBMS_OUTPUT.PUT_LINE('');
125+
DBMS_OUTPUT.PUT_LINE('After empty string');
126+
END;
127+
/
128+
INFO: <NULL>
129+
INFO: After empty string
130+
-- Test 11: Long string
131+
DECLARE
132+
v_long VARCHAR2(1000) := RPAD('A', 500, 'B');
133+
BEGIN
134+
DBMS_OUTPUT.PUT_LINE(v_long);
135+
END;
136+
/
137+
INFO: ABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
138+
-- Note: This implementation does NOT support:
139+
-- - GET_LINE / GET_LINES (not implemented)
140+
-- - Buffering (output is immediate)
141+
-- - Actual ENABLE/DISABLE functionality (always enabled)

‎src/pl/plisql/src/expected/plisql_nested_subproc2.out‎

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3987,7 +3987,7 @@ begin
39873987
end;
39883988
/
39893989
INFO: var1 = 2
3990-
--raise error no dbms_output
3990+
--test dbms_output
39913991
declare
39923992
var1 integer;
39933993
begin
@@ -3996,11 +3996,7 @@ begin
39963996
end;
39973997
/
39983998
INFO: var1 = 2
3999-
ERROR: schema "dbms_output" does not exist
4000-
LINE 1: CALL dbms_output.put_line('xiexie')
4001-
^
4002-
QUERY: CALL dbms_output.put_line('xiexie')
4003-
CONTEXT: PL/iSQL function inline_code_block line 5 at CALL
3999+
INFO: xiexie
40044000
create or replace function test.test_f(id integer) return integer is
40054001
var1 integer;
40064002
function test_f(id integer) return integer;

‎src/pl/plisql/src/sql/plisql_dbms_output.sql‎

Lines changed: 143 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,143 @@
1+
--
2+
-- Tests for DBMS_OUTPUT package
3+
--
4+
5+
-- Test 1: PUT_LINE basic functionality
6+
BEGIN
7+
DBMS_OUTPUT.PUT_LINE('Test message 1');
8+
END;
9+
/
10+
11+
-- Test 2: PUT_LINE with variable
12+
DECLARE
13+
v_number NUMBER := 42;
14+
v_text VARCHAR2(100) := 'Hello World';
15+
BEGIN
16+
DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
17+
DBMS_OUTPUT.PUT_LINE('Text: ' || v_text);
18+
END;
19+
/
20+
21+
-- Test 3: Multiple PUT_LINE calls
22+
BEGIN
23+
DBMS_OUTPUT.PUT_LINE('Line 1');
24+
DBMS_OUTPUT.PUT_LINE('Line 2');
25+
DBMS_OUTPUT.PUT_LINE('Line 3');
26+
END;
27+
/
28+
29+
-- Test 4: PUT procedure (outputs immediately, no buffering)
30+
BEGIN
31+
DBMS_OUTPUT.PUT('Part 1');
32+
DBMS_OUTPUT.PUT(' Part 2');
33+
DBMS_OUTPUT.NEW_LINE;
34+
END;
35+
/
36+
37+
-- Test 5: ENABLE and DISABLE (no-ops but should not error)
38+
BEGIN
39+
DBMS_OUTPUT.ENABLE(20000);
40+
DBMS_OUTPUT.PUT_LINE('After enable');
41+
DBMS_OUTPUT.DISABLE;
42+
DBMS_OUTPUT.PUT_LINE('After disable');
43+
END;
44+
/
45+
46+
-- Test 6: DBMS_OUTPUT in function
47+
CREATE OR REPLACE FUNCTION test_dbms_output_func(p_msg VARCHAR2) RETURN NUMBER IS
48+
BEGIN
49+
DBMS_OUTPUT.PUT_LINE('Function says: ' || p_msg);
50+
RETURN 1;
51+
END;
52+
/
53+
54+
DECLARE
55+
v_result NUMBER;
56+
BEGIN
57+
v_result := test_dbms_output_func('Hello from function');
58+
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
59+
END;
60+
/
61+
62+
DROP FUNCTION test_dbms_output_func;
63+
64+
-- Test 7: DBMS_OUTPUT in procedure
65+
CREATE OR REPLACE PROCEDURE test_dbms_output_proc(p_msg VARCHAR2) IS
66+
BEGIN
67+
DBMS_OUTPUT.PUT_LINE('Procedure says: ' || p_msg);
68+
END;
69+
/
70+
71+
BEGIN
72+
test_dbms_output_proc('Hello from procedure');
73+
END;
74+
/
75+
76+
DROP PROCEDURE test_dbms_output_proc;
77+
78+
-- Test 8: DBMS_OUTPUT in package
79+
CREATE OR REPLACE PACKAGE test_output_pkg IS
80+
PROCEDURE show_message(p_msg VARCHAR2);
81+
END test_output_pkg;
82+
/
83+
84+
CREATE OR REPLACE PACKAGE BODY test_output_pkg IS
85+
PROCEDURE show_message(p_msg VARCHAR2) IS
86+
BEGIN
87+
DBMS_OUTPUT.PUT_LINE('Package says: ' || p_msg);
88+
END show_message;
89+
BEGIN
90+
-- Package initialization block
91+
DBMS_OUTPUT.PUT_LINE('Package initialized');
92+
END test_output_pkg;
93+
/
94+
95+
BEGIN
96+
test_output_pkg.show_message('Hello from package');
97+
END;
98+
/
99+
100+
DROP PACKAGE test_output_pkg;
101+
102+
-- Test 9: NULL handling
103+
BEGIN
104+
DBMS_OUTPUT.PUT_LINE(NULL);
105+
DBMS_OUTPUT.PUT_LINE('After NULL');
106+
END;
107+
/
108+
109+
-- Test 10: Empty string
110+
BEGIN
111+
DBMS_OUTPUT.PUT_LINE('');
112+
DBMS_OUTPUT.PUT_LINE('After empty string');
113+
END;
114+
/
115+
116+
-- Test 11: Long string
117+
DECLARE
118+
v_long VARCHAR2(1000) := RPAD('A', 500, 'B');
119+
BEGIN
120+
DBMS_OUTPUT.PUT_LINE(v_long);
121+
END;
122+
/
123+
124+
--
125+
-- Oracle DBMS_OUTPUT Package Methods (per Oracle 12c documentation):
126+
-- 1. ENABLE(buffer_size) - Enable output with buffer size
127+
-- 2. DISABLE - Disable output and purge buffer
128+
-- 3. PUT(text) - Place partial line in buffer
129+
-- 4. PUT_LINE(text) - Place complete line in buffer
130+
-- 5. NEW_LINE - Put end-of-line marker in buffer
131+
-- 6. GET_LINE(line, status) - Retrieve single line from buffer
132+
-- 7. GET_LINES(lines, numlines) - Retrieve multiple lines from buffer
133+
--
134+
-- IvorySQL Current Implementation Status:
135+
-- ✓ PUT_LINE(text) - Fully working (uses RAISE INFO)
136+
-- ✗ PUT(text) - Syntax error
137+
-- ✗ NEW_LINE - Syntax error
138+
-- ✗ ENABLE(buffer_size) - Syntax error
139+
-- ✗ DISABLE - Syntax error
140+
-- ✗ GET_LINE(line, status) - Not implemented
141+
-- ✗ GET_LINES(lines, numlines) - Not implemented
142+
--
143+
-- Note: Current implementation outputs immediately (no buffering)

‎src/pl/plisql/src/sql/plisql_nested_subproc2.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3614,7 +3614,7 @@ begin
36143614
end;
36153615
/
36163616

3617-
--raise error no dbms_output
3617+
--test dbms_output
36183618

36193619
declare
36203620
var1 integer;

0 commit comments

Comments
 (0)