Page 1 of 1
ORA-01036: illegal variable name/number
Posted: Wed Aug 24, 2005 2:00 pm
by logic
Hi,
I am getting the following error in a straight move initial load job.
Code: Select all
ORA-01036: illegal variable name/number
I am using ODBC to pull the data as well to load the data. I AM NOT USING A CUSTOM SQL in this job. Also there is no lookup. The column names i am using also do not include any reserved word/words.I am getting this error when Ds is trying to execute the insert sql statement
Can anybody please help in debugging this error.
Thanks,
Ash.[/code]
Posted: Wed Aug 24, 2005 2:15 pm
by logic
Hi All,
Making the array size as 1 and transaction handling = read committed....took care of the error.
Thanks for your attention and time,
Ashish.
Posted: Wed Aug 24, 2005 2:56 pm
by ketfos
Hi,
I fail to understand the way you got it resolve.
Can somebody shed more light into it?
Ketfos
Posted: Wed Aug 24, 2005 4:12 pm
by logic
Hi,
I first looked into all the names..when I did not find anything ilegal over there. I guessed it must be something wrong with a numeric variable and since i had changed the array size earlier .i tried playing with it. I am also curious why it it did not like array size of 100 and was OK with an array size of 1.
Thanks,
Ash.
Posted: Wed Aug 24, 2005 4:27 pm
by ketfos
Hi Ash,
Can you share the SQl statement which resulted in an error?
Ketfos
Posted: Wed Aug 24, 2005 4:51 pm
by logic
Hi Ketfos,
it was not a user defined sql but a generated one. following is the error message that i was getting before making changes to the array size.
Code: Select all
CW_HR_init_histPS_RC_CASE..XFM_PS_RC_CASE.LNK_Out_PS_RC_CASE: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO SYSADM.HISTPS_RC_CASE(CASE_ID, BUSINESS_UNIT, ROW_LASTMANT_DTTM, RC_VERTICAL, CASE_TYPE, CASE_CONTACT, BO_ID_CUST, ROLE_TYPE_ID_CUST, BO_ID_CONTACT, RC_SOURCE, PROFILE_CM_SEQ_PHN, PROFILE_CM_SEQ_EML, PROVIDER_GRP_ID, ASSIGNED_TO, RC_STATUS, RC_PRIORITY, RC_CATEGORY, RC_TYPE, RC_DETAIL, RES_FIRST_CNTCT, CREATION_DATE, CLOSED_DATE, RC_SUMMARY, RC_REOPN_REASON_CD, RC_CLOSE_REASON_CD, RC_DESCRLONG, MNTSTATUSINSOURCE, ROW_ADDED_DTTM) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=HY000, DBMS.CODE=1036
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle driver][Oracle]ORA-01036: illegal variable name/number
Thanks,
Posted: Wed Aug 24, 2005 5:09 pm
by ray.wurlod
Could it have been the case that your parameter array size exceeded your rows per commit setting?
Posted: Wed Aug 24, 2005 5:24 pm
by logic
Hi Ray ,
My tansaction size was 1000 and array size before was 500 which when changed to 100 got rid of the error. Thanks for showing the direction.
Ash.
Posted: Wed Aug 24, 2005 6:20 pm
by chulett
No, this just confuses things more.
Which one of the two values 'when changed to 100 got rid of the error'?
Ray said Array Size > Transaction Size. That wasn't your problem, as best as I can tell.
Posted: Thu Aug 25, 2005 10:11 am
by logic
Hi Craig,
No. U r right. transaction size was 1000 and array size was 500 when i git that error and when i made the array size 100 and transaction handling read committed Ds did not give me the error. (And all other things were unchanged).
There is a column with longvarchar and size is 2147483647. but i dont think that has anything to contribute to this.right?
Thanks,
Ash.
Posted: Thu Aug 25, 2005 5:30 pm
by ray.wurlod
Was this column definition imported from Oracle? If so, it's got to be right, hasn't it?
Posted: Thu Aug 25, 2005 5:45 pm
by logic
Hi,
yes it was imported from oracle alright.
....but is this a factor that was contributing anyway for causing the illegal variable error. Anyway the column remained there before the error and after so I dont think it was causing the error. But I was wondering if a Clob contributes in any manner to the transaction wrt array?
thanks
Posted: Fri Aug 26, 2005 12:06 am
by roy
Hi,
It might be that your array size was to big!
8192 (your ODBC's name array) divided by 28 columns is more around 292 then 500
there for it should compile and work for up to 292 minus 1 or 2 in your array size.
Please test and confirm my suspicion,
logic wrote:Hi Ketfos,
it was not a user defined sql but a generated one. following is the error message that i was getting before making changes to the array size.
Code: Select all
CW_HR_init_histPS_RC_CASE..XFM_PS_RC_CASE.LNK_Out_PS_RC_CASE: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO SYSADM.HISTPS_RC_CASE(CASE_ID, BUSINESS_UNIT, ROW_LASTMANT_DTTM, RC_VERTICAL, CASE_TYPE, CASE_CONTACT, BO_ID_CUST, ROLE_TYPE_ID_CUST, BO_ID_CONTACT, RC_SOURCE, PROFILE_CM_SEQ_PHN, PROFILE_CM_SEQ_EML, PROVIDER_GRP_ID, ASSIGNED_TO, RC_STATUS, RC_PRIORITY, RC_CATEGORY, RC_TYPE, RC_DETAIL, RES_FIRST_CNTCT, CREATION_DATE, CLOSED_DATE, RC_SUMMARY, RC_REOPN_REASON_CD, RC_CLOSE_REASON_CD, RC_DESCRLONG, MNTSTATUSINSOURCE, ROW_ADDED_DTTM) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=HY000, DBMS.CODE=1036
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle driver][Oracle]ORA-01036: illegal variable name/number
Thanks,
Posted: Fri Aug 26, 2005 10:05 am
by logic
Hi Roy,
Thanks . .
One correction..in one of the earlier posts I mentioned that the job was running fine with an array size of 100.
Code: Select all
My tansaction size was 1000 and array size before was 500 which when changed to 100 got rid of the error
The original post is correct and the job is only accepting an array size of 1
Code: Select all
Making the array size as 1 and transaction handling = read committed....took care of the error
Sorry!!
Anyway I tested the job and it is aborting with illegal variable/number for any array size other than 1. its not even taking 2.
Thanx,
Ash.[/code]