Working with Stored Procedures in Oracle
Sample Procedure
In Notepad, type:
CREATE OR REPLACE PROCEDURE sample
IS
BEGIN
NULL;
END;
Save your file as sample.sql.
Let’s go through the procedure line by line. The lines are numbered for your convenience:
1 CREATE OR REPLACE PROCEDURE sample
2 IS
3 BEGIN
4 NULL;
5 END;
Line 1:
CREATE OR REPLACE PROCEDURE is an SQL statement that instructs Oracle to create a procedure called sample, and to overwrite it if it exists.
Line 2:
The IS keyword signals that a PL/SQL body will follow.
Line 3:
The BEGIN keyword signals the start of a PL/SQL body.
Line 4:
The NULL PL/SQL statement indicates that no action should be performed. We cannot just leave it out, because at least one statement is required in a PL/SQL body.
Line 5:
The END keyword signals the end of the PL/SQL block.
Now let's move on to creating a procedure.
Read more
You need the CREATE PROCEDURE system privilege to create a procedure in your own schema.
Open SQL*Plus from Windows and log on to your database.
From SQL*Plus, open your sample.sql file.
SQL*Plus loads the contents of your sample.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:
1 CREATE OR REPLACE PROCEDURE sample
2 IS
3 BEGIN
4 NULL;
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>
Now your procedure is created, compiled and saved on your Oracle database.
We have a procedure now, so let's run it.
Run your procedure from the SQL*Plus command prompt with the EXECUTE command like this:
SQL> EXECUTE sample;
SQL*Plus assures you the procedure executed successfully:
PL/SQL procedure successfully completed.
The EXECUTE statement is easy and fast to type.
You can also run your procedure from within an unnamed PL/SQL block. At the SQL*Plus command prompt, it looks like this:
SQL> BEGIN
2 SAMPLE;
3 END;
4 /
PL/SQL procedure successfully completed.
By calling your procedure from within an unnamed PL/SQL block, you can even call your procedure twice, like this:
SQL> BEGIN
2 SAMPLE;
3 SAMPLE;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL*Plus refers to the unnamed PL/SQL block when it says "PL/SQL procedure successfully completed." So even though we called the sample procedure twice, we only get one message back. So much for going the extra mile!
Now that we've run our procedure, what do we need to do if we want to change it?
Let's write a procedure that outputs the string "Hello World!".
Open your sample.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 sample
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
Save your file as sample.sql.
From SQL*Plus, open your sample.sql file.
SQL*Plus loads the contents of your sample.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:
SQL>
1 CREATE OR REPLACE PROCEDURE sample
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 sample;
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 sample;
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.
Let’s introduce a compilation error into your procedure declaration.
Open your sample.sql file in Notepad. Replace the DBMS_OUTPUT.PUT_LINE procedure call with the NULLL statement (notice the three "l"s!), an invalid PL/SQL statement. Your program should look like this:
CREATE OR REPLACE PROCEDURE sample
IS
BEGIN
NULLL;
END;
Save your file as sample.sql.
From SQL*Plus, open your sample.sql file.
SQL*Plus loads the contents of your sample.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:
SQL>
1 CREATE OR REPLACE PROCEDURE sample
2 IS
3 BEGIN
4 NULLL;
5* END;
SQL>
Execute the contents of the SQL*Plus buffer. Type a front slash and press <enter> like this:
SQL> /
Your procedure is compiled and saved on the database. However, SQL*Plus warns us of compilation errors:
Warning: Procedure created with compilation errors.
Let’s see the compilation errors. First, we need to run two SET commands to ensure the SQL*Plus buffer does not overflow.
At the SQL*Plus command prompt, type:
SQL> SET ARRAYSIZE 1
SQL> SET MAXDATA 60000
SQL>
Again, SQL*Plus remains secretive of the result. Let's see the errors. At the SQL*Plus command prompt, type:
SQL> SHOW ERRORS PROCEDURE sample
You should see the compilation error:
LINE/COL
----------------------------------------------
ERROR
----------------------------------------------
4/3
PLS-00201: identifier 'NULLL' must be declared
4/3
PL/SQL: Statement ignored
Oracle doesn't recognize the NULLL statement with the three "l"s. But Oracle won't hold it against you.
Change your procedure declaration in Notepad by inserting the proper NULL statement, and follow the steps to create your procedure again on the Oracle database.
What if you want to completely remove a procedure from your database? That's what we'll cover next.
If you no longer want a procedure in your database, you can remove it.
The SQL statement DROP PROCEDURE removes a procedure from a database.
The effect is permanent- so better think twice
DROP PROCEDURE is classified in SQL as a Data Definition Language (DDL) statement. Other examples of SQL statements in this category include CREATE, ALTER, RENAME and TRUNCATE.
Oracle issues an automatic COMMIT after a DDL statement is executed. Therefore, no rollback is possible after executing a DDL statement.
You need the CREATE PROCEDURE system privilege to create a procedure in your own schema.
At the SQL*Plus command prompt, issue the DROP PROCEDURE SQL statement to remove your procedure called sample:
SQL> DROP PROCEDURE sample;
SQL*Plus assures us the procedure has been removed:
Procedure dropped.
Now let's sum up it all up.
An Oracle stored procedure is a program stored in an Oracle database.
Procedures are written in the PL/SQL programming language. You can use the Notepad text editor to write a procedure. Save each procedure with a .sql file name extension.
Log on to an Oracle database with Oracle's SQL*Plus tool. It submits SQL and PL/SQL statements to the Oracle database.
The SQL statement CREATE OR REPLACE PROCEDURE creates, compiles and saves a procedure on an Oracle database, regardless of whether the procedure contained compilation errors or not.
Run a procedure stored on an Oracle database from the SQL*Plus tool. Use the EXECUTE statement, or an anonymous PL/SQL block to call your procedure.
The clause OR REPLACE from the SQL statement CREATE OR REPLACE PROCEDURE overwrites an existing stored procedure on the Oracle database.
View a procedure's compilation errors with the SQL*Plus command SHOW ERRORS.
The SQL statement DROP PROCEDURE permanently removes a procedure from an Oracle database.
HI
ReplyDelete