Home arrow Oracle arrow Page 6 - Oracle Stored Procedures

Change a procedure - Oracle

So what's this business with Oracle allowing programmers to put programs in databases? That's right. They're called Oracle stored procedures, and they're quite useful. Mooh the Cow walks you through writing, creating, debugging, and deleting a procedure.

TABLE OF CONTENTS:
  1. Oracle Stored Procedures
  2. Before you start
  3. Write a procedure
  4. Create a procedure
  5. Run a procedure
  6. Change a procedure
  7. Debug a procedure
  8. Drop a procedure
  9. What you have learned
  10. Further reading
By: Mooh The Cow
Rating: starstarstarstarstar / 879
January 03, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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. 



 
 
>>> More Oracle Articles          >>> More By Mooh the Cow
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: