Installing a PL/SQL Procedure in the Database
I’ve made a small change to the PL/SQL code in the form of adding a PROCEDURE name, as shown in Example 3-2.
Example 3-2. Completing the declaration of the PL/SQL procedure
CREATE
OR
REPLACE
PROCEDURE
update_employees
IS
CURSOR
c1
is
SELECT
ename
,
empno
,
sal
FROM
emp
ORDER
BY
sal
DESC
;
— start with highest paid employee
my_ename
VARCHAR2
(
10
)
;
my_empno
NUMBER
(
4
)
;
my_sal
NUMBER
(
7
,
2
)
;
BEGIN
OPEN
c1
;
FOR
i
IN
1
.
.
5
LOOP
FETCH
c1
INTO
my_ename
,
my_empno
,
my_sal
;
EXIT
WHEN
c1
%
NOTFOUND
;
/*
in case the number requested
*/
/*
is more than the total
*/
/*
number of employees
*/
INSERT
INTO
temp
VALUES
(
my_ename
,
my_empno
,
my_sal
)
;
COMMIT
;
END
LOOP
;
CLOSE
c1
;
END
;
- This is the changed line.
Tip
The PL/SQL compiler can be configured to emit warnings. The following line of PL/SQL is one way to enable this:
ALTER
SESSION
SET
PLSQL_WARNINGS
=
‘ENABLE:ALL’
;
You can read more about PLSQL_WARNINGS in the Oracle documentation. Jeff Smith’s blog post “PLSQL Warning Messages in Oracle SQL Developer” is another useful source for helping to resolve PL/SQL issues.
You can use CREATE OR REPLACE PROCEDURE to create a stored procedure or to update an existing procedure. In effect, this approach allows you to remove the old version and replace it with a new one. If there is no existing version of the procedure, then just the CREATE part is used. This is a very common concept in database development.
Let’s use the updated version to install the procedure in the database using SQL Developer.
Installing the Stored Procedure in the Database
Copy and paste the PL/SQL code from Example 3-2 into the SQL Developer Worksheet, as shown in Figure 3-6.
Figure 3-6. Installing PL/SQL in the containerized Oracle Database instance
Click the play button and watch out for the message in the Script Output window:
Procedure
UPDATE_EMPLOYEES
compiled
If all is well, you should see something like Figure 3-7. In order to see the procedure name, it might be necessary to click the Refresh button, as can be seen in Figure 3-7.
Figure 3-7. Successfully installed PL/SQL in the containerized Oracle Database instance
You can see the PL/SQL procedure shown under the Procedures control on the left of Figure 3-7. This indicates that the PL/SQL procedure has now been successfully compiled and stored in the database.