1 package dbms_output authid definer as
2
3 -- DE-HEAD <- tell SED where to cut when generating fixed package
4
5 ------------
6 -- OVERVIEW
7 --
8 -- These procedures accumulate information in a buffer (via "put" and
9 -- "put_line") so that it can be retrieved out later (via "get_line" or
10 -- "get_lines"). If this package is disabled then all
11 -- calls to this package are simply ignored. This way, these routines
12 -- are only active when the client is one that is able to deal with the
13 -- information. This is good for debugging, or SP's that want to want
14 -- to display messages or reports to sql*dba or plus (like 'describing
15 -- procedures', etc.). The default buffer size is 20000 bytes. The
16 -- minimum is 2000 and the maximum is 1,000,000.
17
18 -----------
19 -- EXAMPLE
20 --
21 -- A trigger might want to print out some debugging information. To do
22 -- do this the trigger would do
23 -- dbms_output.put_line('I got here:'||:new.col||' is the new value');
24 -- If the client had enabled the dbms_output package then this put_line
25 -- would be buffered and the client could, after executing the statement
26 -- (presumably some insert, delete or update that caused the trigger to
27 -- fire) execute
28 -- begin dbms_output.get_line(:buffer, :status); end;
29 -- to get the line of information back. It could then display the
30 -- buffer on the screen. The client would repeat calls to get_line
31 -- until status came back as non-zero. For better performance, the
32 -- client would use calls to get_lines which can return an array of
33 -- lines.
34 --
35 -- SQL*DBA and SQL*PLUS, for instance, implement a 'SET SERVEROUTPUT
36 -- ON' command so that they know whether to make calls to get_line(s)
37 -- after issuing insert, update, delete or anonymous PL/SQL calls
38 -- (these are the only ones that can cause triggers or stored procedures
39 -- to be executed).
40
41 ------------
42 -- SECURITY
43 --
44 -- At the end of this script, a public synonym (dbms_output) is created
45 -- and execute permission on this package is granted to public.
46
47 ----------------------------
48 -- PROCEDURES AND FUNCTIONS
49 --
50 procedure enable (buffer_size in integer default 20000);
51 pragma restrict_references(enable,WNDS,RNDS);
52 -- Enable calls to put, put_line, new_line, get_line and get_lines.
53 -- Calls to these procedures are noops if the package has
54 -- not been enabled. Set default amount of information to buffer.
55 -- Cleanup data buffered from any dead sessions. Multiple calls to
56 -- enable are allowed.
57 -- Input parameters:
58 -- buffer_size
59 -- Amount of information, in bytes, to buffer. Varchar2, number and
60 -- date items are stored in their internal representation. The
61 -- information is stored in the SGA. An error is raised if the
62 -- buffer size is exceeded. If there are multiple calls to enable,
63 -- then the buffer_size is generally the largest of the values
64 -- specified, and will always be >= than the smallest value
65 -- specified. Currently a more accurate determination is not
66 -- possible. The maximum size is 1,000,000, the minimum is 2000.
67
68 procedure disable;
69 pragma restrict_references(disable,WNDS,RNDS);
70 -- Disable calls to put, put_line, new_line, get_line and get_lines.
71 -- Also purge the buffer of any remaining information.
72
73 procedure put(a varchar2);
74 pragma restrict_references(put,WNDS,RNDS);
75 -- Put a piece of information in the buffer. When retrieved by
76 -- get_line(s), the number and date items will be formated with
77 -- to_char using the default formats. If you want another format
78 -- then format it explicitly.
79 -- Input parameters:
80 -- a
81 -- Item to buffer
82
83 procedure put_line(a varchar2);
84 pragma restrict_references(put_line,WNDS,RNDS);
85 -- Put a piece of information in the buffer followed by an end-of-line
86 -- marker. When retrieved by get_line(s), the number and date items
87 -- will be formated with to_char using the default formats. If you
88 -- want another format then format it explicitly. get_line(s) return
89 -- "lines" as delimited by "newlines". So every call to put_line or
90 -- new_line will generate a line that will be returned by get_line(s).
91 -- Input parameters:
92 -- a
93 -- Item to buffer
94 -- Errors raised:
95 -- -20000, ORU-10027: buffer overflow, limit of <buf_limit> bytes.
96 -- -20000, ORU-10028:line length overflow, limit of 32767 bytes per line.
97
98 procedure new_line;
99 pragma restrict_references(new_line,WNDS,RNDS);
100 -- Put an end-of-line marker. get_line(s) return "lines" as delimited
101 -- by "newlines". So every call to put_line or new_line will generate
102 -- a line that will be returned by get_line(s).
103
104 procedure get_line(line out varchar2, status out integer);
105 pragma restrict_references(get_line,WNDS,RNDS);
106 -- Get a single line back that has been buffered. The lines are
107 -- delimited by calls to put_line or new_line. The line will be
108 -- constructed taking all the items up to a newline, converting all
109 -- the items to varchar2, and concatenating them into a single line.
110 -- If the client fails to retrieve all lines before the next put,
111 -- put_line or new_line, the non-retrieved lines will be discarded.
112 -- This is so if the client is interrupted while selecting back
113 -- the information, there will not be junk left over which would
114 -- look like it was part of the NEXT set of lines.
115 -- Output parameters:
116 -- line
117 -- This line will hold the line - it may be up to 32767 bytes long.
118 -- status
119 -- This will be 0 upon successful completion of the call. 1 means
120 -- that there are no more lines.
121
122 type chararr is table of varchar2(32767) index by binary_integer;
123 procedure get_lines(lines out chararr, numlines in out integer);
124 pragma restrict_references(get_lines,WNDS,RNDS);
125 -- Get multiple lines back that have been buffered. The lines are
126 -- delimited by calls to put_line or new_line. The line will be
127 -- constructed taking all the items up to a newline, converting all
128 -- the items to varchar2, and concatenating them into a single line.
129 -- Once get_lines is executed, the client should continue to retrieve
130 -- all lines because the next put, put_line or new_line will first
131 -- purge the buffer of leftover data. This is so if the client is
132 -- interrupted while selecting back the information, there will not
133 -- be junk left over.
134 -- Input parameters:
135 -- numlines
136 -- This is the maximum number of lines that the caller is prepared
137 -- to accept. This procedure will not return more than this number
138 -- of lines.
139 -- Output parameters:
140 -- lines
141 -- This array will line will hold the lines - they may be up to 32767
142 -- bytes long each. The array is indexed beginning with 0 and
143 -- increases sequentially. From a 3GL host program the array begins
144 -- with whatever is the convention for that language.
145 -- numlines
146 -- This will be the number of lines actually returned. If it is
147 -- less than the value passed in, then there are no more lines.
148
149 --FIXED_ONLYTYPE dbmsoutput_linesarray IS VARRAY(2147483647) OF
150 --FIXED_ONLY VARCHAR2(32767);
151 procedure get_lines(lines out dbmsoutput_linesarray, numlines in out integer);
152 -- get_lines overload with dbmsoutput_linesarray varray type for lines.
153 -- It is recommended that you use this overload in a 3GL host program to
154 -- execute get_lines from a PL/SQL anonymous block.
155 pragma restrict_references(get_lines,WNDS,RNDS);
156
157 pragma TIMESTAMP('2000-06-22:11:21:00');
158
159 end;
160
161 -- CUT_HERE <- tell sed where to chop off the rest
[Home] [Help]