Error:- ORA-01008: not all variables bound from jobs

OAC · Post by **OAC** » Thu Jun 16, 2005 11:08 am

Hi,

I wrote a job which retrieves a list of columns from one table and then in destination table it inserts the value. All the columns are mapped to destination table. We are passing null values for the column for which the source is not available. While running the job i got the following error. ORA-01008: not all variables bound

Please help me to resolve this problem.

We are using Oracle Database.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 11:47 am

Is it an user-defined sql?

You may be including more variables than the columns into the link.

chulett · Post by **chulett** » Thu Jun 16, 2005 12:14 pm

Has to be user defined SQL to generate errors like this. The parameter markers they are using (:1, :2, etc) don't match with the columns defined in the stage. For example, if you have five columns in the stage you must have five parameter markers in your sql numbered from :1 to :5.

OAC · Post by **OAC** » Thu Jun 16, 2005 12:57 pm

Hi,

It is not User Defined Query, I am using the version 7.5.1A. The same job is running perfectly in 7.0.42. Both are poing to same database.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 4:07 pm

Can you post the error message in full. It will provide more info.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 17, 2005 3:05 am

It might help also to post the generated SQL and how many columns there are in the Columns grid.

chulett · Post by **chulett** » Fri Jun 17, 2005 6:39 am

OAC wrote:It is not User Defined Query

I find that... interesting. I've never seen generated SQL not 'bind all columns', it usually takes a people to do that.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jun 17, 2005 7:02 am

Maybe something was changed elsewhere !!??

logic · Post by **logic** » Mon Aug 29, 2005 4:54 pm

Hi,
I am sort of hijacking this thread because i thought this as a related thread.

I am pulling data using ODBConnectivity and loading using a DRStage.
I am using 7.5.1 version. and this is a server job. Windows.

i am getting a ORA-01008 for a GENERATED ddl and The column number is the same.......There is no metadata mismatch...

There are 34 columns in the grid.

I had experienced the same error before and was unable to figure it out,but it was solved at that time when i had reimported and reloaded the metadata

But this time doing that also wont help.

What am i missing here??can anybody please throw some light.
THANKS,
Ash.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 29, 2005 5:47 pm

Is the generated SQL taking the key columns out into the WHERE clause leaving a mismatch?

Really, though, without seeing the actual table definition from the Oracle stage and the corresponding generated SQL, it's not possible to comment one way or the other.

chulett · Post by **chulett** » Mon Aug 29, 2005 5:59 pm

I'd also be curious if you are doing anything 'unusual' in the Where or Other clauses - like referencing any parameter markers there.

logic · Post by **logic** » Mon Aug 29, 2005 6:13 pm

Hi Ray,
This is the generated sql

Code: Select all

INSERT INTO SYSADM.EXTPS_CW_HIRE_REQ (MNTCRC,MNTNCI,CW_STATUS_FORM_NUM,CW_HIRE_TYPE,RQST_TYP,REG_TEMP,FULL_PART_TIME,CW_WORK_CITY,CW_START_DATE,MODIFIED_BY,CW_DT_MODIFIED,CW_AD_REF_NUM,CW_AD_DATE,CW_AD_COST,CW_REPL_EMPLID,CW_HIR_REPL_REASON,CW_NO_OF_POSITIONS,CW_DT_EQUIP_NEEDED,CW_STND_CORP_PC,CW_STND_TELEPHONE,CW_TELEPHONE_DISP,CW_STND_BRANCH_PC,CW_PRINTER,CW_TELEPHONE,CW_DESK,CW_CHAIR,CW_CUBICLE,POSITION_NBR,CW_MAIL_STOP,CW_HIR_ADJ_OPEN_DT,STD_HOURS,SHIFT,CW_HIR_OFR_PREP_DT,CW_LONG_COMMENTS) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,TO_DATE(:9, 'YYYY-MM-DD HH24:MI:SS'),:10,TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),:12,TO_DATE(:13, 'YYYY-MM-DD HH24:MI:SS'),:14,:15,:16,:17,TO_DATE(:18, 'YYYY-MM-DD HH24:MI:SS'),:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,TO_DATE(:30, 'YYYY-MM-DD HH24:MI:SS'),:31,:32,TO_DATE(:33, 'YYYY-MM-DD HH24:MI:SS'),:34)

I am sorry but I was unable to understand your first line

.

Code: Select all

Is the generated SQL taking the key columns out into the WHERE clause leaving a mismatch?

The error is occuring on the insert side I guess. The error warning is

Code: Select all

CW_HR_extPS_CW_HIRE_REQ..XFM_PS_CW_HIRE_REQ: The value of the row is: MNTCRC = 1724476439 MNTNCI = I CW_STATUS_FORM_NUM = 1234 CW_HIRE_TYPE = HIR RQST_TYP = A REG_TEMP = R FULL_PART_TIME = F CW_WORK_CITY =   CW_START_DATE = NULL MODIFIED_BY = 23456 CW_DT_MODIFIED = 1999-06-18 00:00:00 CW_AD_REF_NUM =   CW_AD_DATE = NULL CW_AD_COST = 0.00 CW_REPL_EMPLID =   CW_HIR_REPL_REASON =   CW_NO_OF_POSITIONS = 1 CW_DT_EQUIP_NEEDED = NULL CW_STND_CORP_PC = 0 CW_STND_TELEPHONE = 0 CW_TELEPHONE_DISP = 0 CW_STND_BRANCH_PC = 0 CW_PRINTER = 0 CW_TELEPHONE = 0

Code: Select all

CW_HR_extPS_CW_HIRE_REQ..XFM_PS_CW_HIRE_REQ: ORA-01008: not all variables bound

The key is cw_status_form_num.

Thanks,
Ash.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 29, 2005 6:57 pm

For whatever reason, only columns up to an including CW_TELEPHONE (that is, the first 20 columns) are being used in the INSERT statement.

Check that you have derivations for all 34 columns in the preceding Transformer stage. Check that all 34 columns appear on the input link of whatever stage type you're using to insert rows into Oracle.

In particular check whether anything strange is occurring with column number 21, that is, CW_TELEPHONE_DISP.

Check the code generated by compiling the Transformer stage to make sure that all the output column derivations are accounted for.

Run the job in Debugger so as to watch the row being constructed.

chulett · Post by **chulett** » Mon Aug 29, 2005 8:41 pm

ray.wurlod wrote:For whatever reason, only columns up to an including CW_TELEPHONE (that is, the first 20 columns) are being used in the INSERT statement.

Unless something has changed since it was originally posted, there are 34 column names referenced in the Insert statement and 34 distinct parameter markers as well.

If you are basing that assertation off the 'Value of the row is' log message, they are always truncated after a certain point. For long data values, many fields or a combination of both, you are usually out of luck getting anything useful from that row unless it's got a seat near the front of the bus.

Why do you say "The error is occuring on the insert side I guess." - how many other sides are there in the job? Why aren't you sure? Is there an Update or some other link as well that you haven't shown us?

ray.wurlod · Post by **ray.wurlod** » Mon Aug 29, 2005 11:45 pm

The INSERT statement in the error message reports only 20 columns.