Oracle Error

At random, I get this error: 'ORA-01461: can bind a LONG value only for insert into a LONG column'
when I run a SQL query against our database. When I get this error I just rerun the SQL query and everything works. I don't understand how this error shows up but then disappears when I run the exact query a second time. Can anyone explain why this is or how to stop this from happening.

I'm running a SQL query against our ORACLE 10g database.

Thanks
amaher84Asked:
Who is Participating?
 
DavidSenior Oracle Database AdministratorCommented:
That's a chuck of code, all right!  For the record, your statement begins not with a query (SELECT) but rather an INSERT.  Apparently why it "works the second time" is that your logic succeeds on the 2nd pass.
From an outside source: An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' thru '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.

There are numerous situations where this conversion may occur. A numeric column may be the object of an INSERT or an UPDATE statement. Or, a numeric column may appear as part of a WHERE clause. It is even possible for this error to appear when there are no numeric columns appearing explicitly in the statement!  
My counsel at this point is to get the table description and some caffine.  You're going to have to isolate your mismatched datatype.
0
 
amaher84Author Commented:
Another note, the queries that run are all different, it is never the same query that produces this error.  This makes it that much harder, why at some point this error pops up and the same query has been running for months before and works after I run it for a second time, I don't know.
0
 
DavidSenior Oracle Database AdministratorCommented:
I'd be curious to see your SELECT statement please.  I'm at a loss to explain how a query is attempting to perform an INSERT.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
please provide the complete sql query which can give that error ?
0
 
amaher84Author Commented:
Sorry, the log for that error has been deleted, but it's similar in behavior to the one I have below.  The error above and the Error below come at random on different D.B. tables.  After I rerun this job with this sql below the job will be successful and won't error out.  I just don't understand as to why the Error above and the one below happen at random on different tables at different times.  Also, they don't happen everyday or even every week.  


java.sql.SQLException: ORA-01722: invalid number
statement = INSERT /*+ APPEND */ INTO M_ONTRACKTRAININGCUBE_SIB1 (A_N_TRAININGHOURSOTFTDASH, A_N_TRAININGHOURSOTFT, ELEMID, PAYGRADE_AXIS_ID, PROCESS_AXIS_ID, WHEN_AXIS_ID, EMPLOYMENTSTATUS_AXIS_ID, WHO_AXIS_ID) SELECT SUM( (CASE WHEN ((M_T_30__M_ONTRACKTRAININGCHUNK.CLASSSTATUS = 'Attended') OR (M_T_30__M_ONTRACKTRAININGCHUNK.CLASSSTATUS = 'Incomplete')) THEN (CASE WHEN ((LENGTH( LTRIM( RTRIM( TRANSLATE( M_T_30__M_ONTRACKTRAININGCHUNK.NOTES, '+-.0123456789', ' ' ) ) ) ) IS NULL) AND (not (M_T_30__M_ONTRACKTRAININGCHUNK.NOTES IS NULL))) THEN TO_NUMBER( M_T_30__M_ONTRACKTRAININGCHUNK.NOTES ) ELSE M_T_30__M_ONTRACKTRAININGCHUNK.CREDITHRS END) ELSE 0 END) ) A_N_TRAININGHOURSOTFTDASH, SUM( (CASE WHEN ((M_T_30__M_ONTRACKTRAININGCHUNK.CLASSSTATUS = 'Attended') OR (M_T_30__M_ONTRACKTRAININGCHUNK.CLASSSTATUS = 'Incomplete')) THEN (CASE WHEN ((LENGTH( LTRIM( RTRIM( TRANSLATE( M_T_30__M_ONTRACKTRAININGCHUNK.NOTES, '+-.0123456789', ' ' ) ) ) ) IS NULL) AND (not (M_T_30__M_ONTRACKTRAININGCHUNK.NOTES IS NULL))) THEN TO_NUMBER( M_T_30__M_ONTRACKTRAININGCHUNK.NOTES ) ELSE M_T_30__M_ONTRACKTRAININGCHUNK.CREDITHRS END) ELSE 0 END) ) A_N_TRAININGHOURSOTFT, MIN( M_T_30__M_ONTRACKTRAININGCHUNK.ID ) ELEMID, M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.PAYGRADE_NUM_KEY PAYGRADE_AXIS_ID, M_TH_CUBEPROCESSDIM_SIB1_CA_4U.PROCESS_NUM_KEY PROCESS_AXIS_ID, ((60000 * ROUND( 24 * 60 * ( TRUNC( M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE, 'DD' ) - to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ) )) + (60000 * (4 * 52596000))) WHEN_AXIS_ID, M_TH_CUBEEMPLOYMENTSTATUSDI_CE.EMPLOYMENTSTATUS_NUM_KEY EMPLOYMENTSTATUS_AXIS_ID, M_TH_CUBEPERSONDIM_SIB1_CAL_4X.PERSON_NUM_KEY WHO_AXIS_ID FROM M_CUBEPAYGRADEDIM_SIB1 M_TH_CUBEPAYGRADEDIM_SIB1_C_KY, M_CUBEPROCESSDIM_SIB1 M_TH_CUBEPROCESSDIM_SIB1_CA_4U, M_CUBETIMEDIM M_TH_CUBETIMEDIM_CALLED_WHE_IR, M_CUBEEMPLOYMENTSTATUSDIM_SIB1 M_TH_CUBEEMPLOYMENTSTATUSDI_CE, M_CUBEPERSONDIM_SIB1 M_TH_CUBEPERSONDIM_SIB1_CAL_4X, M_ONTRACKTRAININGCHUNK M_T_30__M_ONTRACKTRAININGCHUNK WHERE ((M_T_30__M_ONTRACKTRAININGCHUNK.ISINACTIVE = 0) AND ((M_T_30__M_ONTRACKTRAININGCHUNK.PERSON = M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.PERSONID) AND (((M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.UNTIL_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE < M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.UNTIL_TIME)) AND ((M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.SINCE_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE >= M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.SINCE_TIME)))) AND ((M_T_30__M_ONTRACKTRAININGCHUNK.PERSON = M_TH_CUBEPROCESSDIM_SIB1_CA_4U.PERSONID) AND (((M_TH_CUBEPROCESSDIM_SIB1_CA_4U.UNTIL_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE < M_TH_CUBEPROCESSDIM_SIB1_CA_4U.UNTIL_TIME)) AND ((M_TH_CUBEPROCESSDIM_SIB1_CA_4U.SINCE_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE >= M_TH_CUBEPROCESSDIM_SIB1_CA_4U.SINCE_TIME)))) AND (((60000 * ROUND( 24 * 60 * ( TRUNC( M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE, 'DD' ) - to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ) )) + (60000 * (4 * 52596000))) = M_TH_CUBETIMEDIM_CALLED_WHE_IR.ID) AND ((M_T_30__M_ONTRACKTRAININGCHUNK.PERSON = M_TH_CUBEEMPLOYMENTSTATUSDI_CE.PERSONID) AND (((M_TH_CUBEEMPLOYMENTSTATUSDI_CE.UNTIL_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE < M_TH_CUBEEMPLOYMENTSTATUSDI_CE.UNTIL_TIME)) AND ((M_TH_CUBEEMPLOYMENTSTATUSDI_CE.SINCE_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE >= M_TH_CUBEEMPLOYMENTSTATUSDI_CE.SINCE_TIME)))) AND ((M_T_30__M_ONTRACKTRAININGCHUNK.PERSON = M_TH_CUBEPERSONDIM_SIB1_CAL_4X.PERSONID) AND (((M_TH_CUBEPERSONDIM_SIB1_CAL_4X.UNTIL_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE < M_TH_CUBEPERSONDIM_SIB1_CAL_4X.UNTIL_TIME)) AND ((M_TH_CUBEPERSONDIM_SIB1_CAL_4X.SINCE_TIME IS NULL) OR (M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE >= M_TH_CUBEPERSONDIM_SIB1_CAL_4X.SINCE_TIME))))) GROUP BY M_TH_CUBEPAYGRADEDIM_SIB1_C_KY.PAYGRADE_NUM_KEY, M_TH_CUBEPROCESSDIM_SIB1_CA_4U.PROCESS_NUM_KEY, ((60000 * ROUND( 24 * 60 * ( TRUNC( M_T_30__M_ONTRACKTRAININGCHUNK.M_DATE, 'DD' ) - to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') ) )) + (60000 * (4 * 52596000))), M_TH_CUBEEMPLOYMENTSTATUSDI_
CE.EMPLOYMENTSTATUS_NUM_KEY, M_TH_CUBEPERSONDIM_SIB1_CAL_4X.PERSON_NUM_KEY ORDER BY WHEN_AXIS_ID, WHO_AXIS_ID
0
 
amaher84Author Commented:
haha yes I know it's a chunk code, thank you for the response.  What do you think of this then? The code I gave you above has run everyday for the past 6 months lets say and it will run tomorrow without this error.  So by it being random, perhaps the column definitions are set correctly, there was just some bad data perhaps entered. But if that was the case, it should fail the second time and every time afterwords. hmm at this point I just wonder if it's the internal compiler, do you think that might be a safe route to look?

I checked the table descriptions and everything is setup correctly and matches the data coming in.

It's just the randomness that's throwing me off, I'm thinking it might have to do with internal setup
0
 
DavidSenior Oracle Database AdministratorCommented:
Do you know how to enable sql_trace in diagnostic mode?  It might be worth getting some detail as the records are processed.  I don't know about the compiler.  It may be time to talk with Metalink support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.