9.1 A Hello World Program | Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

The text in this session includes screen ouput from SQL*Plus sessions. Refer to Chapter 2 for hints and use of SQL*Plus commands. The following table lists the common SQL*Plus commands used in this chapter.

SQL*Plus command	Description
`SAVE filename`	Saves the SQL*Plus buffer to a file with an SQL extension
`@filename`	Sends the file to Oracle for compile/execution. You do not need to type the ".SQL" extension.
`SET SERVEROUTPUT ON`	Sets up DBMS_OUTPUT to flush the buffer to the screen.
`/`	Sends the SQL*Plus buffer to Oracle for compile/execution.

From SQL*Plus, type the following Hello World program. First, type the SQL*Plus command SET SERVEROUTPUT ON, then the procedure text. The last line is a forward slashthis is SQL*Plus and instructs SQL*Plus to send the typed text to Oracle to be compiled.

  SQL>  SET SERVEROUTPUT ON  SQL>  CREATE OR REPLACE PROCEDURE hello IS  2  BEGIN  3  DBMS_OUTPUT.PUT_LINE('Hello');  4  END;  5  /  Procedure created.   SQL>  execute hello  Hello   PL/SQL procedure successfully completed.

Use the DBMS_OUTPUT package for standard output. The DBMS_OUTPUT package has overloaded procedures for different datatypes: DATE, NUMBER, and VARCHAR. The package specification for the PUT_LINE procedure is

 procedure put_line (arg VARCHAR2); procedure put_line (arg NUMBER); procedure put_line (arg DATE);

You must execute the SQL*Plus command once for each session if you use DBMS_OUTPUT.

 SET SERVEROUTPUT ON

DBMS_OUTPUT buffers output to a session-specific DBMS_OUTPUT buffer. Each call to the PUT_LINE procedure does not immediately dump text to your screenit stays in the buffer. The SET SERVEROUTPUT ON command directs the SQL*Plus session to dump buffered text to your screen upon completion of a program.

The default buffer size is 20,000 characters. You can increase this with a call to the ENABLE procedure in DBMS_OUTPUT. The maximum is 1,000,000 characters .

  SQL>  execute dbms_output.enable(1000000);  PL/SQL procedure successfully completed.

The content of the SQL*Plus buffer is currently the Hello program. List the SQL*Plus buffer (lower case L) and save (SAVE) it to a file.

  SQL> l   1  CREATE OR REPLACE PROCEDURE hello IS   2  BEGIN   3      dbms_output.put_line('Hello');   4* END;   SQL> save hello   Created file hello   SQL>

You just saved the contents of the SQL*Plus buffer to a host file HELLO.SQL. Run the script. This recompiles the HELLO procedure.

  SQL>  @hello.sql  Procedure created.

If you try to create a table and it exists, an error comes back. To recreate a table you must first drop the table and then create it. This prevents accidental recreating of a table with vital data. Stored procedures have a CREATE OR REPLACE syntax that is consistent with most programming languages. This syntax creates the procedure if it does not exist; if it does exist, Oracle recompiles it.

Edit the file HELLO.SQL and insert invalid syntax. Change PUT_LINE to PUTLINE. Add two additional lines at the end. Add a SQL*Plus command, SHOW ERRORS; then add the SQL*Plus LIST (L) command. The edited file, seven lines long, is the following:

 CREATE OR REPLACE PROCEDURE hello IS BEGIN     dbms_output.putline('Hello'); END; / show errors l

Run the command file with @HELLO. This will (a) compile the procedure with errors; (b) execute the SQL*Plus SHOW ERRORS command, which will list the offending line of PL/SQL code; and (c) provide a full listing of the procedure just compiled with the SQL*Plus LIST command.

  SQL>  @hello  Warning: Procedure created with compilation errors.   Errors for PROCEDURE HELLO:   LINE/COL ERROR   -------- ------------------------------------------------   3/5      PL/SQL: Statement ignored   3/17     PLS-00302: component 'PUTLINE' must be declared   1  CREATE OR REPLACE PROCEDURE hello IS   2  BEGIN   3      dbms_output.putline('Hello');   4* END;

Correct the host file, HELLO.SQL, and replace PUTLINE with PUT_LINE. Compile and execute. Suppress SQL*Plus feedback messages with SET FEEDBACK OFF:

  SQL>  set feedback off  SQL>  @hello  No errors.   SQL>  execute hello  Hello

Create a PL/SQL block that invokes the HELLO procedure. Do this by creating a text file with a SQL extension. Name the file RUN_HELLO.SQL. The following illustrates the syntax for a PL/SQL block (the forward slash is the SQL*Plus command to compile and execute the script).

 DECLARE  Variables  BEGIN  Body of PL/SQL code.  END; /

The DECLARE part is optional; it is not necessary if the block uses no variables.

A PL/SQL block is different from a stored procedure. HELLO is a compiled object in the database. You must first compile HELLO.SQL; then you can execute the procedure.

The text for RUN_HELLO.SQL, shown next , is seven lines long and includes a comment and a forward slash in the last line. This is a PL/SQL block. It executes the HELLO procedure five times.

 -- Filename: RUN_HELLO.SQL BEGIN     FOR run_count IN 1..5 LOOP         hello;     END LOOP; END; /

A single command to compile and execute a PL/SQL block is:

 @filename    -- the SQL file extension is not necessary

Building the HELLO procedure includes two steps:

First, you compile HELLO.SQLthis step validates the language syntax and compiles the source in the database.
The second step is to execute the procedure.

You build the PL/SQL block with one step: compile-and-execute. To run the PL/SQL block in SQL*Plus:

  SQL>  @run_hello  Hello   Hello   Hello   Hello   Hello

Use PL/SQL blocks as test drivers for stored procedures. There is nothing different about the code in a PL/SQL block and the code in stored procedureboth use PL/SQL. Enhance test driver code with exception handling code to display an exception error number and error message. The PL/SQL block below includes a WHEN OTHERS exception handler that prints the exception to a duplicate insert. First, create a table with a primary key constraint.

 CREATE TABLE TEMP(N NUMBER CONSTRAINT PK_TEMP PRIMARY KEY);

TEST_TEMP.SQL is the name of the PL/SQL block and includes an exception handler. In contains 11 lines, including an initial comment and a forward slash as the last line, which must be in Column 1.

 -- Filename: TEST_TEMP.SQL  BEGIN     INSERT INTO temp VALUES (1);     INSERT INTO temp VALUES (1); EXCEPTION     WHEN OTHERS THEN         dbms_output.put_line('Error code:'SQLCODE'***');         dbms_output.put_line             ('Error message:'SQLERRM'***'); END; /

In this PL/SQL block, the second insert fails with a primary key constraint violation. The code in the exception handler uses DBMS_OUTPUT to print the error number and message.

  SQL>  @test_temp  Error code:-1***   Error message:ORA-00001: unique constraint (SCOTT.PK_TEMP)   violated***

PL/SQL blocks convert to stored procedures by adding CREATE OR REPLACE. The following procedure, TEST_TEMP, implements the preceding script as a compiled procedure in the database. The following includes a corrected INSERT statement that will not violate the primary key constraint.

 -- Filename: TEST_TEMP.SQL CREATE OR REPLACE PROCEDURE TEST_TEMP IS BEGIN     INSERT INTO temp VALUES (1);     INSERT INTO temp VALUES (2); EXCEPTION     WHEN OTHERS THEN         dbms_output.put_line('Error code:'SQLCODE'***');         dbms_output.put_line             ('Error message:'SQLERRM'***'); END; /