The previous post introduced PTF code to calculate totals for numeric columns. It’s pretty straightforward but has two flaws lurking within that I’ll cover in this post.
The first flaw produces incorrect results even though all of the code executes while the other produces incorrect results and causes code to be skipped.
First I’ll query from the PTF to remind you of the display including the totals.
SELECT * FROM ptf_poly_func(ptf_table); COL1 COL2 ---- ---- 10 100 20 200 30 300 "COL1" total is 60 "COL2" total is 600
And query from the underlying table.
SELECT * FROM ptf_table; COL1 COL2 ---- ---- 10 100 20 200 30 300
Incorrect Result – Full Code Execution
This is easy to demonstrate with NULL values. I’ll set one of the col2 values to NULL.
UPDATE ptf_table SET col2 = NULL WHERE col1 = 20;
And select from the PTF again.
SELECT * FROM ptf_poly_func(ptf_table); COL1 COL2 ---- ---- 10 100 20 30 300 "COL1" total is 60 "COL2" total is
Notice the label for the COL2 total is there but the total is not. That’s because if a NULL value is added to another value the result is NULL. So when the second record is processed it adds NULL to the total which makes it NULL. Then 300 is added to NULL which makes it even NULL’er. All of the code in the function executes fine though.
The fix is to change this line:
v_number := v_number + v_row_set(col_counter).tab_number(row_counter);
v_number := v_number + NVL(v_row_set(col_counter).tab_number(row_counter),0);
This ensures a value of zero is returned instead of a NULL.
Incorrect Results – Missed Code
This is best demonstrated by changing the datatype of COL1 to DATE.
CREATE TABLE ptf_table ( col1 DATE, col2 NUMBER ); BEGIN FOR x IN 1..3 LOOP INSERT INTO ptf_table VALUES(SYSDATE + x,x * 100); END LOOP; END;
Now query the PTF.
SELECT * FROM ptf_poly_func(ptf_table); COL1 COL2 --------- ---- 03-APR-19 100 04-APR-19 200 05-APR-19 300 "COL1" total is 0
Both the label for the COL2 total and the total itself are not there. This occurs because an un-handled exception was thrown in the FETCH_ROWS procedure. The exception was not trapped so the PTF returned the results it had up to that point. So where is the exception – let’s step through the code to find out. We know the XSTORE for COL1 was set to zero so these lines of code executed:
IF NOT DBMS_TF.XSTORE_EXISTS(v_get_cols(col_counter).name) THEN DBMS_TF.XSTORE_SET(v_get_cols(col_counter).name,0); END IF; DBMS_TF.XSTORE_GET(v_get_cols(col_counter).name,v_number);
Let’s add en exception handler to the next line to see if it is the problem.
BEGIN v_number := v_number + v_row_set(col_counter).tab_number(row_counter),0); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,sqlcode); END;
This does raise the exception.
ERROR at line 1: ORA-20000: 100 ORA-06512: at "D.QZ_POLY_PKG", line 60
The most troublesome part of this error is that every line of code after it in the original code did not execute even though a result was returned.
So what is wrong with this line of code?
-- Thankfully the problem code identified itself by -- turning bold red! v_number := v_number + v_row_set(col_counter).tab_number(row_counter);
COL1 is DATE so its values will be in the TAB_DATE structure not TAB_NUMBER. So accessing the empty TAB_NUMBER structure resulted in a NO_DATA_FOUND exception
The fix is to check if number values exist like this:
-- if nothing exists in TAB_NUMBER then this is not -- a NUMBER field so do not add to a total IF v_row_set(col_counter).tab_number.EXISTS(row_counter) THEN v_number := v_number + NVL(v_row_set(col_counter).tab_number(row_counter),0); END IF;
Be very careful of this when coding, testing and debugging PTF’s.
Thanks for reading!