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.

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:

1. First, you compile HELLO.SQLthis step validates the language syntax and compiles the source in the database.

2. 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; /