ORA-01036: illegal variable name/number

logic · Post by **logic** » Wed Aug 24, 2005 2:00 pm

Hi,
I am getting the following error in a straight move initial load job.

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]

logic · Post by **logic** » Wed Aug 24, 2005 2:15 pm

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.

ketfos · Post by **ketfos** » Wed Aug 24, 2005 2:56 pm

Hi,

I fail to understand the way you got it resolve.
Can somebody shed more light into it?

Ketfos

logic · Post by **logic** » Wed Aug 24, 2005 4:12 pm

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.

ketfos · Post by **ketfos** » Wed Aug 24, 2005 4:27 pm

Hi Ash,
Can you share the SQl statement which resulted in an error?

Ketfos

logic · Post by **logic** » Wed Aug 24, 2005 4:51 pm

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,

ray.wurlod · Post by **ray.wurlod** » Wed Aug 24, 2005 5:09 pm

Could it have been the case that your parameter array size exceeded your rows per commit setting?

logic · Post by **logic** » Wed Aug 24, 2005 5:24 pm

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.

chulett · Post by **chulett** » Wed Aug 24, 2005 6:20 pm

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.

logic · Post by **logic** » Thu Aug 25, 2005 10:11 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 25, 2005 5:30 pm

Was this column definition imported from Oracle? If so, it's got to be right, hasn't it?

logic · Post by **logic** » Thu Aug 25, 2005 5:45 pm

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

roy · Post by **roy** » Fri Aug 26, 2005 12:06 am

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,

logic · Post by **logic** » Fri Aug 26, 2005 10:05 am

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]