Subject: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)
Subject: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)