Igor's Blog

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...
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

, , ,
About — I'm an enthusiastic software engineer and consultant interested in many fields including J2EE, programming, electronics, 3D printing, video games, wood working and gardening.
See my Resume for more information.
The views expressed in this blog are my own and not those of my employer.
comments powered by Disqus
My other posts you may like...
Programming, DIY, Games, Hacks, Tech and more.
Follow me on...
Current and Past Projects
See my Resume

Subscribe


RSS Feed

My Other Web Sites

Igor and Elise's Travels
Riverside Expressway Cam
StrFunc() Online
300 George St Blogumentary
Guru JSON-RPC Tester
Extrudifier Object Designer

Recent Blog Posts

Binding extra variables with PHP PDO returns no results

New Nintendo 3DS XL, R4i SDHC RTS Lite and DSDoom

Older versions of JD-GUI crashing on macOS Sierra

How does Seeed Fusion stack up as a low budget PCB fab

Where to find the last item (Robbie's lost lyrics) in GravityFalls Lake level

Detecting print requests to print contents of a DIV with JavaScript

Using Google DFP with AdSense on responsive pages

How to change the email subject line in Gmail

Force PHP GD library to load JPEG images that have invalid data

How to uninstall Samsung Smart Switch from your Mac

Recent Galleries

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

DIY spare parts computer build with a RAIDMAX Anura case

Fake 'Lepin' brand Lego packaging

Hardwood garden bench with clear resin void filler

Fixing a 3D printer extruder that stopped heating up

Easily increase disk space in a Lenovo Ideapad 100S 14" laptop with an M.2 SSD

Making a multi-piece 3D printed solder spool holder stand

DIY indoor apartment grow light wiring

Good Friday Electronics fun Easter Bunny LED PCB Kit IBEABU-01.0

Top Categories

Blogs I follow

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth

Friends

RAWS Parts Online
Alpha Dimensions Hosting
Kristensen Photography
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity
Follow me on... 
     
...or subscribe for updates!

Don't show this again