i2b2 - v1.7.07 - ORA-01461: can bind a LONG value only for insert into a LONG column
Peter Beninato
Hello i2b2 community,
I just saw this in the jboss server log.
I am not sure, if there was a schema patch to make a column a LONG, and that was missed (either in QT_QUERY_INSTANCE, or DX?)?
Or how Pat_Num might be a LONG?
Does anyone have any information on how/why this is being thrown?
Thanks.
-PEter
09:15:13,639 INFO [stdout] (Thread-582) update octrii2b2data.QUERY_GLOBAL_TEMP set panel_count = -1 where octrii2b2data.QUERY_GLOBAL_TEMP.panel_count = 2 and exists ( select 1 from ( select /*+ index(f fact_cnpt_pat_enct_idx) */ f.patient_num
09:15:13,639 INFO [stdout] (Thread-582) from octrii2b2data.observation_fact f
09:15:13,639 INFO [stdout] (Thread-582) where
09:15:13,639 INFO [stdout] (Thread-582) f.concept_cd IN (select concept_cd from octrii2b2data.concept_dimension where concept_path LIKE '\i2b2\Demographics\Vital Status\Deceased\%')
09:15:13,639 INFO [stdout] (Thread-582) group by f.patient_num ) t where octrii2b2data.QUERY_GLOBAL_TEMP.patient_num = t.patient_num )
09:15:13,639 INFO [stdout] (Thread-582) <*>
09:15:13,639 INFO [stdout] (Thread-582) insert into octrii2b2data.DX ( patient_num ) select * from ( select distinct patient_num from octrii2b2data.QUERY_GLOBAL_TEMP where panel_count = 2 ) q
09:15:13,655 ERROR [edu.harvard.i2b2.crc.ejb.ExecRunnable] (Thread-582) Got an excpetion in ExecRunnable (RUN): PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE octrii2b2data.QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,END_DATE = ? ,STATUS_TYPE_ID = ?, MESSAGE = nvl(MESSAGE,'') || ? where query_instance_id = ? ]; SQL state [72000]; error code [1461]; ORA-01461: can bind a LONG value only for insert into a LONG column
; nested exception is java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
09:15:13,656 ERROR [stderr] (Thread-582) org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE octrii2b2data.QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,END_DATE = ? ,STATUS_TYPE_ID = ?, MESSAGE = nvl(MESSAGE,'') || ? where query_instance_id = ? ]; SQL state [72000]; error code [1461]; ORA-01461: can bind a LONG value only for insert into a LONG column
09:15:13,657 ERROR [stderr] (Thread-582) ; nested exception is java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
09:15:13,657 ERROR [stderr] (Thread-582)
09:15:13,657 ERROR [stderr] (Thread-582) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
09:15:13,657 ERROR [stderr] (Thread-582) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
09:15:13,657 ERROR [stderr] (Thread-582) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
09:15:13,657 ERROR [stderr] (Thread-582) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
09:15:13,658 ERROR [stderr] (Thread-582) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
09:15:13,658 ERROR [stderr] (Thread-582) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
09:15:13,658 ERROR [stderr] (Thread-582) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:858)
09:15:13,658 ERROR [stderr] (Thread-582) at edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.update(QueryInstanceSpringDao.java:208)
09:15:13,658 ERROR [stderr] (Thread-582) at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.setQueryInstanceStatus(QueryExecutorDao.java:380)
09:15:13,658 ERROR [stderr] (Thread-582) at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.executeSQL(QueryExecutorDao.java:297)
09:15:13,659 ERROR [stderr] (Thread-582) at edu.harvard.i2b2.crc.ejb.ExecRunnable.processQueryRequest(ExecRunnable.java:469)
09:15:13,659 ERROR [stderr] (Thread-582) at edu.harvard.i2b2.crc.ejb.ExecRunnable.run(ExecRunnable.java:184)
09:15:13,659 ERROR [stderr] (Thread-582) at java.lang.Thread.run(Thread.java:744)
09:15:13,659 ERROR [stderr] (Thread-582) Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
09:15:13,659 ERROR [stderr] (Thread-582)
09:15:13,659 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
09:15:13,659 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
09:15:13,660 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
09:15:13,661 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
09:15:13,661 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
09:15:13,661 ERROR [stderr] (Thread-582) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
09:15:13,661 ERROR [stderr] (Thread-582) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
09:15:13,661 ERROR [stderr] (Thread-582) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
09:15:13,661 ERROR [stderr] (Thread-582) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
_________________________________
Peter Beninato - OCTRI DW Developer
Oregon Health & Science University
_________________________________
The information contained in this EMAIL message is confidential and protected by law. The information is intended only for the person or business identified in the document. If you are not the intended recipient, a sharing or copying the information will result in a violation of the law. If you have received this EMAIL by mistake, please notify the sender of this EMAIL and copy the Office of Information Privacy & Security at oi...@ohsu.edu.
Please remember to cite the NCRR/NCATS-funded CTSA grant (UL1RR024140) in any publications resulting from OCTRI funding, support services or consultations, and to submit your publication to PubMed Central.
Hubert Hickman
Mendis, Michael E.
Peter,
Just to verify on the Oracle, is your QT_QUERY_INSTANCE the following:
CREATE TABLE QT_QUERY_INSTANCE (
QUERY_INSTANCE_ID NUMBER(5,0) PRIMARY KEY,
QUERY_MASTER_ID NUMBER(5,0),
USER_ID VARCHAR2(50) NOT NULL,
GROUP_ID VARCHAR2(50) NOT NULL,
BATCH_MODE VARCHAR2(50),
START_DATE DATE NOT NULL,
END_DATE DATE,
DELETE_FLAG VARCHAR2(3),
STATUS_TYPE_ID NUMBER(5,0),
MESSAGE CLOB,
CONSTRAINT QT_FK_QI_MID FOREIGN KEY (QUERY_MASTER_ID)
REFERENCES QT_QUERY_MASTER (QUERY_MASTER_ID),
CONSTRAINT QT_FK_QI_STID FOREIGN KEY (STATUS_TYPE_ID)
REFERENCES QT_QUERY_STATUS_TYPE (STATUS_TYPE_ID)
)
--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
i2b2-install-h...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
The information in this e-mail is intended only for the person to whom it is
addressed. If you believe this e-mail was sent to you in error and the e-mail
contains patient information, please contact the Partners Compliance HelpLine at
http://www.partners.org/complianceline . If the e-mail was sent to you in error
but does not contain patient information, please contact the sender and properly
dispose of the e-mail.
Phillips, Lori C.
I think you may be reading this incorrectly.
ORA-01461: can bind a LONG value only for insert into a LONG column
In other words, you tried to insert a LONG value into a column that is not a LONG datatype.
I’ve dug more and have seen this: If you try to push a very long text in a varchar2, you can end up with a
ORA-01461: can bind a LONG value only for insert into a LONG column
It would help to know what values are being inserted.
Lori
Mendis, Michael E.
Thanks lori, Peter do you know what query this one by the timestamp, I am wondering if lori is correct and that the message was to large
-mike
Peter Beninato
Hello,
I will look into Lori’s suggestion. Here is DDL, the column lengths are approximately the same. It is true that some of the varchar2 include “byte”, however the Message is a clob, so I’m not sure if that is the issue (it is not a varchar).
I will tell you it does concern me that query_instance_id and query_master_id are only Number(5). Does the code roll over the IDs? If so, won’t that eventually generate a unique constraint violation? Does that limit to only 100,000?
I will try to examine the exact query by timestamp, and log entries, and try to provide more information.
Thanks.
CREATE TABLE OCTRII2B2DATA.QT_QUERY_INSTANCE
(
QUERY_INSTANCE_ID NUMBER(5),
QUERY_MASTER_ID NUMBER(5),
USER_ID VARCHAR2(50 BYTE) NOT NULL,
GROUP_ID VARCHAR2(50 BYTE) NOT NULL,
BATCH_MODE VARCHAR2(50 BYTE),
START_DATE DATE NOT NULL,
END_DATE DATE,
DELETE_FLAG VARCHAR2(3 BYTE),
STATUS_TYPE_ID NUMBER(5),
MESSAGE CLOB
)
ALTER TABLE OCTRII2B2DATA.QT_QUERY_INSTANCE ADD (
CONSTRAINT QT_FK_QI_MID
FOREIGN KEY (QUERY_MASTER_ID)
REFERENCES OCTRII2B2DATA.QT_QUERY_MASTER (QUERY_MASTER_ID)
ENABLE VALIDATE,
CONSTRAINT QT_FK_QI_STID
FOREIGN KEY (STATUS_TYPE_ID)
REFERENCES OCTRII2B2DATA.QT_QUERY_STATUS_TYPE (STATUS_TYPE_ID)
ENABLE VALIDATE);
Peter Beninato
Hello,
Our logging in Prod. Is set to info, and the log maintenance in DEBUG grow large quickly.
I looked in query_master, query_instance, and query_result_instance, at that date and examined the query, it was not deleted, Nothing seemed out of order. Query_Result_Instance reported zero rows.
I created a version of the same query, and just ran it. It has a low number of rows, but ran to completion
May just have to let this one go. I looked in the data schema there are no fields that are defined as LONG. Not sure.
-Peter