Working with PL/SQL

Table of contents:

Working with PL SQL

There's not much to say about executing PL/SQL blocks via i SQL*Plus. You execute those in much the same way as you execute statements. You do not need to include the forward slash that command-line SQL*Plus requires following a PL/SQL block, but you do need to be sure to include the semicolon following PL/SQL's END keyword. As in command-line SQL*Plus, you'll want to SET SERVEROUTPUT ON if your PL/SQL block uses DBMS_OUTPUT to return results. Figure 3-11 demonstrates .

Figure 3-11. Executing a PL/SQL block


Figure 3-11 was created using i SQL*Plus from Oracle Database 10 g with the default setting of PREFORMAT OFF. Because the output is not the result of a query, it's not in the form of an HTML table. Examine the HTML source, and you'll see that
tags separate the three lines of output. Interestingly, if you SET MARKUP HTML PREFORMAT ON, i SQL*Plus correctly omits the
tags and incorrectly omits wrapping the output in


tags. Thus, with PREFORMAT ON, the results from executing the block in Figure 3-11 would all run together on one line:

Line 1 Line 2 Line 3 PL/SQL procedure successfully completed.

No doubt this behavior is a bug. It is present in Oracle Database 10 g Release 1, but I hope it will be fixed in a later release. In the meantime, if you encounter difficulty with line breaks in your DBMS_OUTPUT results, you may find that a SET MARKUP HTML PREFORMAT OFF command fixes the problem.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: