{"id":1953,"date":"2023-12-20T15:55:22","date_gmt":"2023-12-20T13:55:22","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1953"},"modified":"2023-12-26T14:05:44","modified_gmt":"2023-12-26T12:05:44","slug":"beware-of-no_data_found-in-your-ptf","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/beware-of-no_data_found-in-your-ptf\/","title":{"rendered":"Beware of NO_DATA_FOUND in your PTF!"},"content":{"rendered":"\n

A few days ago I received a comment\/question on the older post about dynamically transposing rows to column with Polymorphic Table Functions (PTF)<\/a>. Back then I overlooked a bug in the example code, but the explanation takes a bit longer, so i decided to write a new post about it. The PTF was working initially but after inserting new data started to return wrong results – all NULL’s for some columns where we know there are actually values present. So, what’s going on?<\/p>\n\n\n\n\n\n\n\n

For Rafal, who asked the question, the result is looking like this:<\/p>\n\n\n

\nSQL> INSERT INTO t (empno, key_name, key_val_char) VALUES(7839, 'NEW_COLUMN', 'NEW_VALUE');\n\n1 row inserted.\n\nSQL> SELECT * FROM keyval2tab(t PARTITION BY empno,'T');\n\nEMPNO DEPTNO SAL HIREDATE NEW_COLUMN COMM JOB MGR ENAME\n________ _________ _______ ___________ _____________ _______ ____________ ______ ________\n7369 20 800 80\/12\/17\n7499 30 1600 81\/02\/20\n7521 30 1250 81\/02\/22\n7566 20 2975 81\/04\/02\n7654 30 1250 81\/09\/28\n7698 30 2850 81\/05\/01\n7782 10 2450 81\/06\/09\n7788 20 3000 87\/04\/19\n7839 10 5000 81\/11\/17 NEW_VALUE PRESIDENT KING\n7844 30 1500 81\/09\/08\n7876 20 1100 87\/05\/23\n7900 30 950 81\/12\/03\n7902 20 3000 81\/12\/03\n7934 10 1300 82\/01\/23\n\n14 rows selected.\n<\/pre><\/div>\n\n\n
Listing 1<\/em><\/strong><\/p>\n\n\n\n
All columns after HIREDATE<\/strong> are NULL in all rows except of the row where the value for the NEW_COLUMN<\/strong> is present. Before we look at the code of the PTF to find the reason, let’s look at two important points.<\/p>\n\n\n\n
How SQL handles NO_DATA_FOUND<\/h4>\n\n\n\n
Consider following example:<\/p>\n\n\n
\nSQL> create or replace function my_pipeline return  sys.odcivarchar2list pipelined is\n  2  begin\n  3    for i in 1..5 loop\n  4      if i = 3 then \n  5        raise no_data_found;\n  6      end if;\n  7      pipe row (i);\n  8    end loop;\n  9  end;\n 10  \/\n\nFunction MY_PIPELINE compiled\n\nSQL> \nSQL> \nSQL> select column_value from my_pipeline();\n\nCOLUM\n-----\n1\n2\n<\/pre><\/div>\n\n\n
Listing 2<\/em><\/strong><\/p>\n\n\n\n
Although we throw an exception in the function, we do not get an error at the SQL level. The processing stops there, but, nevertheless, we get the first two rows. This behavior is not unexpected and is described for example in this thread on AksTom<\/a>. <\/p>\n\n\n\n
I go along with that, but the next example is less easy for me to follow (but that is how it is  \ud83d\ude42 )<\/p>\n\n\n
\nSQL> create or replace function my_lower (p_str varchar2) return varchar2 is\n  2  begin\n  3    raise no_data_found;\n  4    return lower (p_str);\n  5  end;\n  6  \/\n\nFunction MY_LOWER compiled\n\nSQL> \nSQL> \nSQL> select dummy\n  2  ,      my_lower(dummy) lower_dummy \n  3  from dual;\n\nD LOWER_DUMMY\n- -----------\nX            \n<\/pre><\/div>\n\n\n
Listing 3<\/em><\/strong><\/p>\n\n\n\n
If we want to return a PL\/SQL function result in a SQL and the function throws uncaught NO_DATA_FOUND<\/strong> exception then we will still get all rows but the corresponding columns will be NULL! This can for example happen when using recursive SQL inside a function or this can just as well happen when referencing non-existent associative array elements!<\/p>\n\n\n\n
Referencing non-existent associative array elements<\/h4>\n\n\n\n
Associative arrays (or INDEX BY tables) in PL\/SQL are sparse. They can have gaps, but referencing a non-existent element throws a NO_DATA_FOUND<\/strong> exception:<\/p>\n\n\n
\ndeclare \n    type t_tab is table of number  index by pls_integer;\n    v_tab t_tab;\nbegin    \n    v_tab(1) := 1;\n    v_tab(3) := 3;\n    dbms_output.put_line(v_tab(1)||v_tab(2)||v_tab(3));\nend;\nError report -\nORA-01403: no data found\nORA-06512: at line 7\n<\/pre><\/div>\n\n\n
Listing 4<\/em><\/strong><\/p>\n\n\n\n
Skipping the second element and assigning the third one was okay, but referencing the unassigned second element in line 7 throws an exception.<\/p>\n\n\n\n
What does it mean for PTF’s?<\/h4>\n\n\n\n
Looking at the package DBMS_TF we will see that most of the collections we have to use are declared as associative arrays:<\/p>\n\n\n
\n \/* Collections for each supported types *\/\n  TYPE TAB_VARCHAR2_T      IS TABLE OF VARCHAR2(32767)             INDEX BY PLS_INTEGER;\n  TYPE TAB_NUMBER_T        IS TABLE OF NUMBER                      INDEX BY PLS_INTEGER;\n  TYPE TAB_DATE_T          IS TABLE OF DATE                        INDEX BY PLS_INTEGER;\n\n...\n<\/pre><\/div>\n\n\n
Listing 5<\/em><\/strong><\/p>\n\n\n\n
We have to be careful not to access a non-existent element ourselves, but just as important not to hand over such sparse collections for further processing to the database. <\/p>\n\n\n\n
Let’s now look at the code of the PTF from the initial post<\/a> to see what I mean. What we are doing here is trying to transpose the rows with key names and key values back to the table structure. After we inserted one more key\/value pair for EMPNO=7839, we have 8 key for this EMPNO but still 7 keys for all others. It shouldn’t be a problem though. The DESCRIBE method will have to look into the data (that is the price we pay for being “dynamic”), gather all possible keys and therefore define that there will be 8 columns in the result set. So far so good.<\/p>\n\n\n\n
\"\"<\/a>
Figure 1: Key\/value data set to be transposed<\/figcaption><\/figure>\n\n\n