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:
...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:
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!
Simple right?! The incremental functionality now terminates early if there is no delta to process, exactly as it should.