Jan Carlin's Blog
A Swede in the digital diaspora

Today is a publishing day. This tutorial (page 2) shows you how to use JavaScript in Oracle Forms. Call out to JavaScript in the page the Forms applet was launched from. Call into Forms from JavaScript, causing triggers to fire in the Forms runtime which in turn will execute PL/SQL code. You can even create JavaScript at runtime, creating a browser application with Forms PLSQL code if you'd like.
Feedback on both tutorials are welcome in their respective comments sections.

I just uploaded a tutorial focused on Oracle Forms' support for the "Advanced Queuing" feature in the Oracle database. With this new feature in Forms 11g you can implement asynchronous events in Forms. With it you no longer need to poll for the conclusion of a web service you called through our Java support for example. Just have the web service publish an event on Advanced Queuing and have Forms be notified automatically. AQ (as the feature is often referred to) is a very mature database feature and there are literally hundreds of ways to use it. It has support for JMS, BPEL, propagation from other queuing solutions like IBM's MQ and much, much more.
The tutorial takes you through how to create an AQ queue, how to publish events with the help of stored PL/SQL code, how to set Forms up to be notified of events and how to easily handle the XML data that, optionally, can form the payload of an event.
See the sample code page here on OTN.

The support for AQ we now have in Forms 11g returns an XML representation of the payload of an event if it is declared as an ADT (Abstract Data Type). This is due to a limitation of name resolution between the client and the server.
Parsing XML is not trivial but Oracle has a rich set of dbms packages that makes it almost painless.
To show you I need to set up a simple queue. First we create the type we'll use (as sys):
(See the first comment for text versions of the source code below)

And create an AQ Admin:

Now we can create the queue table as the admin user:

Next we create the queue itself and start it:

Finally we add a subscriber:

The XML we get in the WHEN-EVENT-RAISED trigger on the event object we declare in the Object Navigator will look something like this:

Assuming that we would want to find the record with the empno in that payload this PLSQL code would make sense:

But how do we parse it? I came up with this:

This package declares a package variable (so it is available directly for Forms) of the same type we used for the payload, makes an XML document of the payload XML, selects the correct nodes and get the values for us using the XPath support we have in the XSLProcessor package. XPath is very similar in concept to the CSS selectors. You declare what data you want in the XML tree and you then operate on that node/data. There is support for arrays (not yet suppoerted by Forms. It is in the works) and attributes. JQuery uses a similar concept. See below for more on XPaths.
The "parse" procedure is generic enough to be reused. The myEmp variable could be placed in the body of the package and accessed with a "get" function so the API of the package wouldn't change much when the variable changes.
The getValueAt function takes a list of nodes (as selected by the "parse" procedure in this case) and an XPath expression and returns the value of the node selected by the XPath expression.
The XPath strings will have to change according to the specs of the XML so they should be factored out if you want to make this idea more generic but for this exercise I have made a package that specifically handles the ADT I use in this example. Creating a ADT at runtime is not possible in Forms.

If ename in this example was stored in a nested ADT, like this:

the path would change to

'SYS.SUB_TYPE/ENAME'

If the top ADT was an array of another type:

the path would change to

'NAMES/NAME_ARRAY_T[37]/ENAME'

if the 38th ename item was the desired one.
Looping over all ename items is thus possible with some string concatenation and a call to dbms_xmldom.getLength.

Some functions are available. So for example, the path

'NAMES/NAME_ARRAY_T[last()]/ENAME'

would yield the last ename item.

Important! As of the writing of this post (Aug 2009), there is a defect in the XML generation in Forms (# 8773035) which makes it impossible to use arrays as sub-types. The defect is scheduled to be fixed in the first patch set of 11g of Forms. Single, non-array, sub-types works. AQ can take a number of other complex payload types, none of which works in Forms as of 11.1.1. XMLType support is being worked on. Support for the ANYDATA data type in AQ payloads in Forms would be good to have since Forms in general works well with it but is not currently planned.

For completeness sake, here is the last missing piece, the findEmployee procedure used above:

See the first comment for text versions of the source code above.

Putting together a tutorial that will be on OTN soon for the Advanced Queuing support in Oracle Forms 11g, I found a pitfall and possibly a bug and I wanted to share what I found and an intermediate the solution to the problem.
When using an ADT (Abstract Data Type) payload in an Advanced Queuing (AQ) queue that has a sub-type (a nested type) and is created in a different schema than the creator of the queue table and the queue (the type supporting a JMS message queue implemented in AQ that is created in the sys schema is a good example of this) you will be able to do everything needed to get the function working in Forms if you specify the payload data type with the schema name (SYS in this example):

begin
  dbms_aqadm.create_queue_table(
    queue_table => '...',
    multiple_consumers => TRUE,
    queue_payload_type => 'sys.aq_payload_type')
end;

When you go to retrieve the XML version of the payload however, you are out of luck. The XML contains the name of the type but no data.
The remedy is to create a synonym, either privately for the user running the form or publicly, for all data types:
create public synonym aq_payload_type for sys.aq_payload_type;

Update:
This is not a problem with Forms but a feature of how you define types in the Oracle database. When you define the payload type you need to either specify the schema name (sys) or create a synonym. This becomes non-trivial when you have nested ADTs in the payload type declaration. The sub-type needs to be declared with the schema name if there is no synonym. In an ideal world perhaps the schema name of the parent type should be the default schema name for nested types but Forms only tries to resolve the nested type with the name declared. If the declared name is unqualified (it has no schema designation) Forms will only look in the schema of the user running the form and the name resolution fails if that type is owned by a different schema.

Examples:
Connect as sys and create two types like this:

create or replace type mySubType as object (
  address varchar2(100)
);
create or replace type myParentType as object (
  name varchar2(50),
  addr mySubType
);

If you now specify the queue payload with the schema name:

begin
  dbms_aqadm.create_queue_table(      
    queue_table        => '...',            
    multiple_consumers => TRUE,
    queue_payload_type => 'sys.myParentType')
end;

it all seems to work until you get the XML back and there is no data there. The reason is that Forms cannot resolve the reference to mySubType in sys.myParentType. Declaring the parent type with a schema name:

...
create or replace type myParentType as object (
  name varchar2(50),
  addr sys.mySubType
);

or, connected as sys, creating a synonym for the sub-type

create public synonym mySubType for sys.mySubType;

makes it all work as expected.

There is an error in the formsweb.cfg file that is shipped with version 11g of Forms making it hard to run Forms on Firefox. The variable jpi_codebase is per default set to the wrong value.
The correct value is this:
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6u12-windows-i586.cab#Version=1,6,0,12
The part "u12" should correspond with the last digits of the version at the end if you use a different version than the one we shipped with. So for 1.6.0_14 it should be:
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6u14-windows-i586.cab#Version=1,6,0,14
Defect 8719740 has been logged for this issue and is listed as fixed in version 11.1.2 and is targeted for patch set 1 of 11g.