Page 1 of 1
ORA-01036: illegal variable name/number
Posted: Mon Sep 25, 2006 10:26 am
by Abdulwahabza
Hi,
I am doing look up using OCI Stage, and its was giving error
T_SLS_ORD_XTRCT: ORA-01036: illegal variable name/number,
I change it sql to user defined
Code: Select all
SELECT T_SLS_ORD_XTRCT.VBELN_SLS_ORD_DOC_NUM,
T_SLS_ORD_XTRCT.POSNR_SLS_ORD_DOC_LIN_NUM,
T_SLS_ORD_XTRCT.ERDAT_SLS_ORD_CRTE_DT,
T_SLS_ORD_XTRCT.BSTKDE_SLS_ORD_OSD_DEPT_NAM,
T_SLS_ORD_XTRCT.YYFLXITMDESC,
T_SLS_ORD_XTRCT.BSARK_E_SHIP_TO_PO_TYPE_CD,
T_SLS_ORD_XTRCT.CSOS_ID,
T_SLS_ORD_XTRCT.CSOS_AUTH_IND,
T_SLS_ORD_XTRCT.YYMSG_IND_ITM_SUB_IND,
T_SLS_ORD_XTRCT.SMO_CRTE_BY,TO_CHAR(T_SLS_ORD_XTRCT.SMO_CRTE_DTS, 'YYYY-MM-DD H24:MI:SS'),
T_SLS_ORD_XTRCT.SMO_LAST_UPDT_BY,
TO_CHAR(T_SLS_ORD_XTRCT.SMO_LAST_UPDT_DTS, 'YYYY-MM-DD HH24:MI:SS') FROM RA01.T_SLS_ORD_XTRCT WHERE T_SLS_ORD_XTRCT.VBELN_SLS_ORD_DOC_NUM=:1 AND T_SLS_ORD_XTRCT.POSNR_SLS_ORD_DOC_LIN_NUM=:2 AND T_SLS_ORD_XTRCT.ERDAT_SLS_ORD_CRTE_DT=:3
Change the array size to 1 and read commited transaction
Now it giving error
SRADSIPass2_230..TF_SI_RJT: ORA-01722: invalid number
Any help is greatly apprecated
Re: ORA-01036: illegal variable name/number
Posted: Mon Sep 25, 2006 10:31 am
by bibhudc
could it be this part "ERDAT_SLS_ORD_CRTE_DT=:3" ? try ERDAT_SLS_ORD_CRTE_DT=TO_date(:3, 'YYYY-MM-DD')
Re: ORA-01036: illegal variable name/number
Posted: Mon Sep 25, 2006 10:38 am
by DeepakCorning
Abdulwahabza wrote:TO_CHAR(T_SLS_ORD_XTRCT.SMO_CRTE_DTS, 'YYYY-MM-DD H24:MI:SS'),
Should not be this HH24? I am not sure whether thats the source of the error.
Posted: Mon Sep 25, 2006 10:45 am
by meena
Hi,
T_SLS_ORD_XTRCT: ORA-01036: illegal variable name/number
For the above check with the parameter assigned.
SRADSIPass2_230..TF_SI_RJT: ORA-01722: invalid number
For this message check with the conversion.
Code: Select all
T_SLS_ORD_XTRCT.SMO_CRTE_BY,TO_CHAR(T_SLS_ORD_XTRCT.SMO_CRTE_DTS, 'MM/DD/YYYY HH:MI:SS'),
Re: ORA-01036: illegal variable name/number
Posted: Mon Sep 25, 2006 10:45 am
by Abdulwahabza
bibhudc wrote:could it be this part "ERDAT_SLS_ORD_CRTE_DT=:3" ? try ERDAT_SLS_ORD_CRTE_DT=TO_date(:3, 'YYYY-MM-DD')
ERDAT_SLS_ORD_CRTE_DT datatype is number 8 and its had data as 20060828, keys are First 3 columns...I tried as u said its giving error
SRADSIPass2_230..T_SLS_ORD_XTRCT: ORA-00932: inconsistent datatypes: expected NUMBER got DATE
Any other suggestion...Please
Re: ORA-01036: illegal variable name/number
Posted: Mon Sep 25, 2006 10:59 am
by bibhudc
I am sure you looked at ORA-01722 invalid number already. Just in case you didn't:
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
If ERDAT_SLS_ORD_CRTE_DT is a number datatype, then you don't need to convert :3 to date datatype.
The only other potential conversions you are doing are
TO_CHAR(T_SLS_ORD_XTRCT.SMO_CRTE_DTS, 'YYYY-MM-DD H24:MI:SS'),
TO_CHAR(T_SLS_ORD_XTRCT.SMO_LAST_UPDT_DTS, 'YYYY-MM-DD HH24:MI:SS')
Make sure these are DATE datatypes in Oracle. Does the sql work in a sql client like SQL*Plus, Toad etc ?
Abdulwahabza wrote:bibhudc wrote:could it be this part "ERDAT_SLS_ORD_CRTE_DT=:3" ? try ERDAT_SLS_ORD_CRTE_DT=TO_date(:3, 'YYYY-MM-DD')
ERDAT_SLS_ORD_CRTE_DT datatype is number 8 and its had data as 20060828, keys are First 3 columns...I tried as u said its giving error
T_SLS_ORD_XTRCT: ORA-00920: invalid relational operator
Any other suggestion...Please
Posted: Mon Sep 25, 2006 12:02 pm
by Abdulwahabza
The query is working in toad. i am confused, why is it giving me error in datastage as invalid..Please help
I am using OC1 Stage as look up..
SeqFile--->Transformer------->FlatFile
...its giving error as
SRADSIPass2_230..TF_SI_RJT: ORA-01722: invalid number
Posted: Mon Sep 25, 2006 3:53 pm
by ray.wurlod
Are you 100% certain that the metadata used in your job (on the output link from the OCI stage) matches that of the Oracle table? In particular, are you trying to select a NUMBER from a VARCHAR2 column?
Posted: Tue Sep 26, 2006 8:42 am
by Abdulwahabza
ray.wurlod wrote:Are you 100% certain that the metadata used in your job (on the output link from the OCI stage) matches that of the Oracle table? In particular, are you trying to select a NUMBER from a VARCHAR2 colu ...
Yes Ray I am 100% sure I am not trying to select from NUMBER from a VARCHAR2 Column, I could read the table with view data button, but when i run the job its getting aborted with a fatal error
Code: Select all
SRADSIPass2_230..TF_SI_RJT: ORA-01722: invalid number
Please help
Thanks in advance
Posted: Tue Sep 26, 2006 9:21 am
by DeepakCorning
The Simplest thing to do is pull out the OCI Lookup stage in another job and Populate a Flat file (Hashed File or SEQ file) using the OCI stage as a source.
As in toad the query is working fine , it should be a probelm with the compare or as ray said may be the metadata.
Let us know what happens if you do the above.
Posted: Tue Sep 26, 2006 9:36 am
by Abdulwahabza
DeepakCorning wrote:The Simplest thing to do is pull out the OCI Lookup stage in another job and Populate a Flat file (Hashed File or SEQ file) using the OCI stage as a source.
As in toad the query is working fine , it should be a probelm with the compare or as ray said may be the metadata.
Let us know what happens if you do the above.
I created a hash file out of OCI stage and look it up against the flat file
the job is running fine. but when i use OCI stage directly as look up Job is getting aborted with the invalid number error, I cannot keep hashed file because the table contains 200 millions rows, I am confused why job is working with hashed file but with OCI stage
Thank you so much ...any other ideas
Regards
Posted: Tue Sep 26, 2006 9:37 am
by Abdulwahabza
DeepakCorning wrote:The Simplest thing to do is pull out the OCI Lookup stage in another job and Populate a Flat file (Hashed File or SEQ file) using the OCI stage as a source.
As in toad the query is working fine , it should be a probelm with the compare or as ray said may be the metadata.
Let us know what happens if you do the above.
I created a hash file out of OCI stage and look it up against the flat file
the job is running fine. but when i use OCI stage directly as look up Job is getting aborted with the invalid number error, I cannot keep hashed file because the table will contains 200 millions rows or more in future, I am confused why job is working with hashed file but with OCI stage
Thank you so much ...any other ideas
Regards
Posted: Tue Sep 26, 2006 9:41 am
by DeepakCorning
Well I am confused , you are using OCI stage because you have 200 Million rows in the table?? I was thinking you have some inequality lookup and hence using OCI stage.
The question that arises in my mind here is then A Hashed file will work slower than a OCI stage when there are 200 Million Rows?? Does not sound correct to me , I will let experts answer on this.
Posted: Tue Sep 26, 2006 11:21 am
by Abdulwahabza
DeepakCorning wrote:Well I am confused , you are using OCI stage because you have 200 Million rows in the table?? I was thinking you have some inequality lookup and hence using OCI stage.
The question that arises in my mind here is then A Hashed file will work slower than a OCI stage when there are 200 Million Rows?? Does not sound correct to me , I will let experts answer on this.
I think there will be data over spill if the Hashed file size is over 2 GB, we can increase the size of hashed file to 64 Bits so that it can take data more than 2GB of data.
Regarding my problem I found the source of fatal error Invalid Number, The problem is data, The flat file(source) is having fixed width column, The keys with which i am looking up is having space in them in flat file, when i change the data of flat file ( keys ) from space to numeric, the Job is running fine.so i put a tranformer 1 between with logic changing all the space to 999999999(datatype is (number(9)) . is this the best way to do it.. or can anyone please give me a better idea...Thanks a lot
Code: Select all
OCI Stage
.
.
flatfile......tranformer1......Tranformer 2........flatfile
.
.
Rejects flat File 2
Regards,
Posted: Tue Sep 26, 2006 3:27 pm
by ray.wurlod
Congratulations on successful detective work! Yours is a good solution.
You could have used a filter command in the Sequential File stage, perhaps using sed or awk to change the column in question. But there's nothing at all wrong with your solution.