9.1. A Hello World Program

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.

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 slash—this 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 screen—it 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:

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 procedure—both 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;
/