Oracle Database PL/SQL process debugging Output Method

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;

If the preceding execution result is successful but not displayed, run set serveroutput on;

2. How to Use tables

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 | );

-- Equivalent to a character in single quotes

Execute immediate strSql;

End;

3. How to Use the output file

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: esttmp;

-- 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;