How to save excel file into oracle table

[akashchavan]

I need to save excel file data directely into oracle database by using XML string but getting error as

ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.
Error at Line: 50 Column: 31
Please Help me ...

[r035198x]

I'm assuming here that your column is already NOT varchar2 because that has a limitation on 4000 characters. If it is CLOB which can take 4gig then you still won't be able to send more than 4000 characters at a time. You should break it up into an insert followed by updates that append to the column until all the characters are written. Put that logic in a DB utility class.

[akashchavan]

Is der any option for CLOB or any other method to do the same in c#. Thanks r035198x for quick response.

[r035198x]

You can try with

using (OracleCommand cmd = new OracleCommand())
{
//setup command connection
cmd.CommandType = CommandType.Text;
cmd.CommandText = " insert into yourTable  values (:someParameter) ";
OracleParameter oracleParameterClob = new OracleParameter();
oracleParameterClob.OracleType = OracleType.Clob;
oracleParameterClob.Value = "Your long text here";
oracleParameterClob.ParameterName = "someParameterName";
cmd.Parameters.Add(oracleParameterClob);
cmd.ExecuteNonQuery();
// ...

[akashchavan]

No its not working ... . Suggest Something .. other ..

[r035198x]

What does it's not working mean? Does it not compile? Do you get an exception? Put some effort into asking the question.

[akashchavan]

Thnks Got It Finally ... But Next Part Need to See.. it extract values

[akashchavan]

Got the same error .... i.e.
ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.
Error at Line: 50 Column: 31

[Rabbit]

It would help to see your code.

[r035198x]

Read my first reply in full again.