Issu with User Generated SQL DS6.0R3

zam62 · Post by **zam62** » Fri Sep 26, 2003 12:50 pm

I have been having a problem with an insert using User Defined SQL. When I perform the INSERT, I get "EDWItem2Dim..ITEM_INSERT: ORA-01008: not all variables bound". I changed the job to use "Insert Rows Without Clearing" and it worked fine. Without changing anything else, I copied the SQL from the generated Tab and pasted it into the "User Defined" tab. I then changed the job to use "User Defined SQL"
and got the same error "EDWItem2Dim..ITEM_INSERT: ORA-01008: not all variables bound"
Why would it work in Generated and not in User Defined???

Here is the SQL that I Cut and Pasted:
INSERT INTO EDW_ADMIN.ITEM (ITEM_CODE,ITEM_DESC,ITEM_GROUP_NBR,STORAGE_TYPE_CODE,C_BRACKET_PRICE,PACK_AMT,CATCH_WEIGHT_FLAG,LESS_CASE_FLAG, CASE_YIELD,KOSHER_FLAG,CN_FLAG,SHELF_LIFE,THAW_INSTRUCTION,PREPARE_INSTRUCTION,MRKTING_INSTRUCTION,AVAIL_US_FLAG, AVAIL_CANADA_FLAG,ITEM_GROUP_SEQ,GFS_BRAND_FLAG,DELETED_FLAG,SPECIAL_ORDER_FLAG,NEW_ITEM_FLAG,STATUS_CODE, STATUS_DATE,LC_PACK_TYPE_DESC,SELLING_UNIT,ITEM_TYPE_NBR,COMMISSION_CODE,ITEM_INTERNAL_DESC,PRIME_VENDOR_NBR, PRODUCE_BONUS_CODE,PRICE_CHANGE_FREQUENCY_CODE,NET_WEIGHT_AMT,PACKER_NAME,ITEM_2_DESC,NO_PRINT_FLAG, ITEM_PRICE_GROUP_NBR,PORTIONS_PER_CASE_QTY,FINISH_PORTIONS_PER_CASE_QTY,UNIT_OF_MEASURE_CODE,ADDITIONAL_INFO_FLAG, RETAIL_REQUIRED_STK_IND,COMPETITIVE_MARKETING_INFO_TXT,STOCKING_CODE,VENDOR_ITEM_CODE,BAR_CODE, SALES_REPORT_BAR_CODE,UNIT_CUBE_AMT,GROSS_UNIT_WEIGHT_AMT,PRIVATE_LABEL_CODE,PRIVATE_LABEL_DESC,BRAND_NAME) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,TO_DATE(:24, 'YYYY-MM-DD HH24:MI:SS'),:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52)

ray.wurlod · Post by **ray.wurlod** » Sat Sep 27, 2003 12:42 am

The error means that the number of columns provided in your DataStage job (column metadata) is fewer than the number of parameter markers in the SQL.
This may occur, for example, if your DataStage job provides a key column but the user-defined SQL relies upon a sequence to provide the key value in the INSERT statement.
Check that the number of columns in your column metadata, the number of columns in the column-list clause of your INSERT statement, and the number of parameter markers in your VALUES clause are all identical.

zam62 · Post by **zam62** » Mon Sep 29, 2003 7:11 am

I cut the INSERT statement from the Fully Generated statement that DS create and worked. I then pasted it into the User Defined Portion of the stage and got the error. I am fairly certain that all the columns match.

I also ran the statement in TOAD and it worked fine. IS there something in the user defined portion that I am missing?

ray.wurlod · Post by **ray.wurlod** » Mon Sep 29, 2003 5:41 pm

Does your DataStage job deliver exactly the same number of columns (52) as there are parameter markers in the query? A mismatch here is the most usual cause of "parameter not bound" messages being generated.

vmcburney · Post by **vmcburney** » Mon Sep 29, 2003 6:47 pm

The number of columns matches the number of variables. I cut and pasted the statement to MS Word, replaced the , with ^t, then cut and pasted it to MS Excel where the column names were aligned with the column values. Everything looks okay. I don't know why it doesn't work, sorry.

kcbland · Post by **kcbland** » Mon Sep 29, 2003 9:02 pm

Are you using Oracle 9 or 8 OCI?

Maybe the order of your steps is what is in question. To recap:

<pre>
I cut the INSERT statement from the Fully Generated statement that DS create and worked. I then pasted it into the User Defined Portion of the stage and got the error. I am fairly certain that all the columns match.

</pre>

For 9i

1. It works in generated.
2. You highlighted the SQL from the Generated SQL tab and pressed Ctrl-C
3. You pasted it into the User-Defined SQL tab portion.

Stop right there. The box should be a grey background. You CAN'T paste anything here.

4. You should now switch to User-Defined SQL.
5. NOW paste it into the User-Defined box.

For 8i

1. It works in generated.
2. You highlighted the SQL from the Generated SQL tab and pressed Ctrl-C
3. You pasted it into the User-Defined SQL tab portion.

Stop right there. There's no way you get get to the User-Defined tab as it's grey. You CAN'T have pasted anything here.

4. You should now switch to User-Defined SQL.
5. NOW paste it into the User-Defined box.

If you're using 9i, I suspect you think you pasted in the SQL, but what's actually there is residual SQL. This is a flaky thing DS does. If a job has custom SQL, but you now use Generated, that custom SQL lurks in the background forever.

Just a shot in the dark.

zam62 · Post by **zam62** » Tue Sep 30, 2003 7:32 am

Maybe I should have been more clear on the procedure since it seemed to have been muddy.

1. Used "Insert Rows Without Clearing" and it worked.
2. Highlighted SQL in that Tab.
3. Opened the "general" tab.
4. Changed the "Insert Rows Without Clearing" in the Update Action dropdown to "User Defined SQL"
5. Opened the "SQL" tab.
6. Opened the "User Defined" tab there.
7. Pasted the Copied SQL from step 2 into that textarea.
8. Compiled and ran the job.
9. Got "Not all Variables Bound" error.

Anyway, Ascential sent over a new OCI8 package and I unregistered the old and reinstalled the new.
Problem resolved.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 30, 2003 11:38 pm

Vincent,
I really had in mind were there the same number of columns in the Columns grid in the DataStage job. I too perceived that the number of columns in the SQL matched the number of parameter markers. If you subsequently changed the number of columns in the job design, this could lead to the error message that was reported.
Regards,
Ray

DSXchange

Issu with User Generated SQL DS6.0R3

Issu with User Generated SQL DS6.0R3

Issu with User Generated SQL DS6.0R3 [RESOLVED]