0 Replies Latest reply: Oct 14, 2013 2:56 PM by user464965 RSS

    JMS to Oracle DB - RAW DataType


      I have a JMS queue that I'm reading using a JMS VAM extract, and I'm successfully generating trail files and then using a replicat process to successfully write the changes contained in those trail files to a target Oracle DB schema.  I am able to perform inserts, updates, and deletes in the target DB based on the info in the JMS queue for all tables in my target schema except for one.  Inserts come across for the table in question, but the updates and deletes for the table in question do not happen.  I don't get any errors, but the updates and deletes for the one target table just don't happen.


      Upon further investigation, I can now understand why the updates and deletes aren't happening for the table in question, but I don't know how to solve it!


      The table I'm having a problem with has a primary key in the Oracle DB that is a RAW datatype.  The data being written to the target raw datatype upon insert does match (when read back for a select or update or delete) the hex string value that I'm providing in the JMS queue to represent the field.


      I've even gone so far as to create two identical test tables that have a RAW datatype as the PK.  I have a User Exit extract running against the source database table, and from that, I generate the JMS message in the JMS queue that my JMS VAM extract is using to generate the trail files that then have a replicat process used to run the changes into the target table.


      The data for the RAW datatype field is correct and consistent from the source trail file, to the User Exit operation debug output, to the JMS message, all the way to the target trail file.  But once the record is inserted into the target table, the value that I see at any of the prior points described above, nor does it match the hex representation of the raw datatype as displayed using SQLDeveloper when I view the data in the raw field of the source table.


      By default, the value passing through the trail files, User Exit debug, and JMS message is represented, it appears, as a B64 encoded string ... eg:




      ... but using SQLDeveloper to read the source table/column in question (giving the HEX of the bytes) gives a hex string ... eg:




      ... I've tried doing a hex conversion (in Java) on the byte array in my source User Exit ... and it even generates the exact hex string I'm seeing when I use SQLDeveloper to look at the source ... and this hex string representation will carry all the way through to the trail file generated off of the JMS queue (I can use logdump to visualize), yet what gets written from that trail file to the target table via my replicat process, when I view the target table using SQLDeveloper, does not match this hex string that I'm seeing throughout all of the earlier process!!


      Is there something I need to set in the params for my replicat process?? I was thinking maybe a hextobin, but I'm having no luck with that ...


      Any help is much appreciated!