"Oracle" Output debug information ____oracle

When you debug Oracle's stored procedures and functions, you may need to output some debugging information, dbms_output with Oracle. The Put_Line () method can satisfy the requirements in most cases, but if the output is more than 256 characters, it is not supported, you can take the following methods:

1. The most basic dbms_output. Put_Line () method.
Wherever you are, you can use Dbms_output as long as it is between begin and end. Put_Line (output);
The problem, however, is that the function can display up to 255 characters at a time, or the buffer will overflow.
In addition, the function Dbms_output. The ENABLE (20000) function is simply to set the total output buffer size of the entire process,
Rather than dbms_output. The buffer size of the put_line ().
For variables that are more than 255 characters, use Dbms_output. The Put_Line () method is not effective.

DECLARE
OUTPUTVARCHAR2 (200);
BEGIN
OUTPUT: = ' ... ';--Assign value
Dbms_output. Put_Line (OUTPUT);
End;

2. Use the table method.
First create a table, and then insert the contents of the Pl/sql in the table. And then view the content from the table. The VARCHAR2 supports up to 4,000 characters long.

--(1) Create a table
CREATE TABLE my_output{
ID Number (4),
TXT VARCHAR2 (4000)
);
--(2) Insert the contents of the output into the table
DECLARE
OUTPUT VARCHAR2 (4000);
strSQL VARCHAR2 (4500);
CNT Number (4): = 0;
BEGIN
strSQL: = ' Delete * from My_output ';
EXECUTE IMMEDIATE strSQL;
OUTPUT: = ' ... '; --Assigning value
CNT: = cnt + 1;
strSQL: = ' Insert into My_output value (CNT, ' | | OUTPUT | | ';--' ' in single quotes the equivalent of a character '
EXECUTE IMMEDIATE strSQL;
End;

3. Use the output file method.
If the variable is so large that there is no way to insert using the table, only the file mode is used.
--(1) Create a folder path
Create or replace directory TMP as '/tmp '; --windows on the test, as if not, on the Linux machine can be

--(2) Output to file
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 (default is 2000)
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;

Description
The 3rd method needs to be granted permissions through the SYS User:
GRANT CREATE any DIRECTORY to "fbase";
Grant read, write on directory TMP to "Fbase"; Giving read and write access to the TMP path