DataStage job - runtime error ORA-01036

AthiraM · Post by **AthiraM** » Mon Mar 05, 2007 11:40 pm

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

kumar_s · Post by **kumar_s** » Mon Mar 05, 2007 11:50 pm

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.

chulett · Post by **chulett** » Mon Mar 05, 2007 11:53 pm

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?

kumar_s · Post by **kumar_s** » Mon Mar 05, 2007 11:57 pm

Craig, the only info that I got from the picture is, Package3_2 is an lookup table.
Perhaps, Providing us the query used in main stream as well as the lookup table will help.

AthiraM · Post by **AthiraM** » Tue Mar 06, 2007 2:57 am

Hi,

I redesigned the job as in figure :

http://img176.imageshack.us/my.php?image=dsjobbp0.jpg

Its working fine now.

Thankyou All
Athira

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 4:36 am

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.

DSguru2B · Post by **DSguru2B** » Tue Mar 06, 2007 8:22 am

I am one of those who uses hashed files alone for lookups, or database level sql joins. I think hashed files are the essence of server jobs.

AthiraM · Post by **AthiraM** » Wed Mar 07, 2007 1:01 am

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

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

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 1:12 am

seq_order_no.nextval is Database function not Datastage function.
So remove this column from Datastage, and re arrange the order of the columns starting from :1,:2... rather than :2,:3 etc.

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 1:14 am

And if those lookup tables "Package..." doesn't gets changed for each run, you can isolate the population Hashed file into separate job. So that you can avoid some unnecessary IO.

AthiraM · Post by **AthiraM** » Wed Mar 07, 2007 1:53 am

How can then the sequence be used?

When using ODBC stages I did create user-defined query where sequences where used and it did work fine, in that case i didn't mention the column in the ODBC stage.

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 1:59 am

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

AthiraM · Post by **AthiraM** » Wed Mar 07, 2007 2:09 am

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

kumar_s · Post by **kumar_s** » Wed Mar 07, 2007 2:14 am

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.

AthiraM · Post by **AthiraM** » Wed Mar 07, 2007 3:07 am

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

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