AboutPeter Choi Expertise I am a senior Oracle DBA, PeopleSoft Administrator and Project Manager with
10+ years experience. I have been working with PeopleSoft (HRMS 5, 7, 7.x and
8.9), Oracle RDBMS (7.3 - 11gR1) on various Unix and Windows platforms, and some Oracle Application Server (9i/10gR2). I also have experience with the configuration and administration of BEA`s Tuxedo and WebLogic for PeopleSoft 8.x.
QUICK GUIDE TO USING EXPLICIT CURSORS WITHIN SQL*FORMS 3.0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Many users perceive Explicit Cursors as a difficult means of retrieving
data from the database. In reality, only a few basic concepts are needed
to implement this flexible method, thus, maximizing efficiency and
developer control.
A cursor is a named SQL statement. From the PL/SQL User's Guide and
Reference Manual (Page 2-16):
When a query returns multiple rows, you can explicitly define a cursor to:
* process beyond the first row returned by the query
* keep track of which row is currently being processed.
Cursors allow the designer precise control of the multiple values returned
by the cursor's SELECT statement.
Note that explicit cursors should be used in specific cases only. For most
applications, the default database block is sufficient and easier to use
than explicit cursors. Please read the last section of this document for
situations where explicit cursors may be appropriate.
POPULATING MULTI-RECORD BLOCKS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Explicit cursors can be used to query records into a block, similar to
a normal EXECUTE_QUERY within a database block. The basic difference
between these two methods is that each record queried into the block
is tied, by rowid, to a row in the database. An explicit cursor
retrieves the data and places it into a field as if a user had input
the data by hand. A more in depth discussion of the differences is
included in the last section of this bulletin.
Below is a simple example of populating a multi-record block using
explicit cursors. To recreate this you will need to use the standard
database tables that are supplied with SQL*Forms: create a non-base table
block F_EMP, with 3 fields F_EMPNO, F_ENAME and F_DEPTNO that have the same
datatypes and field lengths as the standard EMP fields EMPNO, ENAME and
DEPTNO in the database.
KEY-HELP
declare cursor C1 is select EMPNO,ENAME,DEPTNO
from EMP
where DEPTNO>10;
begin
go_block('F_EMP'); /* This isn't necessary if you are already in
the block where the values are being entered */
open C1;
loop
fetch C1 into :F_EMP.F_EMPNO, :F_EMP.F_ENAME, :F_EMP.F_DEPTNO;
exit when C1%notfound;
next_record;
end loop;
close C1;
first_record;
end;
Note the looping structure. The FETCH, grabs the next (or first) row from
the table satisfying the cursor declaration, INTO the specified fields. If
it is not the last record, then the cursor is moved to the next record and
the next FETCH INTO is performed. After the cursor is closed, the cursor
is put on the first record of the block, the same functionality as a
regular database block query.
Available cursor options are
%NOTFOUND : TRUE if fetch failed because no more rows were available.
%FOUND : Logical opposite of %NOTFOUND.
%ROWCOUNT : Returns the number of rows fetched from the active set so far.
%ISOPEN : TRUE if explicit cursor is open.
Please see the PL/SQL Manual (page 3-35) for further details.
LOOKUP FIELDS
~~~~~~~~~~~~~
In relational database environments, lookup fields are often performed
to display data referenced from other tables. To recreate the example
below, create a non-database lookup field called LOOKUP within a block
based on the EMP table. LOOKUP will contain the department name (DNAME)
from table DEPT based upon the value of the department number (:DEPTNO)
field in the EMP block. The DEPTNO field is the join field between EMP
and DEPT. The user may wish to make the LOOKUP field non-enterable.
/*=========================================================================*/
/* Get_Name_Value */
/* This procedure attempts to query the department name into the LOOKUP */
/* field from DEPT based on the field value in EMP.DEPTNO. If the value */
/* is not found, then RETURN_CODE := 0, ELSE RETURN_CODE := 1; */
/* This procedure could obviously be made more generic by allowing the */
/* the field value to be populated to be passed in as a parameter. */
/* ----------------------------------------------------------------------- */
procedure get_name_value (return_code IN OUT boolean) is
cursor C1 is select DNAME
from DEPT
where DEPTNO=:EMP.DEPTNO;
begin
open C1;
fetch C1 into :EMP.LOOKUP;
if c1%notfound then /* If value not found then FAIL */
return_code := FALSE;
else
return_code := TRUE; /* If value found then SUCCESS */
end if;
close C1;
end;
POST-QUERY on the EMP block.
/*========================================================================*/
/* Post-Query */
/* Will lookup the department name DNAME from the table DEPT based on */
/* the DEPTNO field in the EMP block for each record queried in. */
/* The RETURN_CODE is not used in this procedure but is needed to */
/* compile the procedure. */
/* ---------------------------------------------------------------------- */
declare
RETURN_CODE boolean;
begin
GET_NAME_VALUE(RETURN_CODE);
end;
/*========================================================================*/
/* On-Validate-Field */
/* Will attempt to populate LOOKUP with department name from DNAME based */
/* on DEPTNO field in the EMP block. The return code is checked if the */
/* procedure GET_VALUE could not query in a related record. */
/* ---------------------------------------------------------------------- */
declare
RETURN_CODE boolean;
begin
GET_NAME_VALUE(RETURN_CODE);
if RETURN_CODE = FALSE then
message('Invalid Department Number, Please Reenter');
raise form_trigger_failure;
elsif RETURN_CODE = TRUE then
message('Department Number is Valid');
end if;
end;
Q: What is the difference between an implicit and explicit cursor.
A: An implicit cursor is of the variety :
SELECT column_name
INTO :block.field_name
FROM table_name;
The syntax for implicit cursors is easier to use yet often, less
efficient. An explicit cursor is DECLARE'd OPEN'ed FETCH'ed and
CLOSE'ed. Note that the user has to explicitly define each step.
Q: I tried an explicit cursor loop and I only get the last record in the block.
A: If the NEXT_RECORD; or DOWN; packaged procedure is not performed, the loop
will dutifully fetch, row by row, into the same record slot until there
are no more rows. What is left, obviously, is the last record fitting the
criteria condition. A NEXT_RECORD; is needed to advance the cursor to
the next record slot after each FETCH.
Q: I've defined an explicit cursor that fetches 5 records from MYTABLE into
my block based on MYTABLE. I update a record and hit commit and I get 5
records inserted to MYTABLE. Why?
A: Fetches into SQL*Forms fields are not tied to the records in the database.
A FETCH INTO is treated as if the user typed in those values from the
keyboard. Thus values can be fetched from any table(s) into any forms
fields. In the above example, SQL*Forms treats the 5 records entered
as new records to be inserted.
Q: When is an explicit cursor closed?
A: An explicit cursor is closed at the end of trigger execution. Thus an
explicit CLOSE is not necessarily needed but HIGHLY recommended. It is
always good programming practice to close what has been opened. Implicit
cursors and forms created cursors (e.g. database block queries) are
kept open while the form is running, mainly for performance reasons. Less
overhead is required to perform a similar query by reusing these objects
rather than creating a new cursor.
* * * * *
Why explicit cursors are more advantageous than:
* Implicit Cursors: a SELECT INTO clause will make two accesses to the
database when it queries in a value, to determine whether or not it
should raise the TOO_MANY_ROWS exception. An example of this is, if the
application uses POST-QUERY to populate some lookup fields on query.
Usually, a SELECT field INTO :field FROM table WHERE primarykey=:primkey;
is expected to return one row. If a row is returned, PL/SQL will access
the database again to see if the form is attempting to fit two rows into
one slot. With an explicit cursor, one fetch can be explicitly stated
to be performed, assuming somewhat correctly that there are no duplicate
primary keys returned. Thus on average, an implicit cursor will make
two accesses to the database on a single row query while an explicit
cursor will make only one. This could be a significant performance
advantage, especially in client-server environments.
* Base Table Block Query: Explicit cursors are based on a select
statement. Any valid select statement can be declared for an explicit
cursor. Thus, you may use more complex SQL statements (for example,
joins) than the ones created through SQL*Forms automatically. The
disadvantages are that by creating an explicit cursor, the designer must
recode the inherent features of a database block, such as locking,
array fetching, inserts, updates and deletes.
* Block Based on Complex View : A block can be based on a view, allowing
the use of complex SQL select statements, buffering and array fetching.
Views, however, can be slower in execution because of the extra level
of translation performed. There is a tradeoff between views and explicit
cursors, dependent upon the buffer size and how large and complex the view
is versus a straight bufferless series of fetches. Generally, the fewer
records retrieved, the more advantageous it is to use explicit cursors.
Like the explicit cursor, updates and deletes will have to be coded by the
designer for more complex views. (Please see the SQL Language Reference
pg. 5-75 CREATE VIEW sect.) One additional caveat is that a user cannot
update a table in SQL*Forms through a view based on that table if any of
the view columns are derived. SQL*Forms attempts to update all of the
fields for that record when an UPDATE is sent to the database. In these
cases, an ON-UPDATE trigger should be written to handle this.
References
----------
SQL*Forms Designer's Reference, Version 3.0. Part No. 3304-V3.0 0191
PL/SQL User's Guide and Reference, Version 1.0. Part No. 800-V1.0
SQL Language Reference Manual, Version 6.0. Part No. 778-V6.0
