• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2191
  • Last Modified:

Update column SYS.XMLTYPE more 64kb. Using CSHARP

Hi,

I need a update one column XmlType in oracle table more than 64kb. Using CSHARP.

Example:

string sql = @" UPDATE TbParceiro
                            SET configuracao = :configuracao
                            WHERE parceiro = :parceiro ";

            DbCommand comm = Database.GetSqlStringCommand(sql);
            comm.Parameters.Add(new OracleParameter("parceiro", parceiro));
            comm.Parameters.Add(new OracleParameter("configuracao", configuracao.ToString()));

            ExecuteNonQuery(comm);

its. throw exception "ORA-01461: can bind a LONG value only for insert into a LONG column"
---
My Column configuracao  is XMLTYPE
My COlumn parceiro  is varchar(32)

2010-08-25 15:42:10.3220 Trace [5132] - LOG QUERY

 UPDATE TbParceiro
                            SET configuracao = :configuracao
                            WHERE parceiro = :parceiro

                                parceiro = mapfrews
                            configuracao = content of <configuracao.txt> attachment.

Question. How to insert large data ( > 64kb) in column type xmltype ??
Thank you
configuracao.txt
0
jefersonsv
Asked:
jefersonsv
  • 5
  • 4
  • 4
1 Solution
 
sdstuberCommented:
Sorry, it's not a c# limitation,  it's an XMLTYPE limit in oracle that isn't resolved until 11g
0
 
slightwv (䄆 Netminder) Commented:
What data access provider are you using? I'm not familiar with the calls like "DbCommand".

It might be a limitation of the data provider. I suggest you use Oracle's data provider, ODP.Net.

http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

>>it's an XMLTYPE limit

That is a single node wiht > 64K of text. The XML provided has no large nodes so I don't think that's the issue.

0
 
sdstuberCommented:
I guess I wasn't paying attention well enough.

I assumed the file was a smaller example of the problem data, but if that's not the case then yes, the file should be able to be loaded.

However I see another problem

You can't assign a string to an xmltype, you must convert the string to XMLTYPE to do the update


  configuracao = content of <configuracao.txt> attachment.


this would need to be     configuracao = XMLTYPE(  content of <configuracao.txt> attachment )

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sdstuberCommented:
also note

XMLTYPE(  content of <configuracao.txt> attachment )

the "content" must be of type CLOB if it's bigger than 4000 characters

a really big string literal won't work
0
 
jefersonsvAuthor Commented:
I alter column type to CLOB because my ORACLE is 10G r2 .

The my source code is ok now! with a big string in column
0
 
slightwv (䄆 Netminder) Commented:
@jefersonsv,

Can I ask why you selected that response as the answer since I don't believe it is correct?
0
 
jefersonsvAuthor Commented:
I selected this answer because the column XmlType not accept a large size of text, changing the column type to CLOB he accepted normally.

However the documentation says that the Oracle XMLType supports up to 4GB of data means that it has a problem in my version, which is not being accepted.

I did not do tests with this column in Oracle 11g, but possibly the bug is fixed as mentioned in first answer.

Do you agree?
0
 
slightwv (䄆 Netminder) Commented:
>>because the column XmlType not accept a large size of text

I pointed out the 64K limit 'bug' does not apply to your problem in http:#a33530833.  the bug referenced in the answer you selcted pertains to a single text node containing more then 64K of text in the single node.  

It does not apply to xml docs > 64K.

I've loaded XML docs into Oracle with VB.Net larger than 64K for years.

FYI:  XMLType is actually stored as a CLOB behind the scenes.



0
 
sdstuberCommented:
it seems to me that http:#33531100 would have been more appropriate to accept given the CLOB type was your problem
0
 
jefersonsvAuthor Commented:
>>because the column XmlType not accept a large size of text
Its not valid! The type XmlType support 4GB

See: http://ss64.com/ora/syntax-datatypes.html

>> FYI:  XMLType is actually stored as a CLOB behind the scenes.
The column XmlType use clob in behind. Its really true...
See this:
http://msdn.microsoft.com/en-us/library/ms971488.aspx#advoracledataac_topic12
http://msdn.microsoft.com/pt-br/library/ms971488.aspx#XSLTsection135121120120

but. i can execute this code:

string sql = @" UPDATE TbParceiro
                            SET configuracao = :configuracao
                            WHERE parceiro = :parceiro ";
            DbCommand comm = Database.GetSqlStringCommand(sql);
            comm.Parameters.Add(new OracleParameter("parceiro", parceiro));
            comm.Parameters.Add(new OracleParameter("configuracao", configuracao.ToString()));

            ExecuteNonQuery(comm);

with column <configuracao> CLOB
and the same source not run with column <configuracao> XMLTYPE

string sql = @" UPDATE TbParceiro
                            SET configuracao = XMLTYPE(:configuracao)
                            WHERE parceiro = :parceiro ";
            DbCommand comm = Database.GetSqlStringCommand(sql);
            comm.Parameters.Add(new OracleParameter("parceiro", parceiro));
            comm.Parameters.Add(new OracleParameter("configuracao", configuracao.ToString()));

its code not run with column <configuracao> XMLTYPE

I use Enterprise Library 5 to manage my connection.
0
 
slightwv (䄆 Netminder) Commented:
>>The column XmlType use clob in behind. Its really true...

I need to correct myself.  sdstuber has pointed out that in "can" be stored as Object Relational and in newer releases as Binary:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2130736


>>its code not run with column <configuracao> XMLTYPE

I suspect this is because of your data provider not knowing how to deal with that specific data type.
0
 
jefersonsvAuthor Commented:
@slightwv

if you can UPDATE a large text into column XmlType with Oracle 10g r2 using C#, enterprise library 5, provider: System.Data.OracleClient

I open a new question, and accept you solution... because i need use a function "xpath" of XmlType and in column Clob xpath its no possible.

Thank you very much to all ..... @slightwv @sdstuber
0
 
slightwv (䄆 Netminder) Commented:
>>enterprise library 5

I don't know anything about this. I see it's some product from Microsoft and it appears it has it's own Data Access Components.

I can provide an example using ODP.Net but that's it. It's all I use. That is why I asked about your data provider in http:#a33530833. I didn't recognize your DB calls.

I seriously doubt any ODP example I can provide will work for you.

>>and in column Clob xpath its no possible
You can always CAST it then use XPATH calls. That might work for your data provider.

select xmltype(myCLOBcolumn) from table;
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now