Solved

Update column SYS.XMLTYPE more 64kb. Using CSHARP

Posted on 2010-08-25
13
2,099 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 36
Two different visual studio versions 3 21
SQL query question 8 31
PL SQL Search Across Columns 4 17
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

929 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

14 Experts available now in Live!

Get 1:1 Help Now