Working with PL/SQL

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.


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
Simiral book on Amazon © 2008-2017.
If you may any questions please contact us: