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
170
171
172
173
174
175
176
177
178
179
| /* ***************************************
<a href="http://www.adp-gmbh.ch/ora/misc/oru_10028.html" target="_blank">http://www.adp-gmbh.ch/ora/misc/oru_10028.html</a>
Procedure to print big text on screen
Parameters:
IN_TEXT - text to print
IN_TEXT_LENGTH - output string length. Default is 255 (maximum allowed for DBMS_OUTPUT)
IN_DIVIDER - divider between words.
Used to do not split the whole word when start new print line
Default is SPACE
IN_NEW_LINE - new line divider. If there is this divider withing string to print out, then
string will be first printed till this divider, and then start from new line.
Default NULL
Examples:
print_out(<text>);
print_out(<text>, 80);
print_out(<text>, 20);
print_out(<text>, 255, ' ');
print_out(<text>, 250, ' ', chr(10));
Last example: print text breaking it by spaces.
If there is new line character within test, it will be printed on the different line.
Author:
Oleg Savkin Nov 2005
* ***************************************/
CREATE OR REPLACE PROCEDURE print_out(
IN_TEXT VARCHAR2,
IN_TEXT_LENGTH NUMBER DEFAULT 255,
IN_DIVIDER VARCHAR2 DEFAULT CHR(32),
IN_NEW_LINE VARCHAR2 DEFAULT NULL)
IS
lv_print_text VARCHAR2(32767);
ln_position PLS_INTEGER;
ln_divider_position PLS_INTEGER;
ln_total_printed PLS_INTEGER;
ln_string_length PLS_INTEGER;
PROCEDURE printText (IN_PRINT VARCHAR2)
IS
BEGIN
-- dbms_output.put_line( IN_PRINT );
dbms_output.put( IN_PRINT );
END printText;
BEGIN
IF IN_TEXT_LENGTH >255
THEN
ln_string_length := 255;
ELSE
ln_string_length := IN_TEXT_LENGTH;
END IF;
IF LENGTHB(IN_TEXT) <=IN_TEXT_LENGTH
THEN
printText(IN_TEXT);
ELSE
ln_position := 1;
ln_total_printed := 0;
LOOP
lv_print_text := SUBSTR( IN_TEXT,ln_position, ln_string_length );
IF IN_NEW_LINE IS NULL
THEN
ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider
ELSE
ln_divider_position := INSTR(lv_print_text, IN_NEW_LINE, -1);
IF ln_divider_position = 0
THEN
ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider
END IF;
END IF;
IF ln_divider_position = 0
THEN
ln_divider_position := ln_string_length;
END IF;
IF ln_divider_position <=ln_string_length
THEN
lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position);
IF length( lv_print_text ) <> lengthb(lv_print_text)
THEN
ln_divider_position := ln_divider_position-(lengthb(lv_print_text)-length( lv_print_text ));
lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position);
IF IN_NEW_LINE IS NULL
THEN
ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider
ELSE
ln_divider_position := INSTR(lv_print_text, IN_NEW_LINE, -1);
IF ln_divider_position = 0
THEN
ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider
END IF;
END IF;
IF ln_divider_position = 0
THEN
ln_divider_position := ln_string_length-(lengthb(lv_print_text)-length( lv_print_text ));
END IF;
lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position);
END IF;
IF ln_divider_position = 0
THEN
ln_divider_position := ln_string_length;
END IF;
ln_position := ln_position+ln_divider_position;
END IF;
ln_total_printed := ln_total_printed+LENGTHB(lv_print_text);
lv_print_text := TRIM( lv_print_text );
--dbms_output.put_line('***');
printText(lv_print_text);
EXIT WHEN ln_position >= LENGTH(TRIM(IN_TEXT));
END LOOP;
IF ln_position <ln_total_printed -- printed not everything
THEN
printText(substr( IN_TEXT, ln_position, ln_total_printed ));
END IF;
END IF;
EXCEPTION
WHEN others
THEN
dbms_output.put_line( 'ERROR :'||SQLERRM );
dbms_output.put_line( 'ln_position: '||ln_position );
dbms_output.put_line( 'ln_divider_position: '||ln_divider_position );
END print_out;
/
show error
set head off
set serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
set pagesize 0
set verify off
set feedback off
set termout off
set echo off
set embedded off
-- Suppression espaces de fin de ligne
set trimspool on
set recsep off
set time off
set timing off
-- set trimspool off
col value format a68
-- set lines 4050
set lines unlimited
spool &1
BEGIN
FOR c_rec IN (SELECT DISTINCT name, type FROM user_source ORDER BY name, type)
LOOP
DBMS_OUTPUT.put_line ('-- SOURCE CODE FROM : ' || c_rec.name || ' TYPE OF OBJECT : ' || c_rec.type);
FOR c_source IN (SELECT line,text FROM user_source WHERE NAME=c_rec.NAME AND type=c_rec.type ORDER BY line)
LOOP
-- DBMS_OUTPUT.put(c_source.text);
DBMS_OUTPUT.put_line(''); -- LIGNE AJOUTÉE POUR EMPÊCHER ORU-10028
print_out(c_source.text);
END LOOP;
DBMS_OUTPUT.put_line (CHR(10) || '-- END OF OBJECT CODE ' || c_rec.name || CHR(10));
END LOOP;
END;
/ |
