Error:- ORA-01008: not all variables bound from jobs
Moderators: chulett, rschirm, roy
Error:- ORA-01008: not all variables bound from jobs
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Ray,
This is the generated sql
I am sorry but I was unable to understand your first line .
The error is occuring on the insert side I guess. The error warning is The key is cw_status_form_num.
Thanks,
Ash.
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)
Code: Select all
Is the generated SQL taking the key columns out into the WHERE clause leaving a mismatch?
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
Thanks,
Ash.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.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.
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: