How to rectify issue on UPDATE?
Following is related data and error
Error starting at line : 1 in command - UPDATE JOB_STATUS SET STATUS ='FAILED' ,ENDDATE=sysdate, REMARKS = 'Error' WHERE ID = 30 Error at Command Line : 3 Column : 7 Error report - SQL Error: ORA-00997: illegal use of LONG datatype 00997. 00000 - "illegal use of LONG datatype" *Cause: *Action:
Following is table structure:
ID LONG STARTDATE TIMESTAMP(6) ENDDATE TIMESTAMP(6) STATUS VARCHAR2(20 CHAR) REMARKS VARCHAR2(255 CHAR) JOBID NUMBER(38,0)
Not able to find answers in related questions. Not sure whether i need to modify table datatype but when i try to modify it says remove data first.
Advertisement
Answer
if ID is Primary Key – it should not be LONG.
The LONG column has limitations – You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.
there is larger list where it cannot be used! including
- LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
see for list https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF00201
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1831