DataStage job - runtime error ORA-01036
Moderators: chulett, rschirm, roy
DataStage job - runtime error ORA-01036
Hi,
The job in the figure raises the fatal error
ORA1..Package3_2: ORA-01036: illegal variable name/number
http://img161.imageshack.us/my.php?image=dsjobhl3.jpg
Did confirm with the user-defined SQL statement and the data in the table can be viewed. Please do comment on the same.
Thanks Athira
The job in the figure raises the fatal error
ORA1..Package3_2: ORA-01036: illegal variable name/number
http://img161.imageshack.us/my.php?image=dsjobhl3.jpg
Did confirm with the user-defined SQL statement and the data in the table can be viewed. Please do comment on the same.
Thanks Athira
The error is from the lookup table. In that case, you shouldn't be able to view the data.
Check if you have crated the lookup variable correctly. If you are using User defined SQL, avoid it and use SQL builder to frame the query.
Check if you have crated the lookup variable correctly. If you are using User defined SQL, avoid it and use SQL builder to frame the query.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
The picture doesn't help at all except, as Kumar notes, to let us know which stage is 'Package3_2'.
Confirm again. Your user-defined sql does not conform to the rules needed for execution in an OCI stage. Every column in the stage must be bound into the query. Key fields must be used in the where clause. All columns must be returned by the select statement.
Why are you using user-defined sql?![Confused :?](./images/smilies/icon_confused.gif)
Confirm again. Your user-defined sql does not conform to the rules needed for execution in an OCI stage. Every column in the stage must be bound into the query. Key fields must be used in the where clause. All columns must be returned by the select statement.
Why are you using user-defined sql?
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi,
I redesigned the job as in figure :
http://img176.imageshack.us/my.php?image=dsjobbp0.jpg
Its working fine now.
Thankyou All
Athira
I redesigned the job as in figure :
http://img176.imageshack.us/my.php?image=dsjobbp0.jpg
Its working fine now.
Thankyou All
Athira
So its certainly with the bind variable that you used in lookup. It could have been easily identified if you posted the query. Still I dont find it reasonable to populate a Hashed file and doing a lookup on Hashed file.
So do you mean the problem is resolved?? If so you can mark the thread as
such.
So do you mean the problem is resolved?? If so you can mark the thread as
such.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I faced the issue mentioned above in the input stages which I could resolve using the hash file.
The same error now pops up in the ouput stage too![Sad :(](./images/smilies/icon_sad.gif)
I designed 2 jobs
The first design, the transformer stage and the sql query is as follows :
figure 1 ---> http://img113.imageshack.us/my.php?image=dsjob1ig1.jpg
figure 2 ---> http://img254.imageshack.us/my.php?image=dsjob2fg6.jpg
The output table vas_order_master has one more colum to be populated - vom_order_no, using a sequence in oracle.
I have included the same the same in the sql query.
figure 3 ---> http://img92.imageshack.us/my.php?image=dsjob3cm2.jpg
The sql query as seen in figure :
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:2,TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),:6,:7,:8,:9,:10);
The second design and the transformer stage :
I included the colum in the stage and tried to use the sequence there itself
figure 1 --->http://img178.imageshack.us/my.php?image=dsjob4rl7.jpg
figure 2 --->http://img117.imageshack.us/my.php?image=dsjob5oc9.jpg
Do suggest on how to proceed.
Athira
The same error now pops up in the ouput stage too
![Sad :(](./images/smilies/icon_sad.gif)
I designed 2 jobs
The first design, the transformer stage and the sql query is as follows :
figure 1 ---> http://img113.imageshack.us/my.php?image=dsjob1ig1.jpg
figure 2 ---> http://img254.imageshack.us/my.php?image=dsjob2fg6.jpg
The output table vas_order_master has one more colum to be populated - vom_order_no, using a sequence in oracle.
I have included the same the same in the sql query.
figure 3 ---> http://img92.imageshack.us/my.php?image=dsjob3cm2.jpg
The sql query as seen in figure :
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:2,TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),:6,:7,:8,:9,:10);
The second design and the transformer stage :
I included the colum in the stage and tried to use the sequence there itself
figure 1 --->http://img178.imageshack.us/my.php?image=dsjob4rl7.jpg
figure 2 --->http://img117.imageshack.us/my.php?image=dsjob5oc9.jpg
Do suggest on how to proceed.
Athira
Create a sequence "seq_order_no" in database. And now where ever you call "seq_order_no.nextval", you ll get the sequence number from database.
This will be assigned to VOM_ORDER_NO. So no need to assign anything in Transformer stage for sequence no.
Transformer will deliver the record from VOM_ORDER_VIM. Which is the first field (:1).
This will be assigned to VOM_ORDER_NO. So no need to assign anything in Transformer stage for sequence no.
Transformer will deliver the record from VOM_ORDER_VIM. Which is the first field (:1).
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I already do have the sequence created in the Oracle database.
An insert statement into the oracle database should include the sequence which is mandatory I believe, please correct me if wrong.
So when defining the insert query in the stage how will the query be ( with the sequence also specified) ?
An insert statement into the oracle database should include the sequence which is mandatory I believe, please correct me if wrong.
So when defining the insert query in the stage how will the query be ( with the sequence also specified) ?
As I mentioned in my previous posts, the VALUES section will be (seq_order_no.nextval,:1,TO_DATE(:2,'YYYY.....
Make sure you dont have the sequence column defined in Transformer. Dont you see that, it marked Red without proper derivation.
Make sure you dont have the sequence column defined in Transformer. Dont you see that, it marked Red without proper derivation.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
sorry i didnt get that statement , i tried it out now ...
anyways the ORA-01036 is no longer popping up..
it gave me a new warning and a fatal error![Smile :)](./images/smilies/icon_smile.gif)
The warning :
ORA1..Oracle_OCI_3: INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9); is not supported for pre-defined update action.
The fatal error :
ORA1..Oracle_OCI_3: ORA-24373: invalid length specified for statement
I tried changing the sql query as such (without the senicolon at the end) :
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9)
Its now working .
Thanks a lot Athira
anyways the ORA-01036 is no longer popping up..
it gave me a new warning and a fatal error
![Smile :)](./images/smilies/icon_smile.gif)
The warning :
ORA1..Oracle_OCI_3: INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9); is not supported for pre-defined update action.
The fatal error :
ORA1..Oracle_OCI_3: ORA-24373: invalid length specified for statement
I tried changing the sql query as such (without the senicolon at the end) :
INSERT INTO ODB.VAS_ORDER_MASTER_NEW (VOM_ORDER_NO,VOM_CUSTOMER_EIN,VOM_SUBMITTED_DT,VOM_START_DT,VOM_ACTUAL_END_DT,VOM_ORDER_TYPE,VOM_MONTHLY_ALLOWANCE,VOM_CHC,VOM_STATUS_NO,VOM_REALLOC_ORDER_NO) VALUES (seq_order_no.nextval,:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,:7,:8,:9)
Its now working .
Thanks a lot Athira