Oracle PLS debugging Output Method

We need to debug the PL/SQL process to view the variable content. You can use the following method. Assume that the variable we want to view is output and the type is varchar2.

1. The most basic DBMS_OUTPUT.PUT_LINE () method.
You can use DBMS_OUTPUT.PUT_LINE (output) Wherever you are between BEGIN and END );
However, this may cause a problem, that is, the function can display a maximum of 255 characters at a time, otherwise the buffer will overflow.
In addition, the function DBMS_OUTPUT.ENABLE (20000) is only used to set the buffer size of all output buffers throughout the process, rather than the buffer size of DBMS_OUTPUT.PUT_LINE.
The DBMS_OUTPUT.PUT_LINE () method is not valid for variables with more than 255 characters. It is said that in Oracle10, this restriction is lifted to output strings of any size.
Declare
Output varchar2 (200 );
Begin
Output: = '...'; // value assignment
DBMS_OUTPUT.PUT_LINE (output );
End;

2. Use the table method.
Create a table and insert the content to be output to the table in PL/SQL. Then, you can view the content in the table. This method can output thousands of characters at a time.
(1) create table my_output {
Id number (4 ),
Txt varchar2 (4000)
);

(2) declare
Output varchar2 (4000 );
StrSql varchar2 (4500 );
Count number (4): = 0;
Begin
StrSql: = 'delete * from my_output ';
Execute immediate strSql;

Output: = '...'; // value assignment
Count: = count + 1;
StrSql: = 'insert into my_output value (count, ''' | output | ''')';
-- ''It is equivalent to a character in single quotes'
Execute immediate strSql;
End;
3. Use the output file method.
If the variable is so large that no table can be inserted, only the file method is used.
(1) create or replace directory TMP as 'd: \ testtmp ';
-- Create a folder path
(2) declare
File_handle UTL_FILE.FILE_TYPE;
Output varchar2 (30000 );
Begin
Output: = "....";
File_handle: = UTL_FILE.FOPEN ('tmp ', 'output.txt', 'w', [1-32767]);
-- Four parameters: Directory, file name, open mode, maximum number of rows (2000 by default)
UTL_FILE.PUT_LINE (file_handle, output );
UTL_FILE.FCLOSE (file_handle );
Exception
WHEN utl_file.invalid_path THEN
Raise_application_error (-20000, 'error: Invalid path for file or path not in INIT. ORA .');
End;