Oracle Stored Procedures - Change a procedure
(Page 6 of 10 )
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.
Next: Debug a procedure >>
More Oracle Articles
More By Mooh the Cow
|
| · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | | |
|
/ 503 
