XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Michael ReitsmaSubject: fn:substring in XQuery to Oracle db gives LONG error
Author: Michael Reitsma
Date: 22 Nov 2010 08:37 AM
Hi,

I created an XQuery that parses an xml file and stores the content as rows in a Oracle database.
Since there is one node in it that has a maximum size of 21k i used the fn:substring(AString,1, 4000) function to trim the string before inserting it into the database.
This way i can suffice with a Varchar2 column instead of a CLOB(or LONG).
The XQuery works and inserts all of the data, and the specified column is trimmed at 4000chars when the column is set CLOB.
However when i set the column to Varchar2(4000 char) it errors telling me it expects a LONG column.

Why is that ? My expectation is that since i trim the data at 4000char the column can be set to varchar2(4000char).
Instead it only works when the column is set to CLOB.

The error message:
-----------------------------------
javax.xml.xquery.XQException: [DataDirect][XQuery]Error during SQL execution: ORA-01461: can bind a LONG value only for insert into a LONG column


at com.ddtek.xquery.xqj.Util.createXQException(Util.java:243)

at com.ddtek.xquery.xqj.DDXQPreparedExpression.executeQuery(DDXQPreparedExpression.java:73)

Caused by: com.ddtek.xquery.util.XQueryException: [EX5300][DataDirect][XQuery]Error during SQL execution: ORA-01461: can bind a LONG value only for insert into a LONG column


at com.ddtek.xquery.extractor.util.SqlUtil.createXQueryException(SqlUtil.java:216)

at com.ddtek.xquery.extractor.util.SqlUtil.createXQueryException(SqlUtil.java:197)

at com.ddtek.xquery.extractor.resultset.JdbcPreparedStatement.executeBatch(JdbcStatement.java:537)

at com.ddtek.xquery.extractor.resultset.DirectUpdateSQL.commitExecutes(UpdateSQL.java:316)

at com.ddtek.xquery.extractor.ExtractorPreparedStatement.commitExecutes(ExtractorPreparedStatement.java:181)

at com.ddtek.xquery.extractor.ExtractorUpdateBatch.commitExecutes(ExtractorUpdateBatch.java:147)

at com.ddtek.xquery.extractor.ExtractorUpdateBatch.addExecute(ExtractorUpdateBatch.java:136)

at com.ddtek.xquery.extractor.ExtractorPreparedStatement.executeUpdateQueryUpdateBatch(ExtractorPreparedStatement.java:155)

at com.ddtek.xquery.extractor.ExtractorPreparedStatement.executeQuery(ExtractorPreparedStatement.java:105)

at com.ddtek.xquery.mediator.plan.SourcePhysOpImpl.executeStatement(SourcePhysOpImpl.java:685)

at com.ddtek.xquery.mediator.plan.SourcePhysOpImpl.execute(SourcePhysOpImpl.java:663)

at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.generateNextXTuple(JoinPhysOpImpl.java:195)

at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)

at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:73)

at com.ddtek.xquery.mediator.plan.ReturnPhysOpImpl.getNextSequenceUngrouper(ReturnPhysOpImpl.java:106)

at com.ddtek.xquery.mediator.plan.ReturnPhysOpImpl.next(ReturnPhysOpImpl.java:63)

at com.ddtek.xquery.mediator.plan.SequencePhysOpImpl.next(SequencePhysOpImpl.java:51)

at com.ddtek.xquery.mediator.xtuple.LetSequence.getNextSubSequence(LetSequence.java:136)

at com.ddtek.xquery.mediator.xtuple.LetSequenceUngrouperImpl.hasNext(LetSequence.java:229)

at com.ddtek.xquery.mediator.plan.ReturnPhysOpImpl.next(ReturnPhysOpImpl.java:70)

at com.ddtek.xquery.mediator.plan.ExecutionPlanImpl.next(ExecutionPlanImpl.java:1176)

at com.ddtek.xquery.mediator.plan.DefaultPlanIterator.next(DefaultPlanIterator.java:143)

at com.ddtek.xquery.mediator.plan.ExecutionPlanImpl.fetchAllForUpdate(ExecutionPlanImpl.java:1737)

at com.ddtek.xquery.mediator.plan.ExecutionPlanImpl.executeSqlUpdateQuery(ExecutionPlanImpl.java:301)

at com.ddtek.xquery.mediator.plan.ExecutionPlanImpl.executeQuery(ExecutionPlanImpl.java:205)

at com.ddtek.xquery.mediator.plan.ExecutionContextImpl.executeQuery(ExecutionContextImpl.java:325)

at com.ddtek.xquery.xqj.DDXQAbstractExpression.startExecution(DDXQAbstractExpression.java:564)

at com.ddtek.xquery.xqj.DDXQPreparedExpression.executeQuery(DDXQPreparedExpression.java:68)

Caused by: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column


at com.ddtek.xquery.jdbc.oracle.OracleImplStatement.generateBatchUpdateException(Unknown Source)

at com.ddtek.xquery.jdbc.oracle.OracleImplStatement.appendParameterdata(Unknown Source)

at com.ddtek.xquery.jdbc.oracle.OracleImplStatement.execute(Unknown Source)

at com.ddtek.xquery.jdbc.oracle.OracleImplStatement.executeBatch(Unknown Source)

at com.ddtek.xquery.jdbc.oraclebase.BaseStatement.commonExecute(Unknown Source)

at com.ddtek.xquery.jdbc.oraclebase.BasePreparedStatement.executeBatch(Unknown Source)

at com.ddtek.xquery.extractor.resultset.JdbcPreparedStatement.executeBatch(JdbcStatement.java:524)

... 25 more

Postnext
Ivan PedruzziSubject: fn:substring in XQuery to Oracle db gives LONG error
Author: Ivan Pedruzzi
Date: 23 Nov 2010 12:53 AM
Michael,

It looks like that Stylus is still seeing the old table definition.

Right click on the database connection in File Explorer then click Edit then click OK. This should ivalidate the connection in the pool.

Hope this helps
Ivan




Postnext
Michael ReitsmaSubject: fn:substring in XQuery to Oracle db gives LONG error
Author: Michael Reitsma
Date: 23 Nov 2010 11:51 AM
>Michael,
>
>It looks like that Stylus is
>still seeing the old table
>definition.
>
>Right click on the database
>connection in File Explorer
>then click Edit then click OK.
>This should ivalidate the
>connection in the pool.
>
>Hope this helps
>Ivan
>
>
>
>

As soon as my PC is up again i'll check it.
I did notice though that using refresh at the File Explorer section did not show any new tables which had been created since i last used File Explorer to connect to the db.

In the meantime have another question related to this: is there any option as to see what values are being inserted or what node is processed ?
My file is 10Million rows big and IF the problem is in the data then any help is appreciated.
Since SS does a rollback when errors occur i can not see in the db what stuff gets processed (so i can see where it halted in the source)

Posttop
Ivan PedruzziSubject: fn:substring in XQuery to Oracle db gives LONG error
Author: Ivan Pedruzzi
Date: 26 Nov 2010 02:37 AM


Updating queries run in a single transaction, if an error occurs the all INSERT is rolled back.



Ivan

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.