Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Working with PL/SQL

There's not much to say about executing PL/SQL blocks via iSQL*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 iSQL*Plus from Oracle Database 10g 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 <br> tags separate the three lines of output. Interestingly, if you SET MARKUP HTML PREFORMAT ON, iSQL*Plus correctly omits the <br> tags and incorrectly omits wrapping the output in <pre> and </pre> 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 10g 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 ...