9.1 A Hello World Program

[ Team LiB ]

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. 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 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: First, you compile HELLO.SQL�this 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 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; /

[ Team LiB ]