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

and

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.