DSXchange

We recently upgraded the 6.0.1 jobs to 7.5.1. After the upgradation we are testing the jobs. In the process we are getting this error
"ORA-01008: not all variables bound" .

The design of the jobs is simple, with a source a oracle 9i lookup, loading to a target hash file.

The error is basically occuring at the lookup while executing the SQL
"SELECT * FROM PGM WHERE PGM.PGM_S_ID=:1 "

PGM_S_ID is the primary key in this table and so the PGM.PGM_S_ID=:1 is generated by datastage.

Now , if i remove this "PGM.PGM_S_ID=:1" it works fine. If i keep it and run the job iam getting the ": ORA-01008: not all variables bound"

any one has any idea how to resolve this?????what is causing this error??

Hi,
Usually I'd asume the table definition doesn't have the PK checked for any column in your case; is it?
Or perhaps a change in scheme ?
Performing a "select * ... " is a perfect way to have jobs fail along time due to scheme changes.

It might also have something to do with the fact that 7.5.1 has an ORAOCI stage and 6 version had ORAOCI9 stage, so you might want to check it as well.

If none of the above was the issue simply try rebuilding this stage/link

IHTH,

vcannadevula wrote:We recently upgraded the 6.0.1 jobs to 7.5.1. After the upgradation we are testing the jobs. In the process we are getting this error
"ORA-01008: not all variables bound" .

The design of the jobs is simple, with a source a oracle 9i lookup, loading to a target hash file.

The error is basically occuring at the lookup while executing the SQL
"SELECT * FROM PGM WHERE PGM.PGM_S_ID=:1 "

PGM_S_ID is the primary key in this table and so the PGM.PGM_S_ID=:1 is generated by datastage.

Now , if i remove this "PGM.PGM_S_ID=:1" it works fine. If i keep it and run the job iam getting the ": ORA-01008: not all variables bound"

any one has any idea how to resolve this?????what is causing this error??

Well, I could resolve the issue.........But can any one explain the concpet behind the ":1"??????? what datastage internally does...

That represents the (one) key value supplied for the reference link.

HI

You can keep the sql as it is "SELECT * FROM PGM WHERE PGM.PGM_S_ID=:1 " but uncheck the checkbox for "Pre 4.2 user-defined sql". Then compile. By default this checkbox gets checked.

I tried this way once.

Regards
Sreenivasulu

This is a bug of OCI Stage in DS 7.5.1. Here is the link for the information :

http://www.ascentialsoftware.com/eservi ... tches.html

Gook luck ...