PL/SQL Methods

For Inbound message flows it is possibly to define directly a PL/SQ method instead of BizAPI using the PL/SQL Routing Address type. The method however has to fulfill some conditions: it has to be a function placed within a package own by the Application Owner (IFSAPP) taking CLOB as the only input argument and returning a CLOB.

The input argument can contain an XML document encoded to UTF-8 or a plain text document, depending on what is read by the Connect Reader or delivered by Transformer, if used. Any other type of input is treated as binary data and will be, after being encoded to Base64, placed into attribute BIN_DATA of the special framework view BINARY_PARAMETER (see also BizAPI with Binary Data).

The same rules apply to the value returned by the function: it can be either plain text or XML (or null). If the method is supposed to return binary data, it has to be Base64 encoded and encapsulated within the BINARY_PARAMETER view too.

Additional data is sent by the Batch Processor and is available for the method through Application Context. Following Application Message parameters are passed: APPLICATION_MESSAGE_ID, SUBJECT, CREATED_FROM, SENDER, RECEIVER, MESSAGE_FUNCTION, MESSAGE_TYPE, EXTERNAL_MESSAGE_ID.

A simple example of a method reading an Application Context parameter and manipulating the incoming XML document:

FUNCTION Plsql_Address_Test (xml_ CLOB) RETURN CLOB IS
   app_msg_id_ NUMBER;
   result_     CLOB;
BEGIN
   Log_SYS.App_Trace(Log_SYS.trace_, 'Incoming document:'||xml_);
   app_msg_id_ := App_Context_SYS.Find_Number_Value('APPLICATION_MESSAGE_ID', 0);
   Log_SYS.App_Trace(Log_SYS.trace_, 'Extracting app msg id:'||app_msg_id_);
   result_ := replace(xml_, '</COMMENTS>', '/Modified by Batch_Processor_Test_API.Plsql_Address_Test ['||app_msg_id_||']</COMMENTS>');
   RETURN result_;
END Plsql_Address_Test;

Another simple example working on text:

FUNCTION Plsql_Address_Test_Txt (txt_ CLOB) RETURN CLOB IS
   pos_ NUMBER;
   scenario_ VARCHAR2(50);
BEGIN
   Log_SYS.App_Trace(Log_SYS.debug_, 'Incoming document:'||txt_);
   pos_ := instr(txt_, '@', 1, 2);
   scenario_ := substr(txt_, pos_+1, 32);
   RETURN 'Document retrieved by Plsql_Address_Test_Txt method for scenario ['||scenario_||'].';
END Plsql_Address_Test_Txt;

This method is expecting an identifier (scenario_) of given length (32) following the '@' character in the incoming plain text document. The identifier is then extracted and sent back in the returning text.

Two examples showing how to work with binary data:

FUNCTION Plsql_Address_Test_Bin (xml_ CLOB) RETURN CLOB IS
   doc_    Plsqlap_Document_API.Document;
   data_   BLOB;
   result_ CLOB;
BEGIN
   Log_SYS.App_Trace(Log_SYS.debug_, 'Incoming XML document:'||chr(10)||xml_);
   Plsqlap_Document_API.From_Ifs_Xml(doc_, xml_);
   data_ := Plsqlap_Document_API.Get_Blob_Value(doc_, 'BIN_DATA');
   Modify_Bmp___(data_);

   doc_ := Plsqlap_Document_API.New_Document('BINARY_PARAMETER');
   Plsqlap_Document_API.Add_Attribute(doc_, 'BIN_DATA', data_);
   Plsqlap_Document_API.To_Ifs_Xml(result_, doc_);
   Log_SYS.App_Trace(Log_SYS.debug_, 'Resulting XML document:'||result_);
   RETURN result_;
END Plsql_Address_Test_Bin;

FUNCTION Plsql_Address_Test_Bin2 (xml_ CLOB) RETURN CLOB IS
   enc_data_ CLOB;
   ns_       VARCHAR2(4000) := 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ifsrecord="urn:ifsworld-com:ifsrecord" xmlns="urn:ifsworld-com:schemas:connectframework_binary_parameter"';
   data_     BLOB;
   result_   CLOB;
BEGIN
   Log_SYS.App_Trace(Log_SYS.debug_, 'Incoming XML document:'||chr(10)||xml_);

   SELECT EXTRACTVALUE(XMLTYPE(xml_), '/BINARY_PARAMETER/BIN_DATA', ns_) INTO enc_data_ FROM dual;
   Log_SYS.App_Trace(Log_SYS.debug_, 'Extracted data:'||enc_data_);

   data_ := From_Base64___(enc_data_);
   Modify_Bmp___(data_);
   enc_data_ := To_Base64___(data_);

   SELECT XMLAGG(XMLELEMENT("BINARY_PARAMETER", XMLELEMENT("BIN_DATA",enc_data_))).getClobVal() INTO result_ FROM dual;
   Log_SYS.App_Trace(Log_SYS.debug_, 'Resulting XML document:'||result_);
   RETURN result_;
END Plsql_Address_Test_Bin2;

Both examples do the same - take an XML document of type BINARY_PARAMETER with Base64 encoded binary data encapsulated within attribute BIN_DATA, extract the binary data to a BLOB variable, do some manipulation, create the resulting view, also of type BINARY_PARAMETER and put the modified data back to the resulting view.

The only difference between the two methods is that the first one is using IFS Document API available through the Plsqlap_Document_API package, the second one is using Oracle XML handling. While decoding/encoding from/to Base64 is done automatically by the framework when using IFS Documents, it has to be done explicitly when working with Oracle XML API.

The called method Modify_Bmp___ is expecting the incoming data to be a BMP image of size 20 x 20 pixels representing a red square. It then changes the color to rose of the square's upper half.

All the example methods above can be found in the Batch_Processor_Test_API package supplied within the IFSBAS component.