donderdag 1 maart 2018

Get data from checkpoints OPA Interviews

In Oracle Policy Automation (see also the other blogs) you can save temporary information using the SetCheckpoint operations. This sends you a base64 encoded blob with the temporary information from your interview, but no structured data.
However, this base64 encoded blob is simply a zipfile, so we can extract it, find the file we need with our data and use that data during our checkpoints if necessary.

In our use case we would like to see the structured data before the submit, because interviews could run longer over time (people need to gather information, request help, etc). So an interview could be open for a number of days before the submit takes place.

The first thing we need to do is decode the checkpoint

l_blob := XXX_OPA_UTIL_PKG.base64decode (checkPointData.Encoded_Checkpoint);

This gives us the zipfile as a blob. The file we are looking for is UserData.xml, so let's extract that from the zip using the AS_ZIP utility created by Anton Scheffer and which you can find at the site of Amis

l_xml_blob := AS_ZIP.Get_File 
    p_zipped_blob   => l_blob 
  , p_file_name     => ' UserData.xml' 

Now we have our file, but we need to convert it to XML first.

l_xml_data := Convert_Blob_To_Xml (l_xml_blob);

And then we run through the XML to find our attributes. You can refer to a specific attr_id (like title, username, etc) to find the value and use that value in your process. In our case it was the title of a request that we wanted to store so we can see from all our running requests which one is which! Otherwise you would only have some kind of internal ID maybe or any data you use to start the interview. In our case we started the interview and all data came from the interview, so we started with nothing more than an ID.

  FOR x IN C_XML_Data (l_xml_data)
    dbms_output.put_line (x.attr_id || ': ' || x.attr_val);
  END LOOP Attributes;

The code to convert the blob to XML was written like this. The reason for replacing the boolean and number values is because we want to treat everything as a string (varchar) later on and that makes our XML query easier, but of course you can do that differently.

  -- Subfunction to convert the blob we extract to XML.
  FUNCTION Convert_Blob_To_Xml 
    p_blob BLOB
  ) RETURN XmlType
    v_clob    CLOB;
v_varchar VARCHAR2(32767);
v_start   PLS_INTEGER := 1;
v_buffer  PLS_INTEGER := 32767;
x         xmltype;
FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(p_blob) / v_buffer) LOOP

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);

v_start := v_start + v_buffer;

v_clob := replace (v_clob,'boolean-val','text-val');
      v_clob := replace (v_clob,'number-val','text-val');
v_clob := replace (v_clob,'date-val','text-val');

x := xmltype.createxml(v_clob);
Return (x);
  END Convert_Blob_To_Xml;

The XML Query is build as follows

    p_xml_data XMLType
     WITH XmlTypeAsTab as (select 
 xml from dual)  
    SELECT attribs.attr_id 
,      attribs.attr_val
    FROM   XmlTypeAsTab x 
    , xmltable ('/user-data/global-inst/attr' passing x.xml 
columns attr_id  varchar2(4000) path './@id'
        attr_val varchar2(4000) path './text-val'
) attribs