"The pursuit of
happiness is a most ridiculous phrase; if you pursue happiness
you'll never find it." C. P. Snow (1905 - 1980)
first then play the video:
is a REF CURSOR
BLOCK and TRANSACTIONAL TRIGGERS
You have been assigned to
complete the process for basing a block on a stored procedure for "single
What is a REF Cursor?
REF cursors hold cursors in
the same way that VARCHAR2 variables hold strings. This is an added
feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to
be opened on the server and passed to the client as a unit, rather
than one row at a time. One can use a Ref cursor as a target of
assignments and can be passed as parameters to the Program Units. Ref
cursors are opened with an OPEN FOR statement and in all other ways,
they are the same as regular cursors.
What is a table of records?
A table of records is a new
feature added in PL/SQL v2.3. It is the equivalent of a database table
in memory. If you structure the PL/SQL table of records with a primary
key (an index) you can have array-like access to the rows.
Why base a block on a
PL/SQL Table versus a Ref Cursor?
A table of records fetches
all the rows from the table. A reference cursor fetches only those
rows that match your query criteria. If you are planning to filter the
rows with a where clause or your query returns only few records out of
many, you can choose the ref cursor rather than the table of records.
Note the block properties for the number of records set and the
buffered affected blocks, based on stored procedures.
Your assignments are:
Create a table
Create a package spec at
the database level
Create the package body
Create the Form Block
Create following procedure
" delete, and
" count procedures.
These are required steps to
complete the process for basing a block on a stored procedure for
single block operations. You have been assigned to complete the
process for basing a block on a stored procedure for "single block
Connect to SQLPLUS using
SQL> CONNECT iself/schooling
Create a Table
Let's, first create a table that contains all the manager names.
SQL> CREATE TABLE
empno NUMBER PRIMARY KEY,
Create a Package
Create a package
specification at the database level. Read the following procedures and
functions very carefully. At this level, we assume that you know how
to write a PACKAGE SPECIFICATION and BODY.
CREATE OR REPLACE PACKAGE managers_pkg IS
TYPE managers_rec IS
TYPE c_managers IS REF CURSOR RETURN managers_rec;
TYPE t_mgrtab IS TABLE OF managers_rec
INDEX BY BINARY_INTEGER;
PROCEDURE managers_refcur(managers_data IN OUT c_managers);
PROCEDURE managers_query(managers_data IN OUT t_mgrtab);
PROCEDURE managers_insert(r IN managers_rec);
PROCEDURE managers_lock(s IN managers.empno%TYPE);
PROCEDURE managers_update(t IN managers_rec);
PROCEDURE managers_delete(t IN managers_rec);
FUNCTION count_query_ RETURN number;
Note that you can use either a Ref Cursor or a Table of Records on the
FORM Builder to perform the query operation.
Create a Package Body
The next page is a package body that contains the source code
of the procedures and function in the package.
You are encouraged to the movie and take notes about
the package body.
In the next Hands-On you will learn how to use the FORM Builder
tool to call the package and use its procedures and function to
insert, delete, update, lock and count the managers table.
You may use the managers_refcur or managers_query procedures
in the FORM Builder tool to perform the query operation.
CREATE OR REPLACE PACKAGE BODY managerS_pkg
PROCEDURE managers_query(managers_data IN OUT t_mgrtab)
CURSOR manager_select IS
SELECT empno, ename from managers;
for v_managers_select in manager_select loop
ii := 1;
managers_data( ii ).empno := v_managers_select.empno;
managers_data( ii ).ename := v_managers_select.ename;
ii := ii + 1;
PROCEDURE managers_refcur(managers_data IN OUT c_managers)
OPEN managers_data FOR SELECT empno, ename
PROCEDURE managers_insert(r IN managers_rec)
INSERT INTO managers VALUES(r.empno, r.ename);
PROCEDURE managers_lock(s IN managers.empno%TYPE)
SELECT empno INTO v_rownum FROM managers
WHERE empno=s FOR UPDATE OF ename;
PROCEDURE managers_update(t IN managers_rec)
UPDATE managers SET ename=t.ename
PROCEDURE managers_delete(t IN managers_rec)
DELETE FROM managers WHERE empno=t.empno;
FUNCTION count_query_ RETURN NUMBER
SELECT COUNT(*) INTO r FROM managers;
Now, you are ready to create the FORM Block along with the
Transactional Triggers. Let"s go to the Procedure Builder tool to
view the package specification and body.
proclaims that we live in the best of all possible worlds, and
the pessimist fears this is true." - James Branch Cabell
Q: What is a REF Cursor?
Q: What is a table of