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...
CHUNKING DATA_OWNER FACT_TABLE
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)
then
return;
end if;
Simple right?! The incremental functionality now terminates early if there is no delta to process, exactly as it should.
-i
