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.


A quick disclaimer...

Although I put in a great effort into researching all the topics I cover, mistakes can happen. Use of any information from my blog posts should be at own risk and I do not hold any liability towards any information misuse or damages caused by following any of my posts.

All content and opinions expressed on this Blog are my own and do not represent the opinions of my employer (Oracle). Use of any information contained in this blog post/article is subject to this disclaimer.
Hi! You can search my blog here ⤵
NOTE: (2022) This Blog is no longer maintained and I will not be answering any emails or comments.

I am now focusing on Atari Gamer.