Fox API for PL/SQL

This worksheet will explain the way that you can embed anonymous PL/SQL blocks (ie. Non-stored procedures/functions) and DML (Data Manipulation Language ie. SELECT, INSERT, UPDATE, DELETE SQL commands etc.).

They differ from the fm:query blocks because they do not return result sets, but you can use bind variables both as an input and as an output, so data can be returned in that way.

SYNTAX

The fm:api command specifies the API block within an fm:db-interface. When creating a db-interface, you can mix and match both fm:query and fm:api blocks, but the fm:query ones must be declared before the fm:api ones.

fm:api blocks can be called in a similar way to fm:query blocks, by using the fm:run-api command in an fm:do block.

The fm:using child element has optional attributes and can contain an XPath. It can be used in the same way as that of an fm:query using clause, but it is possible to specify the direction and type of a bind variable.

When specifying a datadom-type, it is customary to use a datadom-location to match an XPath, rather than include an XPath expression as a text node within the fm:using tag elements. The datadom-type specifies the type of data coming from or going to the location targeted by the XPath expression and the sql-type specifies a value similar to that of Oracle’s own datatypes to dictate how Oracle should treat it.

Under most circumstances, FOX should guess the datatypes, but occasionally it is necessary to override this.

NB: See examples for use of fm:using.

fm:run-api attributes:

Attribute	Values
api	Name of API to run
interface	Name of db-interface
match	XPath to run API in context of
mode	ADD-TO AUGMENT PURGE-ALL PURGE-SELECTED

The fm:run-api command runs similarly to the fm:run-query statement, except the API attribute is used to run a specific fm:api.

The match clause makes the API execute over one or more elements, changing the context of each execution based on the XPath expression. For instance, the bind variable XPath will run in context of the match clause, so ./NAME will be evaluated to <match_clause>/NAME.

EXAMPLES

The fm:statement in the run-api can either contain anonymous PL/SQL or DML. As a PL/SQL block with bind variables, it can look as follows:

<fm:api name=”api name”>
<fm:statement>
DECLARE
l_myNum NUMBER(4) := :IN_NUM;
l_myStr VARCHAR2(30) := :IN_STR;
l_myXML XMLTYPE;

BEGIN

SELECT
XMLElement("MY_DATA"
, XMLElement("MY_STRING",l_myStr)
, XMLElement("MY_NUM",l_myNum)
)
INTO l_myXML
FROM sys.dual;

:OUT_XML := l_myXML;

END;
  </fm:statement>
  <fm:using name="IN_NUM">/*/SOME_NUMBER</fm:using>
  <fm:using name="IN_STR" direction="in" datadom-type="string" datadom-location="/*/SOME_STRING" sql-type="varchar" />
  <fm:using name="OUT_XML" direction="out" datadom-type="dom" datadom-location="/*/SOME_XML" sql-type="xmltype" />
</fm:api>

The above code will take a Number and String from the Data DOM, binding it to the PL/SQL. The PL/SQL will do some processing, in this case it will generate an XML structure based on the two bind variables, and then it will bind the result outwards to a location in the Data DOM as XML.

The first and second fm:using statements show the different syntax that can be used. For the first fm:using statement, only one attribute is set and the XPath is specified as a text node, however for the second statement, everything is specified, overriding FOX’s own guess.

EXERCISES

Please use your XX_EMPLOYEEMODULE (where XX are your initials) file for the following exercises.

Exercise 1

Write an API called “api-emp” to populate the structure /*/EMPLOYEE_LIST/EMPLOYEE in the Theme DOM with the ID, Forename, Surname and Hire_date from scott.employee_search. Restrict the query based on Surname using an input bind variable, entered via an input text box on the screen.

Exercise 2

Add new elements to create the “NEW” branch in the schema as follows:

NB: The ID, Title, Forename, Surname and Hire_date can be copied from the Employee_list.

Make sure that the elements display for a new namespace called “new”. All elements should be displayed as editable, apart from ID, which should not be displayed. When the module loads, the new element and its sub-elements should be initialised and set-out in a table with the heading “Add New Employee”.

Create a button called “action-save-emp” that will save the data from the new elements into the scott.employee_search table and add the new row to the employee list above. The ID for the new column should be the current maximum ID + 1. This should be completed by using an API to select the next ID, insert the data using a DML statement and return the ID back to the module.

NB: After inserting, you will need to commit the transaction to the database. You can do this with the FOX command:

<fm:transaction operation=”COMMIT”/>

Using fm:api for PL/SQL and DML