Hi all,

I am trying to return mulitple sets of data from an Oracle table each of which exceed VARCHAR2(4000).

I wish to do this in one call to a stored procedure as the intention is to assign the returned data to a messages body parts so that I can send them as multiple seperate attachments to an email (using the SMTP adapter).

My oracle stored procedure and returned data types are as follows:

    PROCEDURE get_job(

        po_job_id OUT jobs.job_id%TYPE, po_endpoint OUT data.endpoint%TYPE,

        po_job_type OUT job_types.job_type_id%TYPE, po_data OUT rec_data_table);

    TYPE rec_data_table IS TABLE OF rec_data

       INDEX BY BINARY_INTEGER;

    TYPE rec_data IS RECORD (

        data CLOB

        --data VARCHAR2(4000));

If I do restrict the data to VARCHAR2(4000), as shown commented out in the rec_data RECORD above everything works fine.

However, to return anything larger I believe would require me to return a CLOB (even though I would probably only be returning data of a few MBytes not GBytes).

Having tried this I get the following error:

"The adapter "WCF-Custom" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-03113: end-of-file on communication channel

Process ID: 499868

Session ID: 201 Serial number: 33735 ---> Oracle.DataAccess.Client.OracleException: ORA-03113: end-of-file on communication channel

Process ID: 499868

Session ID: 201 Serial number: 33735"

I am using Oracle 11gR2, BizTalk 2010 and the WCF adapter with oracle bindings.

Researching this I find conflicting information:

• "LOB types: The WCF-based Oracle DB adapter provides rich support for BLOB, CLOB and BFILE types in tables and stored procedures/functions. The adapter also exposes special operations for streaming BLOB/CLOB/BFILE data in the WCF service model interface." Source: http://blogs.msdn.com/b/adapters/archive/2007/10/29/biztalk-oracle-adapter-vs-wcf-based-oracle-db-adapter.aspx
• Due to the limitation of associative arrays, PL/SQL tables or PL/SQL tables of records that contain any of the following data types are not supported in the Oracle Database adapter:
1. BFILE
2. BLOB
3. CLOB . Source: http://msdn.microsoft.com/en-us/library/dd788520.aspx
1. Also, this post (http://social.msdn.microsoft.com/Forums/en-US/303cc67c-5e01-4ecb-ba5c-184a1d73a7f7/biztalk-wcf-adpater-oracle-store-procedure-returning-clob?forum=biztalkediandas2) seems to indicate it is possible but there is no detail so not sure if it matches my requirements.

I have also had a quick look at the Operate_LOB sample but not sure if this will provide me with the solution I am after as I really wanted to return an array of CLOBS (or similar).

I have seen various other posts but nothing that definitively answers my questions and from my tests I would say that I cant easily return largish amounts of data (a few MBs) in a single element from Oracle . unless of course I am missing something.

Can anyone give me a definitive answer as to whether I can do what I have set out above if not I will turn my attention to a different solution.

Many Thanks for any help/advice you can provide,

Regards,

Dave

From your description, it can hardly give precise troubleshooting for this problem, you can consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx 

Hi Qiangs,

Thank you for the reply.

I think initially my question is about capability.

Is it possible to return a table or ref cursor of records which contian CLOBs to Biztalk using the WCF-Custom Adapter with oracle bindings.

As stated above there seems to be contradictory or confusing evidence ... so once I have a definitive answer to this I can decide whether to persue alternative solutions.

Regards,

Dave

Hi all,

I now have a solution to my issue ... please feel free to contact me directly if you have a similar requirement.

Regards,

Dave

• Marked as answer by daveyoggy Monday, April 07, 2014 8:38 AM

Hi Dave

Could you share the solution you had? I have a similar issue.

Regards

Jerome