Solved

Update column SYS.XMLTYPE more 64kb. Using CSHARP

Posted on 2010-08-25
13
2,089 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
  • 5
  • 4
  • 4
13 Comments
 
LVL 73

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 76

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 73

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
 
LVL 73

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 76

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 76

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 73

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 76

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 76

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now