Change a procedure - Oracle

Let's write a procedure that outputs the string "Hello World!".

Open your skeleton.sql file in Notepad. Replace the NULL statement with the DBMS_OUTPUT.PUT_LINE procedure call. Your program should look like this:

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World!');
END;

Save your file as skeleton.sql.

From SQL*Plus, open your skeleton.sql file.
 
SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:

SQL>
  1  CREATE OR REPLACE PROCEDURE skeleton
  2  IS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('Hello World!');
  5* END;
SQL>

Execute the contents of the SQL*Plus buffer. Type a front slash and press <enter> like this:

SQL> /

SQL*Plus informs you the procedure has been created successfully and presents the SQL command prompt:

Procedure created.

SQL>

Run your procedure from the SQL*Plus command prompt with the EXECUTE command like this:

SQL> EXECUTE skeleton;

SQL*Plus assures you the procedure executed successfully:

PL/SQL procedure successfully completed.

But wait a minute! We wanted to see the string "Hello World!" Where did that go?

SQL*Plus is quirky. A SET command is needed before output is shown from the DBMS_OUTPUT.PUT_LINE procedure. So let's do that. At the SQL*Plus command prompt, type:

SQL> SET SERVEROUTPUT ON

SQL*Plus remains secretive and provides no feedback. Execute your procedure again. From the SQL*Plus command prompt, type:

SQL> EXECUTE skeleton;

Now it works! SQL*Plus rewards you with:

Hello World!

PL/SQL procedure successfully completed.

Suppose there is a problem with our procedure. Then what? We debug it, and that's what we're going to do next.