|
More Resources by
Google: |
|
|
|
|
IMPORTANT-READ
CAREFULLY
Steps
to create the Form Block
------------------------------
Build
a block using the Data Block Wizard with type of data block as "Table or
View"
based on the managers table. Now,
open the block property sheet to base
the
block on the stored procedures as follows:
In
the block property sheet,
* Set the Query Data Source
Type as Procedure.
* Set the Query Data Source
Name with the appropriate stored procedure.
In this example, for refcur you will enter "managers_pkg.managers_refcur"
and
for table of records you will enter "managers_pkg.managers_query".
Note: One can use either Ref Cursor or Table of Records to perform this
query operation. For this
example, either use the procedure
managers_pkg.managers_query or managers_pkg.managers_refcur.
You do not need to set anything in the "Query Data Source
Columns"
property, as Forms has already done this because you began by creating
the block with data block as a Table.
* Set the Query Data Source
Arguments with the appropriate argument name for
that query.
In this example, "managers_data" is the argument name for both
refcur and
table of records.
* Set the Type to
"Table" for table of records or "Refcursor" for referenced
cursor.
* Set a Name.
In this example, we can use "managers_pkg.mgrtab" for table of
records
(packagename.table_name) or you can use either "managers_pkg.c_managers"
or "managers_data.c_managers" for a ref cursor.
* Set Mode to
"IN/OUT" as the data is flowing between the client and server
and viceversa.
* Set Value (optional)
If
you skip to set typename, you will hit an error. The possible compilation
error
will be PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.
When
you use the "table of records" as the source of query, Forms
automatically
creates
a trigger like Query-Procedure to populate the values that are sent
from
the database through the stored procedure.
* Set the DML target type as
"Transactional triggers" under the Advanced
Database section. This step
is important. You must specify
"transactional triggers" to avoid getting an error:
FRM-40743: THIS OPERATION WITH NO BASE TABLE
REQUIRES THE %S TRIGGER.
at runtime.
Leave all other properties under the Advanced Database section blank.
Note: You must use transactional triggers to perform all DML processing
as your block is based on stored procedures and not a table or view.
If you do not provide these triggers (see code in Step 5) you will
receive runtime error: Frm-40401
No Changes To Save when after
performing DML operations like insert, delete or update.
One
more general example of setting the Query Data Source Arguments in the
block
property palette could be,
ARGUMENTNAME
TYPE TYPENAME
MODE VALUE
-------------
---- --------
---- -----
managers_data
REFCURSOR managers_pkg.c_managers
IN OUT (leave
blank)
Or managers_data.c_managers
OR
managers_data
TABLE managers_pkg.mgrtab
IN OUT (leave
blank)
Steps
to create Transactional Triggers
--------------------------------------
Transactional
triggers must be created a the block level as follows:
*
On-insert trigger
DECLARE
r managers_pkg.managers_rec;
BEGIN
r.empno := :managers.empno;
r.ename :=:managers.ename;
managers_pkg.managers_insert(r);
END;
*
On_lock trigger
managers_pkg.managers_lock(:managers.empno);
*
On-update trigger
DECLARE
t managers_pkg.managers_rec;
BEGIN
t.empno :=:managers.empno;
t.ename :=:managers.ename;
managers_pkg.managers_update(t);
END;
*
On-delete trigger
DECLARE
t managers_pkg.managers_rec;
BEGIN
t.empno :=:managers.empno;
t.ename :=:managers.ename;
managers_pkg.managers_delete(t);
END;
*
On-count trigger (optional)
Note. Because you have based
your block on a stored procedure, Form's
default processing will not return the number of query hits.
This
trigger takes the place of the default processing and will return
the number of query hits.
DECLARE
recs NUMBER;
BEGIN
recs := managers_pkg.count_query_;
SET_BLOCK_PROPERTY('managers', query_hits,recs);
END;
You
now have completed the process for basing a block on a stored
procedure
for single block operations.
|
