Issue with Post SQL execution in OCI

kumarjit · Post by **kumarjit** » Tue Dec 17, 2013 7:54 am

Hi there ,

Been a long time since I'd posted last in this forum. Hope you all are doing well .

I came with a pretty odd hitch that we faced in one of our parallel jobs.
But the real trouble is, the same design is used in multiple other jobs as well, hence an error here would mean that changes are required in all the other jobs also.

The job consists of three parallel data load flows, and the generic structure for all these loads are as follows:

Code: Select all

SEQUENTIAL FILE --> TRANSFORMER -- > ORACLE CONNECTOR

We have Before and After SQL sections defined for all the three OCI stages, but surprisingly the After SQL execution for the SECOND OCI is skipped, and overall job is said to complete successfully , as per the log .
I ran the After SQL statement independently and it ran fine, but somehow when fired as a post sql from the OCI stage it is getting skipped.

Any idea as to how to go forth in finding the root cause of this problem?

Regards
Kumarjit.

chulett · Post by **chulett** » Tue Dec 17, 2013 8:36 am

Can we get a clue as to what the after SQL actually is? I'd also be curious what makes you think it is being skipped.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 17, 2013 2:40 pm

Are there any settings in the second Connector that might cause the After-SQL command to be skipped?

Is the after-SQL properly formed?

kumarjit · Post by **kumarjit** » Wed Dec 18, 2013 2:15 am

@craig : Sure , its something like the one below :
BEGIN
BEGIN
/*Script for creating index on a table whose name is a Job-level parameter*/
END ;
/*Script to Update the table based on a specific criteria*/
END ;

Surprisingly, this code seems to be doing fine when run from a sql client tool like SQL developer/Toad , but when its run as a post sql from DS job it DOESNT get invoked at all , and the reason why I'm saying this is is I didnt find any entries like "Executing the After SQL...." within the "post run....." section of the job log .

@ray : Absolutely no idea .......

chulett · Post by **chulett** » Wed Dec 18, 2013 8:24 am

What happens if you change it to two anonymous blocks one after the other rather than embedding them like that? Or run both scripts inside one anonymous block?

kumarjit · Post by **kumarjit** » Fri Dec 20, 2013 3:51 am

@craig : As I mentioned in the job design , there are three parallel data flows , each of which ending in an OCI stage , the surprise is that the same block is not getting executed (or skipped as I had been saying all this long) if used as a post sql for the second OCI , but when invoked from the first or the third OCI , it runs fine .
As far as splitting up the two blocks is concerned , I didnt give it a try.

Regards
Kumarjit.

chulett · Post by **chulett** » Fri Dec 20, 2013 8:36 am

Then it seems to me you are firmly in the hands of your support provider.

kumarjit · Post by **kumarjit** » Thu Dec 26, 2013 1:37 am

Anyways , thanks for your time people....

Regards
Kumarjit.