Exception while using CLOB type in DB stored procedure component

[dexterschumi007]

Hi,

I am trying to fetch results from a database through DB BC component using the stored procedure. The procedure is attached to this mail. proc.sql

The procedure accepts a int as input and 5 elements in return including a cursor. The curson contains a CLOB data type.

Whenever i am trying to executing the DB BC component i am getting the error "Caused by: java.lang.ClassCastException: oracle.sql.CLOB cannot be cast to [C" full log contents attached.error1.txt.

I am using "ojdbc6.jar" for setting up the DB connection.

Following are my server details.

Product Version: NetBeans IDE 6.5.1 (Build 200903161801)
Java: 1.6.0_06; Java HotSpot(TM) Client VM 10.0-b22
GlassFish V2

[dougal]

Some javascript/style in this post has been disabled (why?)

What version of the Database BC are you using?

dexterschumi007 wrote:

Hi,

I am trying to fetch results from a database through DB BC component using
the stored procedure. The procedure is attached to this mail. 
http://n2.nabble.com/file/n3186950/proc.sql proc.sql 

The procedure accepts a int as input and 5 elements in return including a
cursor. The curson contains a CLOB data type.

Whenever i am trying to executing the DB BC component i am getting the error
"Caused by: java.lang.ClassCastException: oracle.sql.CLOB cannot be cast to
[C" full log contents attached.
http://n2.nabble.com/file/n3186950/error1.txt error1.txt .

I am using "ojdbc6.jar" for setting up the DB connection.

Following are my server details.

Product Version: NetBeans IDE 6.5.1 (Build 200903161801)
Java: 1.6.0_06; Java HotSpot(TM) Client VM 10.0-b22
GlassFish V2


  

--------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email]

[Andy Knight]

In investigating this issue (because I know that there have been  
problems handling LOB data), I may have discovered a more fundamental  
issue when dealing with cursors.

Using GlassFish ESB v2.1 and Oracle 11g, I created a table thus:-

create table fast.lobtest (id integer not null primary key, lobdat  
clob);

I then created a procedure as follows:-

create procedure fast.getclobdata (p_cursor out sys_refcursor) is
begin
        open p_cursor for select * from fast.lobtest;
end;

I then created a WSDL for this procedure.

I noted the the generated XSD contains this:-

<xsd:element name="GETCLOBDATA_Response">
       <xsd:complexType>
         <xsd:sequence></xsd:sequence>
       </xsd:complexType>
     </xsd:element>

 From the BPEL Mapper's perspective, the output of an invocation of  
the procedure looks like this:-





I can not think of any way to use this 'part' to extract the return  
value(s) from the procedure invocation.

Perhaps I'm missing something


Andy Knight
Principal Engineer, Field Assist Support Team
Sun Microsystems, Inc.
Java House, Guillemont Park, Minley Road, Blackwater
Camberley, Surrey GU17 9QG United Kingdom
Home office: [phone]
Mobile: [phone]
Skype: aprknight
Email: [hidden email]
Blog: http://blogs.sun.com/andky


On 1 Jul 2009, at 04:52, dexterschumi007 wrote:

>
> Hi,
>
> I am trying to fetch results from a database through DB BC component  
> using
> the stored procedure. The procedure is attached to this mail.
> http://n2.nabble.com/file/n3186950/proc.sql proc.sql
>
> The procedure accepts a int as input and 5 elements in return  
> including a
> cursor. The curson contains a CLOB data type.
>
> Whenever i am trying to executing the DB BC component i am getting  
> the error
> "Caused by: java.lang.ClassCastException: oracle.sql.CLOB cannot be  
> cast to
> [C" full log contents attached.
> http://n2.nabble.com/file/n3186950/error1.txt error1.txt .
>
> I am using "ojdbc6.jar" for setting up the DB connection.
>
> Following are my server details.
>
> Product Version: NetBeans IDE 6.5.1 (Build 200903161801)
> Java: 1.6.0_06; Java HotSpot(TM) Client VM 10.0-b22
> GlassFish V2
>
>
> --
> View this message in context: http://n2.nabble.com/Exception-while-using-CLOB-type-in-DB-stored-procedure-component-tp3186950p3186950.html
> Sent from the OpenESB Users mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[dexterschumi007]

The details of the Glass fish server is mentioned below.

Build : 090627
Version : 2.6.0

Thanks for the reply

dougal wrote:

 


What version of the Database BC are you using?

dexterschumi007 wrote:

  Hi,

I am trying to fetch results from a database through DB BC component using
the stored procedure. The procedure is attached to this mail.
http://n2.nabble.com/file/n3186950/proc.sql  proc.sql

The procedure accepts a int as input and 5 elements in return including a
cursor. The curson contains a CLOB data type.

Whenever i am trying to executing the DB BC component i am getting the error
"Caused by: java.lang.ClassCastException: oracle.sql.CLOB cannot be cast to
[C" full log contents attached.
http://n2.nabble.com/file/n3186950/error1.txt  error1.txt .

I am using "ojdbc6.jar" for setting up the DB connection.

Following are my server details.

Product Version: NetBeans IDE 6.5.1 (Build 200903161801)
Java: 1.6.0_06; Java HotSpot(TM) Client VM 10.0-b22
GlassFish V2


 


--

 
   
     
       Dougal Fraser
SOA Architect
     
      Sun Microsystems, Inc.
New Zealand
Phone x69822/[phone]
Mobile [phone]
Email [e-mail]
     
   
 





---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[dexterschumi007]

Thanks Andy for the reply :-)

While creating a DB BC i have observed the following things.

When you are defining a procedure with input and outputs and with a cursor in the output, then the   cursor is not recognized as a result set.

When you define input and output but you do not use the input in the body of the procedure then the cursor IS recognized as a result set.

This is quite weird because i don't see any reason why should the DB BC should be looking inside whats happening in the body of the procedure,

As for the clob......this is quite weird.......when you get a clob through simple SELECT statement , there is no casting error !!. The CLOB is automatically casted to string as i am writing the output to a file........

Please provide insight ,what really must be the issue?

1. ojdbc driver?
2. Java version ..i am using 1.6
3. DB BC


Thanks & Regards.
Virendra P.Sawant

Andy Knight wrote:

In investigating this issue (because I know that there have been  
problems handling LOB data), I may have discovered a more fundamental  
issue when dealing with cursors.

Using GlassFish ESB v2.1 and Oracle 11g, I created a table thus:-

create table fast.lobtest (id integer not null primary key, lobdat  
clob);

I then created a procedure as follows:-

create procedure fast.getclobdata (p_cursor out sys_refcursor) is
begin
        open p_cursor for select * from fast.lobtest;
end;

I then created a WSDL for this procedure.

I noted the the generated XSD contains this:-

<xsd:element name="GETCLOBDATA_Response">
       <xsd:complexType>
         <xsd:sequence></xsd:sequence>
       </xsd:complexType>
     </xsd:element>

 From the BPEL Mapper's perspective, the output of an invocation of  
the procedure looks like this:-

 



I can not think of any way to use this 'part' to extract the return  
value(s) from the procedure invocation.

Perhaps I'm missing something


Andy Knight
Principal Engineer, Field Assist Support Team
Sun Microsystems, Inc.
Java House, Guillemont Park, Minley Road, Blackwater
Camberley, Surrey GU17 9QG United Kingdom
Home office: [phone]
Mobile: [phone]
Skype: aprknight
Email: [e-mail]
Blog: http://blogs.sun.com/andky


On 1 Jul 2009, at 04:52, dexterschumi007 wrote:

>
> Hi,
>
> I am trying to fetch results from a database through DB BC component  
> using
> the stored procedure. The procedure is attached to this mail.
> http://n2.nabble.com/file/n3186950/proc.sql proc.sql
>
> The procedure accepts a int as input and 5 elements in return  
> including a
> cursor. The curson contains a CLOB data type.
>
> Whenever i am trying to executing the DB BC component i am getting  
> the error
> "Caused by: java.lang.ClassCastException: oracle.sql.CLOB cannot be  
> cast to
> [C" full log contents attached.
> http://n2.nabble.com/file/n3186950/error1.txt error1.txt .
>
> I am using "ojdbc6.jar" for setting up the DB connection.
>
> Following are my server details.
>
> Product Version: NetBeans IDE 6.5.1 (Build 200903161801)
> Java: 1.6.0_06; Java HotSpot(TM) Client VM 10.0-b22
> GlassFish V2
>
>
> --
> View this message in context: http://n2.nabble.com/Exception-while-using-CLOB-type-in-DB-stored-procedure-component-tp3186950p3186950.html
> Sent from the OpenESB Users mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [e-mail]
> For additional commands, e-mail: [e-mail]
>



---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Andy Knight]

On 9 Jul 2009, at 04:48, dexterschumi007 wrote:

>
> Thanks Andy for the reply :-)
>
> While creating a DB BC i have observed the following things.
>

> When you are defining a procedure with input and outputs and with a  
> cursor
> in the output, then the   cursor is not recognized as a result set.
>
That's what I have observed. You need to raise this as a bug.

> When you define input and output but you do not use the input in the  
> body of
> the procedure then the cursor IS recognized as a result set.
>
> This is quite weird because i don't see any reason why should the DB  
> BC
> should be looking inside whats happening in the body of the procedure,
>
The developers will need to answer this one.

> As for the clob......this is quite weird.......when you get a clob  
> through
> simple SELECT statement , there is no casting error !!. The CLOB is
> automatically casted to string as i am writing the output to a  
> file........
>
The problem here is that there appears to be no way to iterate through  
the OUT CURSOR.

> Please provide insight ,what really must be the issue?
>
> 1. ojdbc driver?
> 2. Java version ..i am using 1.6
> 3. DB BC
>
>
Pavan? Can you help?

> Thanks & Regards.
> Virendra P.Sawant
>
>
> Andy Knight wrote:
>>
>> In investigating this issue (because I know that there have been
>> problems handling LOB data), I may have discovered a more fundamental
>> issue when dealing with cursors.
>>
>> Using GlassFish ESB v2.1 and Oracle 11g, I created a table thus:-
>>
>> create table fast.lobtest (id integer not null primary key, lobdat
>> clob);
>>
>> I then created a procedure as follows:-
>>
>> create procedure fast.getclobdata (p_cursor out sys_refcursor) is
>> begin
>> open p_cursor for select * from fast.lobtest;
>> end;
>>
>> I then created a WSDL for this procedure.
>>
>> I noted the the generated XSD contains this:-
>>
>> <xsd:element name="GETCLOBDATA_Response">
>>       <xsd:complexType>
>>         <xsd:sequence></xsd:sequence>
>>       </xsd:complexType>
>>     </xsd:element>
>>
>> From the BPEL Mapper's perspective, the output of an invocation of
>> the procedure looks like this:-
>>
>>
>>
>>
>>
>> I can not think of any way to use this 'part' to extract the return
>> value(s) from the procedure invocation.
>>
>> Perhaps I'm missing something
>>
>>
>> Andy Knight
>> Principal Engineer, Field Assist Support Team
>> Sun Microsystems, Inc.
>> Java House, Guillemont Park, Minley Road, Blackwater
>> Camberley, Surrey GU17 9QG United Kingdom
>> Home office: [phone]
>> Mobile: [phone]
>> Skype: aprknight
>> Email: [hidden email]
>> Blog: http://blogs.sun.com/andky
>>
>>
>> On 1 Jul 2009, at 04:52, dexterschumi007 wrote:
>>
>>>
>>> Hi,
>>>
>>> I am trying to fetch results from a database through DB BC component
>>> using
>>> the stored procedure. The procedure is attached to this mail.
>>> http://n2.nabble.com/file/n3186950/proc.sql proc.sql
>>>
>>> The procedure accepts a int as input and 5 elements in return
>>> including a
>>> cursor. The curson contains a CLOB data type.
>>>
>>> Whenever i am trying to executing the DB BC component i am getting
>>> the error
>>> "Caused by: java.lang.ClassCastException: oracle.sql.CLOB cannot be
>>> cast to
>>> [C" full log contents attached.
>>> http://n2.nabble.com/file/n3186950/error1.txt error1.txt .
>>>
>>> I am using "ojdbc6.jar" for setting up the DB connection.
>>>
>>> Following are my server details.
>>>
>>> Product Version: NetBeans IDE 6.5.1 (Build 200903161801)
>>> Java: 1.6.0_06; Java HotSpot(TM) Client VM 10.0-b22
>>> GlassFish V2
>>>
>>>
>>> --
>>> View this message in context:
>>> http://n2.nabble.com/Exception-while-using-CLOB-type-in-DB-stored-procedure-component-tp3186950p3186950.html
>>> Sent from the OpenESB Users mailing list archive at Nabble.com.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
> --
> View this message in context: http://n2.nabble.com/Exception-while-using-CLOB-type-in-DB-stored-procedure-component-tp3186950p3229434.html
> Sent from the OpenESB Users mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

Andy Knight

Principal Engineer,

Sun Microsystems, Inc.

Java House, Guillemont Park, Minley Road, Blackwater

Camberley, Surrey GU17 9QG United Kingdom

Home office: [phone]

Mobile: [phone]

Skype: aprknight

Email: [hidden email]

Blog: http://blogs.sun.com/andky





---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]