Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

I've been updating some of my previous PL/SQL scripts at work only to notice during my testing that I was getting the error: ORA-29494: invalid state for run task error when I tried to run certain tests. This had me stumped for a while because none of the functionality that was to do with chunking was changed. Turns out I was just doing something stupid, here's what happened.

The functionality I was testing does an incremental update of a large fact table based on the delta between the last time my procedure ran to now. Lo and behold, the test environment had no effective delta, so the driving table for my procedure had no rows and therefore create_chunks_by_number_col() could not create any chunks!

I checked the status of my chunking like so:
select status, table_owner, table_name from USER_PARALLEL_EXECUTE_TASKS;

...with the following results...

It looked like the job was stuck in the CHUNKING state, but there was no error.

The solution to this is to check if there is any data in the driving table that is used for chunking before calling create_chunks_by_number_col(). Since my driving table is passed to my procedure as a VARCHAR2, I had to use a ref cursor to check this.

First I declared a ref cursor and a counter variable:
type b_cursor is ref cursor;
cur b_cursor;
cnt number;

Then, I added a simple check to see if there is some data. I only need to check for existence of a single value here, no need to do a full table count!
open cur for 'select count(1) from ' || drv_table || ' where rownum=1';
fetch cur into cnt;
close cur;
if (cnt = 0)
end if;

Simple right?! The incremental functionality now terminates early if there is no delta to process, exactly as it should.


Have comments or feedback on what I wrote? Please share them below!
comments powered by Disqus
Other posts you may like...