7 Replies Latest reply on Jun 8, 2010 5:06 AM by radek.r

    Oracle BLOB value to a LC variable

    radek.r Level 1



      Is it possible in LC ES2 to coerce oracle blob value to some livecycle variable ?


      When mapping sql query result (column of type BLOB) to a LC variable (of type Document), we receive:


      com.adobe.workflow.datatype.InvalidCoercionException: Cannot coerce object: oracle.sql.BLOB@27e7d6 of type: oracle.sql.BLOB to type: com.adobe.idp.Document


      We have tried mapping to string, binary, Document (, we even desperately hardcoded sql.oracle.BLOB to process XML), but none of this works.


      Is it possible to solve this other way, then following ?





        • 1. Re: Oracle BLOB value to a LC variable
          Jasmin Charbonneau Level 4

          Have you tried to use the data type "Object"?



          1 person found this helpful
          • 2. Re: Oracle BLOB value to a LC variable
            radek.r Level 1

            Thanks for reply Jasmin.


            It is really possible to store BLOB column from query result to Object variable. I have tried to assign it then to Document variable via SetValue, but this produces:


            Caused by: ALC-DSC-119-000: com.adobe.idp.dsc.util.InvalidCoercionException: Cannot coerce object: oracle.sql.BLOB@bbdde2 of type: oracle.sql.BLOB to type: interface org.w3c.dom.Document


            Would You have some hint yet for this?


            The goal is to read pdf form stored in BLOB Oracle field, than use it (as a Document) with Apply Usage Rights to Reader extend the form.

            • 3. Re: Oracle BLOB value to a LC variable
              radek.r Level 1

              :-/ I still don't like this way, but the execute script may be minimized to cast logic:

              Then we don't need to place jdbc logic to executeScript (and avoid to hardcode/pass here e.g. datasource ids).

              It is still required to map blob field to Object type in Query Single Row component.


              try {
                  java.sql.Blob documentBlob =  (java.sql.Blob) patExecContext.getProcessDataValue("/process_data/BLOB_FIELD_STORED_IN_OBJECT_VAR");
                  com.adobe.idp.Document document = new com.adobe.idp.Document(documentBlob.getBinaryStream());
                  patExecContext.setProcessDataValue("/process_data/@doc", document);


              } catch(Exception e) {
                  //do something, if desired
                  throw e;


              I still feel, that this should be possible to make somehow without exectue script ?

              • 4. Re: Oracle BLOB value to a LC variable
                Jasmin Charbonneau Level 4

                I just noticed something in your error:


                Caused by: ALC-DSC-119-000:com.adobe.idp.dsc.util.InvalidCoercionException: Cannot coerce object:oracle.sql.BLOB@bbdde2 of type: oracle.sql.BLOB to type: interfaceorg.w3c.dom.Document


                It can't cast the Blob into a org.w3c.dom.Document.


                The org.w3c.dom.Document is an xml datatype that's why it's not working. The LiveCycle document data type is com.adobe.idp.Document. It's a different object.


                Are you sure you're selecting the right data type?



                • 5. Re: Oracle BLOB value to a LC variable
                  radek.r Level 1

                  Sorry, You are right. I don't know, where the org.w3c.dom.Document came from.


                  But still, when I use com.adobe.idp.Document, I get:


                  com.adobe.workflow.datatype.InvalidCoercionException: Cannot coerce object: oracle.sql.BLOB@1045881 of type: oracle.sql.BLOB to type: com.adobe.idp.Document

                  • 6. Re: Oracle BLOB value to a LC variable
                    $Nith$ Level 4

                    My idea is to fetch the BLOB column data as UTF8 (base64) encoded string and then use getDocumentFromBase64 available in ES2.


                    I am not sure what syntax is to fetch from ORACLE.


                    I have used MySql database and the query is:


                    SELECT CONVERT(Blob_Column USING UTF8) as MyBlobDataAsBase64Str FROM myTableName;


                    Now using SetValue activity to look like:   myXMLvariable = getDocumentFromBase64(strVariableHoldingBase64Data)


                    This case works perfectly without any issues.



                    The problems is you should find out the appropriate syntax for ORACLE.


                    I was searching about CONVERT() & UNISTR() functions. But i'm unable to evaluate.


                    Try by yourself..



                    • 7. Re: Oracle BLOB value to a LC variable
                      radek.r Level 1

                      I think, this is the cleanest available solution $Nith$, very good idea.


                      I have even tried st. similar, but as an Oracle beinner, I have problem with the BLOB -> base64 string conversion. I am consulting this with db specialists and post solution, if I get one :-).