Document-ID: 16346.1
Subject: (V4/V45) USER-DEFINED FUNCTIONS IN ORACLE FORMS
Author: GKAMINAG
Last Modified Date: 28 May 1996
User-defined Functions in Oracle Forms
Introduction
------------
The ORACLE RDBMS V7.1 first introduced the capability to called stored
functions from within SQL statements. However, this capability is not yet
available from Oracle Forms. This is primarily because internally Oracle
Forms uses a version of PL/SQL which cannot resolve these references.
This bulletin outlines alternative methods of accessing user-defined
functions in Oracle Forms. For each situation where calling the function
from SQL may be used, an alternative is given. (For brevity, I will employ the
abbreviation UDFs for User-defined Functions)
Example
-------
As an example for the sections, I will use the following UDF, called
LINETOT, which will take an item quantity and a unit price, and return
the line total (unit * price). To create this function, log into SQL*Plus and
issue the following statement:
CREATE OR REPLACE FUNCTION LINETOT (UNITS IN NUMBER, PRICE IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN UNITS * PRICE;
END;
Note: Oracle Forms V4.5 users can create this function directly from the
forms designer by inserting in the object navigator under the node
Database Objects > <your username> > Stored Program Units.
A Generic Solution
------------------
There is one solution which works for all the situations discussed below --
create a view based on the table with the UDF as a calculated column. For
instance, the following SQL will not compile:
SELECT LINETOT(QTY, ACTUALPRICE) INTO :MYFIELD FROM ITEM;
But, if we create a view:
CREATE VIEW ITEM_TOT AS
SELECT LINETOT(QTY, ACTUALPRICE) LTOTAL FROM ITEM;
then the following will do exactly the same as the original, and WILL compile:
SELECT LTOTAL INTO :MYFIELD FROM ITEM;
Remember that it is the PL/SQL compiler which causes the problem. By
referencing the view, the UDF is disguised as a normal column, and the
PL/SQL compiler lets it pass. Creating a view is a generic solution which
will work for all examples below, but it does create an additional database
object. We now focus on specific techniques for each situation which avoid
creating a view.
UDFs in List of Values
----------------------
The one place in Oracle Forms where UDFs can be called from a SQL statement
is in the select definition of a record group. Since this is possible,
UDFs can be used to calculate columns of an LOV. For instance, forms will
accept an LOV created with a select defintion of:
SELECT QTY, ACTUALPRICE, LINETOT(QTY, ACTUALPRICE)
INTO :QUANTITY, :PRICE, :LINETOTAL
FROM ITEM
You may notice that this same SQL would not be accepted in a trigger.
The reason for this difference is that the SQL of the LOV gets sent directly
to the database, but the SQL in triggers need to be passed through the
PL/SQL compiler, which cannot resolve references to UDFs in SQL.
Select Into
-----------
Since the PL/SQL compiler cannot resolve UDFs in SQL statements, statements
of the form
SELECT ORDID, ITEMID, LINETOT(QTY, ACTUALPRICE)
INTO :ORDER, :ITEM, :LINETOTAL
FROM ITEM;
WHERE ORDID = 605 AND ITEMID = 6;
simply won't compile. Instead, make use of the fact that UDFs are stored
functions (which the PL/SQL compiler CAN resolve), and translate the above
to the following:
SELECT ORDID, ITEMID
INTO :ORDER, :ITEM
FROM ITEM
WHERE ORDID = 605 AND ITEMID = 6;
:LINETOTAL := LINETOT(:QUANTITY, :PRICE);
In other words, break it down into two steps -- first, fetch the non-calculated
values, then perform the calculation as a stored function on the variables
which now hold the fetched values. Note that like any other select into,
this only applies to the current record, and the select must return one and
only one row. If you need to return multiple values, you need to use a
cursor loop (see below).
Alternatively, you can create a view based on the table which includes the
UDF as a calculated column, then SELECT INTO from that view (see above)
Where/Subselects
-----------------
If the UDF is part of the where clause or contained in a subselect of the
select statement, as in:
SELECT ORDID INTO :ORDID FROM ITEM
WHERE LINETOT(QTY, ACTUALPRICE) > 50;
SELECT ORDID INTO :ORDID FROM ORD
WHERE 1000 < (SELECT SUM(LINETOT(QTY, ACTUALPRICE))
FROM ITEM
WHERE ITEM.ORDID = ORD.ORDID)
then the only recourse is to utilize a view on the table which has the UDF
as a column of the view (see above) or to dynamically populate and parse a
record group (see below).
Cursor
------
Using a view is also the easiest method for resolving cursor loops which
have UDF references in them. However, record groups can also be used, since
their query strings are sent directly to the database, bypassing the
PL/SQL engine. The following cursor block:
DECLARE
CURSOR MYCUR IS SELECT ORDID, ITEMID, LINETOT(QTY, ACTUALPRICE) LTOTAL
FROM ITEM
WHERE ORDID = :ORDERNUM;
F_ORDID NUMBER;
F_ITEMID NUMBER;
F_LTOTAL NUMBER;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO F_ORDID, F_ITEMID, F_LTOTAL;
EXIT WHEN MYCUR%NOTFOUND;
<processing>
END LOOP;
END;
will not compile due to the UDF reference in the cursor defintion. The
following block accomplishes the same thing:
DECLARE
RGID RecordGroup;
ERRORCD NUMBER;
NUMRECS NUMBER;
5 F_ORDID NUMBER;
F_ITEMID NUMBER;
F_LTOTAL NUMBER;
BEGIN
RGID := CREATE_GROUP_FROM_QUERY('MYGRP', 'SELECT ORDID, ITEMID, ' ||
10 'LINETOT(QTY, ACTUALPRICE) LTOTAL FROM ITEM WHERE ' ||
'ORDID = ' || TO_CHAR(:ORDID));
ERRORCD := POPULATE_GROUP(RGID);
IF ERRORCD != 0 THEN
MESSAGE('ERROR WITH QUERY');
15 ELSE
NUMRECS := GET_GROUP_ROW_COUNT(RGID);
FOR LOOPCNT IN 1..NUMRECS LOOP
F_ORDID := GET_GROUP_NUMBER_CELL('MYGRP.ORDID', LOOPCNT);
F_ITEMID := GET_GROUP_NUMBER_CELL('MYGRP.ITEMID', LOOPCNT);
20 F_LTOTAL := GET_GROUP_NUMBER_CELL('MYGRP.LTOTAL', LOOPCNT);
< processing >
END LOOP;
END IF;
END;
Here we are populating a record group, then looping through the return, much
how our cursor would work. Lines 9-12 create the record group and populate
it using our query. Notice that the field reference to :ORDID is not part
of the query string, but its actual value gets woven into the string using
concatenation. Lines 9-12 correspond roughly to the cursor declare and open.
Once the record group is populated, lines 18-20 do the fetch into our local
variables using the GET_GROUP_NUMBER_CELL built-in. Note that if the type of
our columns were char, then we would use GET_GROUP_CHAR_CELL, if date, then
GET_GROUP_DATE_CELL.
Again, since the record group does not need to go through the PL/SQL engine,
it can resolve the UDF reference correctly.
DML Statements
--------------
Record group queries bypass the PL/SQL engine, but they only work for
select statements. Thus, another solution must be found to resolve DML
statements such as:
UPDATE INVOICES
SET LINETOTAL = LINETOT(:QUANTITY, :PRICE);
Luckily, with V4.0.12 of Oracle Forms and later, the built-in FORMS_DDL
can send DML directly to the database. FORMS_DDL takes a SQL or PL/SQL string
as an argument, then evaluates it on the server. Thus, the above would work
when written as:
FORMS_DDL('UPDATE INVOICES SET LINETOTAL = LINETOT('||
TO_CHAR(:QUANTITY) || ',' || TO_CHAR(:PRICE) || ')' );
IF NOT FORM_SUCCESS THEN
MESSAGE('DML FAILED');
END IF;
Notice that the field references need to be "woven" into the command string
using concatenation. Since this is sent directly to the database, field
references in the string cannot be resolved. Thus, we weave the actual
values into the string.