In Oracle Stage i coded as update or insert but its not inse

vijaydev · Post by **vijaydev** » Mon Jul 09, 2007 11:57 pm

In Oracle Stage i coded user defied sql as update or Insert, but its not inserting the records, can any one help in this regard.

This is my sql query

UPDATE #$SCHEMA_DWH#.DWH_S3_TAHGRP SET END_ACT_CD=:9,AVG_SALARY_AMT=:10,TOT_SALARY_AMT=:11,HIGH_SALARY_AMT=:12,SALARY_BAND_CD=:13,SALARY_FREQ_CD=:14,CATEGORY_NO=:15,PRINT_SEQ_NO=:16,ELIG_TXT=:17,NO_OF_EMPS=:18,VEH_MAKE_CD=:19,VEH_YEAR=:20,VEH_MODEL_CD=:21,VEH_LIC=:22,VEH_CAPACITY=:23,VEH_ENG_CAPACITY=:24,SI_UNIT=:25,MAX_UNIT=:26,SOURCE_SYSTEM_ID=:27,USERID_CD=:28,UPDATE_DATE=TO_DATE(:29, 'YYYY-MM-DD HH24:MI:SS'),DESTINATION_CD=:30,CONVEYANCE_CD=:31,TRIP_DURATION_DAYS=:32,NO_TRIPS_CNT=:33,ADDTL_EMP_ENDRSMT=:34 WHERE POLICY_NO=:1 AND POL_OFFICE_CD=:2 AND CERTIFICATE_NO=:3 AND RENL_CERT_NO=:4 AND EFF_DT_SEQ_NO=:5 AND LOCATION_NO=:6 AND SECT_OBJ_NO=:7 AND RISK_OBJ_NO=:8;
INSERT INTO #$SCHEMA_DWH#.DWH_S3_TAHGRP (POLICY_NO,POL_OFFICE_CD,CERTIFICATE_NO,RENL_CERT_NO,EFF_DT_SEQ_NO,LOCATION_NO,SECT_OBJ_NO,RISK_OBJ_NO,END_ACT_CD,AVG_SALARY_AMT,TOT_SALARY_AMT,HIGH_SALARY_AMT,SALARY_BAND_CD,SALARY_FREQ_CD,CATEGORY_NO,PRINT_SEQ_NO,ELIG_TXT,NO_OF_EMPS,VEH_MAKE_CD,VEH_YEAR,VEH_MODEL_CD,VEH_LIC,VEH_CAPACITY,VEH_ENG_CAPACITY,SI_UNIT,MAX_UNIT,SOURCE_SYSTEM_ID,USERID_CD,UPDATE_DATE,DESTINATION_CD,CONVEYANCE_CD,TRIP_DURATION_DAYS,NO_TRIPS_CNT,ADDTL_EMP_ENDRSMT) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,TO_DATE(:29, 'YYYY-MM-DD HH24:MI:SS'),:30,:31,:32,:33,:34)

Thanks in Advance

ArndW · Post by **ArndW** » Tue Jul 10, 2007 12:08 am

You need to code a reject link in a transform stage right before your oracle stage and capture your error.

vijaydev · Post by **vijaydev** » Tue Jul 10, 2007 12:25 am

Its not rejecting any records and No warning massages also

ArndW · Post by **ArndW** » Tue Jul 10, 2007 1:15 am

Did you put a reject link in to measure this? If not, you won't know. If the records are NOT being written to the table then they will go down the reject link

vijaydev · Post by **vijaydev** » Tue Jul 10, 2007 1:19 am

Previously i didint put the reject link , after knowing from you i put the rejected link and again a run the job with some date now its not rejecting the records in second time run, previously also same thing if we run second time we can get that records, we are not able to find out root casue of this..

vijaydev · Post by **vijaydev** » Tue Jul 10, 2007 1:20 am

Previously i didint put the reject link , after knowing from you i put the rejected link and again a run the job with some date now its not rejecting the records in second time run, previously also same thing if we run second time we can get that records, we are not able to find out root casue of this..

we have more than 200 jobs it will run daily, any other way to slove this problem, or we have to use first we have to get the rejected rows and than again we have to develop some more jobs to load the records any automated way is there

ArndW · Post by **ArndW** » Tue Jul 10, 2007 1:47 am

Your post confused me. Even after re-reading. To debug your problem, simplify the issue. Use 1 row of data, truncate the table and run the job with a reject link. Is the row inserted (check using your favorite tool) and if not, is anything written doen the reject link in the job or visible in the log file?

chulett · Post by **chulett** » Tue Jul 10, 2007 6:38 am

Like Arnd said... um, what?

Have you verified that you do indeed have a problem? That inserts should have in fact have been performed on your data? Do you understand how these 'dual action' actions work?

I personally despise them. To me they are a crutch supporting bad job designs and should never be used. The first action has to fail for the second action to be triggered. Never mind the fact that you have no idea when the job ends which action was actually performed.

Keep in mind the fact that a 'failed' Oracle update - i.e. one that updates zero records - will not generate an error and so thus there will not be anything for your reject link to capture.

Take Arnd's advice and simplify things. Run one record into an empty table and verify that it gets inserted. Tweak the input record slightly so you can recognize an update when you see one and run the job again, verify that it does indeed get updated.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 10, 2007 11:35 am

Check that you have both UPDATE and INSERT table privileges. Though if you do not I would expect warnings to have been logged.

Were there any? You didn't say.