tostinni Posted December 13, 2006 Share Posted December 13, 2006 Hi everybody,I recently discover the XMLTYPE Datatype in Oracle and found it very handy to manage XML document I have to deal with.So I built my little app to analyze and store them, but I found a problem dealing with big documents.As you may know, Oracle have a big drawback when inserting string larger than 4000 characters. To overcome this limitation, we need to use bind variables.So I looked for exemples over the web and couldn't find anything which will satisfy me.Regarding DataTypes available in ADO (MDAC 2.8), I found nothing approching Oracle's Clob nor XMLTYPE. In W3Schools ADO tutorial, it says that adLongVarChar seems to fit Clob.After many tests, I wasn't able to bind a string to insert it in an XMLTYPE column, nor in a CLOB :)It seems that MDAC/ADO doesn't support such DataTypes which is a very annoying problem.So I ended doing the following SQL = "INSERT INTO long_tab (my_long) values ( ? )"cmd.CommandText = SQLcmd.Parameters.Append cmd.CreateParameter("XML", adLongVarChar, adParamInput, 20000, xmlResponse)cmd.ExecuteSQL = "INSERT INTO clob_tab (my_clob) select to_lob(my_long) from long_tab "cmd.CommandText = SQLcmd.ExecuteSQL = "INSERT INTO XML_TAB (my_XML) select xmltype(my_clob) from clob_tab "cmd.CommandText = SQLcmd.Execute Each X_TAB is a table with one column of X DataType (LONG_TAB -> LONG column )So what's done here, is that first I insert a binded variable into a Long column, which works fine using "adLongVarChar" from ADO, and then I made an insert converting this LONG into a CLOB and end by converting this CLOB into an XMLTYPE column.It seems that doing this in one query doesn't work. INSERT INTO xml_tab (my_xml) SELECT XMLTYPE(TO_LOB(?)) FROM DUAL Note: Oracle can only use TO_LOB function in the SELECT part of an INSERT...Well, maybe someone had struggle with this...PS: I'm already looking for another solution using OO4O.PPS: I successfuly made this with VB.NET, but I need to work with VB6 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now