Complete Guide to Printing Text in Oracle SQL Developer

Introduction

When developing databases using Oracle SQL Developer, there is often a need to output prompt messages or debugging information before and after SQL statement execution. Many beginners encounter issues with directly using the PRINT command. This article systematically introduces the correct methods for outputting text in SQL Worksheet.

Basic Usage of DBMS_OUTPUT.PUT_LINE

Oracle Database provides the DBMS_OUTPUT package to handle output operations, with the PUT_LINE procedure being the most commonly used method for text output. However, before using it, ensure that the serveroutput parameter is enabled:

set serveroutput on format wrapped;
begin
    DBMS_OUTPUT.put_line('Simple comment message');
end;
/

After executing the above code, you will see in the output panel:

anonymous block completed
Simple comment message

Detailed Explanation of serveroutput Parameter

The set serveroutput on command is used to enable the output function of the DBMS_OUTPUT package. The format wrapped option specifies the output format to automatically wrap lines, ensuring that long text is displayed correctly. This setting is valid for the current session and needs to be executed before each script that requires text output.

Practice of Multiple Text Outputs

In actual development, it is often necessary to insert multiple output messages between different SQL statements:

set serveroutput on format wrapped;
begin
    DBMS_OUTPUT.put_line('First simple comment');
end;
/

-- Execute query operation
SELECT * FROM employees;

begin
    DBMS_OUTPUT.put_line('Second simple comment');
end;
/

The output result will display:

anonymous block completed
First simple comment

anonymous block completed
Second simple comment

Combined Output of Variables and Text

DBMS_OUTPUT.PUT_LINE supports concatenation of variables and strings, which is very useful when dynamically generating output information:

set serveroutput on format wrapped;
declare
    a_comment VARCHAR2(200) :='First comment';
begin
    DBMS_OUTPUT.put_line(a_comment);
end;
/

-- Execute other operations

declare
    a_comment VARCHAR2(200) :='Comment';
begin
    DBMS_OUTPUT.put_line(a_comment || 2);
end;

The corresponding output is:

anonymous block completed
First comment

anonymous block completed
Comment2

Alternative Solution with PROMPT Command

In addition to the DBMS_OUTPUT method, the PROMPT command can also be used to output simple text:

PROMPT text to print

Important Note: When using the PROMPT command, you must select "Run as Script" (F5), not "Run Statement" (Ctrl+Enter). The PROMPT command is more suitable for simple script comments, while DBMS_OUTPUT provides more powerful output control capabilities.

Floating Panel Features in SQL Developer

Referencing SQL Developer's floating panel characteristics, developers can better manage output information. The DESC command (SHIFT+F4) can open description information panels for tables, and these panels can float without affecting operations in the main workspace. Similarly, output information can be optimized for display through appropriate panel configurations.

Best Practice Recommendations

Based on practical development experience, it is recommended to:

• Use DBMS_OUTPUT for segment marking in complex SQL scripts
• Consider using the PROMPT command for simple script comments
• Properly set the serveroutput parameter to ensure output visibility
• Utilize SQL Developer's floating panel features to manage multiple output sources

Common Issue Resolution

Common problems encountered by beginners include:

• Forgetting to set serveroutput on resulting in no output
• Incorrectly using the PRINT command (not supported by Oracle)
• Confusing usage scenarios between PROMPT and DBMS_OUTPUT
• Not correctly using script execution mode

Through the methods introduced in this article, developers can effectively implement text output functions in Oracle SQL Developer, improving development efficiency and code readability.