SQL & PL/SQL

Hi,

I have a requirement to send an xml payload with some parts being encrypted. Here's a sample format.

<xml>
<events>
<create>
<person_id/>
<name/>
<encrypted_data>64b6fcb391380a0a67b07844b46f9544
e7def8df78c6d7e68b79de62fe5627f9
cb30b3d15b0905f94538475bd681b113
</encrypted_data>
</create>
</events>

For reference, the decrypted format of the encrypted_data is:

<content>
<date_of_birth/>
<sex/>
<address/>
<email/>
<phone/>
... etc
</content>

So I created the following wrapper package for encryption.

create or replace package body APPS.xxhr_crypto
is
function encrypt ( src in clob
, typ in binary_integer
, key in raw
, iv in raw default null ) return blob
is
l_enc blob;
begin
dbms_lob.createtemporary ( l_enc, true );
dbms_crypto.encrypt (
dst => l_enc
, src => src
, typ => typ
, key => key
, iv => iv
);
return ( l_enc );
end;

function decrypt ( src in blob
, typ in binary_integer
, key in raw
, iv in raw default null ) return clob
is
l_dec clob;
begin
dbms_lob.createtemporary ( l_dec, true );
dbms_crypto.decrypt (
dst => l_dec
, src => src
, typ => typ
, key => key
, iv => iv
);
return l_dec;
end;

end;

Then I also created a package for generating the payload using dbms_xmlgen. Then a function that basically generates a query into an xml clob which I then encrypt in the main procedure that generates the main payload.

create or replace package body APPS.xxhr_publisher
as
l_nls_date_format2 nls_session_parameters.parameter%type := 'yyyy-mm-dd"T"hh24:mi:ss"Z"';

function get_emp_sensitive_data_as_xml ( p_person_id in number ) return xmltype
is
l_xml xmltype;
l_ctx dbms_xmlgen.ctxhandle;
begin
l_ctx := dbms_xmlgen.newcontext('select * from xxhr_emp_sensitive_pub_v where "personId" = :person_id');
dbms_xmlgen.setbindvalue(l_ctx, 'person_id', p_person_id);
dbms_xmlgen.setrowsettag(l_ctx, 'content');
dbms_xmlgen.setrowtag(l_ctx, '');
l_xml := dbms_xmlgen.getxmltype (l_ctx);
dbms_xmlgen.closecontext(l_ctx);
return l_xml;
end;

procedure publish_emp_delta ( errbuff out varchar2
, retcode out number
, p_event_date in varchar2
, p_from_lastname in varchar2
, p_to_lastname in varchar2 )
is
l_create xmltype;
l_payload clob;
l_create_ctx dbms_xmlgen.ctxhandle;
l_response clob;
begin
execute immediate 'alter session set nls_date_format = ''' || l_nls_date_format2 || '''';

l_create_ctx := dbms_xmlgen.newcontext(
q'~select a.*
, xxhr_crypto.encrypt ( ( xxhr_publisher.get_emp_sensitive_data_as_xml (a."personId") ), 4356, 'SECRET_KEY' ) as "_encryptedData"
from xxhr_employees_pub_v a
where ( ( fnd_date.canonical_to_date(:event_date) is not null and a."_eventType" = 'create' ) or fnd_date.canonical_to_date(:event_date) is null )
and lower(a."lastName") between lower(nvl(:from_last_name,a."lastName")) and lower(nvl(:to_last_name,a."lastName"))
and trunc(a."_eventDate") = nvl(fnd_date.canonical_to_date(:event_date),trunc(a."_eventDate"))~');

dbms_xmlgen.setbindvalue(l_create_ctx,'from_last_name',p_from_lastname );
dbms_xmlgen.setbindvalue(l_create_ctx,'to_last_name',p_to_lastname );
dbms_xmlgen.setbindvalue(l_create_ctx,'event_date',p_event_date);

dbms_xmlgen.setrowsettag(l_create_ctx, '');
dbms_xmlgen.setrowtag(l_create_ctx, 'create');

select xmlserialize ( content ( deletexml ( xmlelement ( "payload", xmlelement ( "correlationId", fnd_global.conc_request_id ), xmlelement ( "initialLoad", nvl2(p_event_date,'false','true') )

            , xmlelement ( "events", ( select dbms\_xmlgen.getxmltype(l\_create\_ctx) from dual ) )  
         )  
      , '/payload/events/\*/\_eventType' ) ) as clob indent size = 4  
   )  

into l_payload
from dual;

dbms_xmlgen.closecontext(l_create_ctx);
end;

end;

When we generate xml payload for many employees, somewhere in the middle, an record will have when some special characters when decrypting the encrypted data. Something like:

<xml>
<events>
<create>
<person_id>1</person_id>
<name>Name</name>
<encrypted_data>
<content>
<date_of_birth>1967-09-14T00:00:00Z</date_of_birth>
<address>ADDRESS LINE 1 ADDRESS LINE 2 ADDRESS LINE 3</address>
¿D(¿¿¿4¿M¿¿)M¿¿*¿¿~MZ¿y.œ¿¿¿¿¿¿
</content>
</encrypted_data>
</create>
</events>

However, when we try to generate xml payload for this particular employee, it work's fine.

I don't know yet what's causing this but it seems to happen only when generating payload for may records. My hunch is that the encryption procedure xxhr_crypto.encrypt is the culprit since I am not closing/freeing the temporary lob? How do I do this btw, since it is used in a SQL statement, I can't close or free it before returning it.

Appreciate any help.