ORA-01008: not all variables bound

[ravij]

Hi,

We are migrating our project from version 6.0 to 7.5.1a in Tru64 OS. I am having Oracle stage as lookup and its having where condition. The custion SQL Stagement is ike:

SELECT TESTING_TB.PRODUCT_ID, TESTING_TB.PRO_COMP_NBR, TESTING_TB.UOM_STOCKING, TESTING_TB.SU_CONV_FACTOR, TESTING_TB.AZ_PRODUCT_ID
FROM I_ETL.TESTING_TB
WHERE AZ_PRODUCT_ID=:1

When I run this job in version 7.5.1aI am getting the error like.

ORA-01008: not all variables bound

When I run the same job in version 6.0, running fine. What does :1 refer exactly? What could be the problem. I searched in the forum but I could not find the required solution.
Please help me to solve this issue.
Thanks in advance.
[/code][/quote]

[sachin1]

see that all your column selection from database has exact number of columns in output beign mapped..........

[chulett]

As new versions are released, they fix things that weren't working right and generally tighten up any loopholes. Hopefully without create too many new ones. This really wasn't "running fine" in the old version, you were getting away with something you never should have been able to.

That :1 is a bind variable and means the value from the 1st column is used there in the sql. Oracle will complain if you do not mention all of the column bind variables in the sql. So, if you have three columns defined in the stage, you need to have :1, :2 and :3 in the sql so it knows what to do with all the values being passed in.

This is one of the inherent dangers of custom sql and 99 times out of 100 people use it when there's no need to. Let the stage generate the dang sql.

[ravij]

Hi All,

First of all Thanks for you help.

I am still facing the same problem. I am facing it with only custon SQL in Look up Oracle stage. If it is generated SQL, the job is running fine.

I took the qurey from Generated SQL and put it in custom sql as it is and tried to vew the data, but in this case also its giving same error like:

ORA-01008: not all variables bound

I don't understand what could be the problem. Please help me how to solve this issue.

Your help will be appreciated
Thanks in advance.

[chulett]

I've already explained both the problem and the solution.

Why do you need custom SQL when the generated SQL works for you? What changes are you making to it? If you want specific help, you'd need to provide specifics:

1. Number of columns defined in the stage
2. Which are Key columns
3. Your SQL

[ravij]

Hi Craig,

Thanks for your reply.

Actually I need to include some conditions in the WHERE cluase, for tht I need custom sql. My actual query looks like.

SELECT DISTINCT
A.PRODUCT_ID,
A.PROD_COMP_NBR,
A.UOM_STOCKING,
NVL(A.SU_CONV_FACTOR,0),
B.AZ_PRODUCT_ID
FROM
ODS.PRODUCT_SKU_DETAILS A,
ODS.PRODUCT_AZTEC_UAL_MAPPING B
WHERE
B.AZ_PRODUCT_ID = :1 AND
A.PRODUCT_TYPE IN ('F','G','H') AND
A.PRODUCT_ID = B.PRODUCT_ID

I have TWO tables and 5 columns in Oracle lookup stage and the FIFTH column(AZ_PRODUCT_ID) is the key column.

Please give me the solution.
Thanks in advance.

[chulett]

Well, you're close. You are selecting five columns so that matches what is in the stage. Your problem looks to be the fact that you are using a bind variable for column one when in fact you need a bind variable for each key column and you said the key column was the fifth column.

Change :1 to :5 and see if it works more better.