Insert into CLOB Column in Oracle using JDBC Component

[garyssidhu <ga...@yahoo.com>]

Hi , I am newbie to Camel's JDBC Component. I need to insert data into clob
column of Oracle 11g Table using Camel's JDBC Component.  

My route looks like this

from("direct:requestwriter")
.to("bean:requestWriterBean")
.to("jdbc:myDataSourceInWLS10Jndi");

In RequestWriterBean, I am creating insert statement

insert into testCLOBTable values ("1",largexml). 

At this point giving an Oracle error that string is large. largexml size is
> 4K.

I have tried seaching for an example of using CLOB in JDBC Component but I
could not find. 

I need help urgently!!!!!!!!!!
		

--
View this message in context: http://camel.465427.n5.nabble.com/Insert-into-CLOB-Column-in-Oracle-using-JDBC-Component-tp4667458p4667458.html
Sent from the Camel - Users mailing list archive at Nabble.com.

Re: Insert into CLOB Column in Oracle using JDBC Component

[Christian Müller <ch...@gmail.com>]

First, you should get it working without Camel (writing a BLOB into the
database) if you have problems. I think the instructions in [1] will help
you. I think you have to do develop something similar like this one:

String cmd = "CREATE TABLE my_blob_table (x VARCHAR2 (30), c BLOB)";
stmt.execute (cmd);

stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");

cmd = "SELECT * FROM my_blob_table WHERE X='row1' FOR UPDATE";
ResultSet rset = stmt.executeQuery(cmd);
rset.next();
BLOB blob = ((OracleResultSet)rset).getBLOB(2);

File binaryFile = new File("john.gif");
FileInputStream instream = new FileInputStream(binaryFile);
OutputStream outstream = blob.setBinaryStream(1L);
int size = blob.getBufferSize();
byte[] buffer = new byte[size];

int length = -1;
while ((length = instream.read(buffer)) != -1)
   outstream.write(buffer, 0, length);
instream.close();
outstream.close();
conn.commit();


I think the easiest solution for your requirement is to implement a bean
(DAO style) which may use the JDBCTemplate from Spring and implement the
code by yourself. As you can see, handling BLOB and CLOB is much more
difficult than the other datatypes.

[1]
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oralob.htm#i1058035

Best,
Christian

Re: Insert into CLOB Column in Oracle using JDBC Component

[garyssidhu <ga...@yahoo.com>]

David, I am inserting string size greater than 4000, so column is CLOB

Cheers

--
View this message in context: http://camel.465427.n5.nabble.com/Insert-into-CLOB-Column-in-Oracle-using-JDBC-Component-tp4667458p4673824.html
Sent from the Camel - Users mailing list archive at Nabble.com.

Re: Insert into CLOB Column in Oracle using JDBC Component

[David Karlsen <da...@gmail.com>]

What type is the column declared as? AFAIK it should be text.
Den 4. aug. 2011 20:38 skrev "garyssidhu" <ga...@yahoo.com> følgende:
> Hi , I am newbie to Camel's JDBC Component. I need to insert data into
clob
> column of Oracle 11g Table using Camel's JDBC Component.
>
> My route looks like this
>
> from("direct:requestwriter")
> .to("bean:requestWriterBean")
> .to("jdbc:myDataSourceInWLS10Jndi");
>
> In RequestWriterBean, I am creating insert statement
>
> insert into testCLOBTable values ("1",largexml).
>
> At this point giving an Oracle error that string is large. largexml size
is
>> 4K.
>
> I have tried seaching for an example of using CLOB in JDBC Component but I
> could not find.
>
> I need help urgently!!!!!!!!!!
>
>
> --
> View this message in context:
http://camel.465427.n5.nabble.com/Insert-into-CLOB-Column-in-Oracle-using-JDBC-Component-tp4667458p4667458.html
> Sent from the Camel - Users mailing list archive at Nabble.com.

Re: Insert into CLOB Column in Oracle using JDBC Component

[garyssidhu <ga...@yahoo.com>]

Christian , 

I am looking for an example of inserting into Oracle's clob column. If you
could pointer to an example that would be a great help!

I am using Camel 2.8.0, Oracle 11g , JDK 6

Cheers
Gary


--
View this message in context: http://camel.465427.n5.nabble.com/Insert-into-CLOB-Column-in-Oracle-using-JDBC-Component-tp4667458p4668187.html
Sent from the Camel - Users mailing list archive at Nabble.com.

Re: Insert into CLOB Column in Oracle using JDBC Component

[Christian Müller <ch...@gmail.com>]

Hello!

Welcome in the Camel community.  First you should study how you can get help
[1] (which version of Camel do you use, ...).
The stack trace would also be helpful for us to analyze the issue.

What do you get, when do you run this code (This is what Camel does)?
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("insert into testCLOBTable values ("1",largexml)");

Did you try to set the 'largexml' as header option? And what is large for
you?

[1] http://camel.apache.org/how-can-i-get-help.html

Best,
Christian