ORA-01036: illegal variable name/number

[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

[bibhudc]

could it be this part "ERDAT_SLS_ORD_CRTE_DT=:3" ? try ERDAT_SLS_ORD_CRTE_DT=TO_date(:3, 'YYYY-MM-DD')

[DeepakCorning]

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.

[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'), 

[Abdulwahabza]

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

[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 ?

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

[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

[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?

[Abdulwahabza]

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

[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.

[Abdulwahabza]

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

[Abdulwahabza]

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

[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.

[Abdulwahabza]

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,

[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.