Solved

Update column SYS.XMLTYPE more 64kb. Using CSHARP

Posted on 2010-08-25
13
2,130 Views
Last Modified: 2013-12-19
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
Comment
Question by:jefersonsv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 33530666
Sorry, it's not a c# limitation,  it's an XMLTYPE limit in oracle that isn't resolved until 11g
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33530833
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 33530899
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 74

Expert Comment

by:sdstuber
ID: 33531100
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
 

Author Closing Comment

by:jefersonsv
ID: 33532432
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33532451
@jefersonsv,

Can I ask why you selected that response as the answer since I don't believe it is correct?
0
 

Author Comment

by:jefersonsv
ID: 33532797
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33532880
>>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
 
LVL 74

Expert Comment

by:sdstuber
ID: 33533329
it seems to me that http:#33531100 would have been more appropriate to accept given the CLOB type was your problem
0
 

Author Comment

by:jefersonsv
ID: 33533897
>>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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33533937
>>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
 

Author Comment

by:jefersonsv
ID: 33534037
@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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33534211
>>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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question