Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Eyad.

Asked: March 01, 2023 - 11:01 am UTC

Last updated: March 09, 2023 - 4:34 pm UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

Hello,

A PTF is used to pivot rows into columns. But these columns are described in a table. Once data in this table changes, the PTF does not reflect correctly. It seems to be cashing the describe results somehow ...

We have the following tables:
- PROPS(id, name, ord): used to store pivot columns.
- VECTORS(id, notes): stores the final pivot rows.
- VECTORS_DETAILS(vector_id, prop_id, val): is for pivot data (i.e.: cartesian product of PROS and VECTORS).


Statement 14: shows the initial result of the PTF.
Statement 15: inserts a new property.
Statement 16: show the describe procedure being called, and PTF working fine.
Statement 17: still working fine...
Statement 18: Old data returned!

Running the PTF again in the following statements results in random behavior.

If the package is enforce-recompiled, everything works fine, until PROPS data change again.

Thank you.

with LiveSQL Test Case:

and Chris said...

There are a couple of issues here. The main takeaway is "dynamic" pivots using PTFs only work if you can hard parse queries when you modify data that change the columns in the output at the same time.

First up, looking at the Live SQL link statement 16 onwards return null for all columns. I see this effect too; I'm unsure how you're getting this to be "working fine" from this point given the data shown.

This problem arises from the query in fn_val. After inserting the props 10 row this raises NO_DATA_FOUND because there's no matching row in vectors_details.

When you call PL/SQL functions that raise NO_DATA_FOUND in SQL, the database swallows up the error:

create or replace function ndf
  return int as
begin
  raise no_data_found;
  return 1;
end;
/

select ndf from dual;

NDF       
   <null> 


So what's happening is the fetch_rows procedure is stopping when it calls fn_val for property 10. This because more apparent if we add an exception handler for this and raise a different error:

create or replace package body pkg_ptf is 
 
function fn_val(p_vector_id number, p_prop_id number) return varchar2 
as 
  l_result vectors_details.val%type; 
begin 
 
  select val 
  into l_result 
  from vectors_details 
  where 
    vector_id = p_vector_id 
    and prop_id = p_prop_id; 
 
  return l_result; 
end; 
 
 
function describe(tab in out dbms_tf.table_t) return dbms_tf.describe_t 
as  
  cols dbms_tf.columns_new_t; 
  i pls_integer := 1; 
begin 
  dbms_output.put_line('pkg_ptf.describe'); 
 
  -- hide notes, just for the sake of it: 
  for i in 1 .. tab.column.count loop 
      tab.column(i).pass_through := tab.column(i).description.name != '"NOTES"'; 
      tab.column(i).for_read := tab.column(i).description.name != '"NOTES"'; 
  end loop; 
 
  -- add props (pivot): 
  for l_rec in (select name from props order by ord) 
  loop 
      cols(i) := dbms_tf.column_metadata_t(name => l_rec.name, type => dbms_tf.type_varchar2); 
      i := i + 1; 
  end loop; 
 
  return dbms_tf.describe_t(new_columns => cols); 
end; 
 
 
procedure fetch_rows 
as 
  l_rowset dbms_tf.row_set_t; 
  l_vals_rowset dbms_tf.row_set_t; 
   
  l_row_count binary_integer; 
  l_col_count binary_integer; 
   
  l_vector_id_col binary_integer := 1; 
   
  l_col binary_integer; 
begin 
  -- fetch rowset: 
  dbms_tf.get_row_set(l_rowset, l_row_count, l_col_count); 
  
  for l_props_rec in (select id from props order by ord) 
  loop 
    for l_row in 1 .. l_row_count 
    loop 
      l_col := l_props_rec.id; 
      l_vals_rowset(l_col).tab_varchar2(l_row) := 
        fn_val(l_rowset(1).tab_number(l_row), l_props_rec.id); 
    end loop; 
  end loop; 
 
  dbms_tf.put_row_set(l_vals_rowset); 
exception
  when no_data_found then 
    raise_application_error ( -20001, q'!Oopsie, there's no data!' );
end; 
 
end; 
/

select * from pkg_ptf.fn_vectors_list(vectors);
/*
ID    PROP1     PROP2     PROP3     PROP4     PROP5     PROP6     PROP7     PROP8     PROP9     
    1 val 11    val 12    val 13    val 14    val 15    val 16    val 17    val 18    val 19    
    2 val 21    val 22    val 23    val 24    val 25    val 26    val 27    val 28    val 29    
    3 val 31    val 32    val 33    val 34    val 35    val 36    val 37    val 38    val 39  
*/
insert into props values (10, 'props10', 10);

select * from pkg_ptf.fn_vectors_list(vectors);

--ORA-20001: Oopsie, there's no data


We can fix this by adding a NDF handler in the function.

create or replace package body pkg_ptf is 
 
function fn_val(p_vector_id number, p_prop_id number) return varchar2 
as 
  l_result vectors_details.val%type; 
begin 
 
  select val 
  into l_result 
  from vectors_details 
  where 
    vector_id = p_vector_id 
    and prop_id = p_prop_id; 
 
  return l_result; 
exception
  when no_data_found then 
    return null;
end; 
 
 
function describe(tab in out dbms_tf.table_t) return dbms_tf.describe_t 
as  
  cols dbms_tf.columns_new_t; 
  i pls_integer := 1; 
begin 
  dbms_output.put_line('pkg_ptf.describe'); 
 
  -- hide notes, just for the sake of it: 
  for i in 1 .. tab.column.count loop 
      tab.column(i).pass_through := tab.column(i).description.name != '"NOTES"'; 
      tab.column(i).for_read := tab.column(i).description.name != '"NOTES"'; 
  end loop; 
 
  -- add props (pivot): 
  for l_rec in (select name from props order by ord) 
  loop 
      cols(i) := dbms_tf.column_metadata_t(name => l_rec.name, type => dbms_tf.type_varchar2); 
      i := i + 1; 
  end loop; 
 
  return dbms_tf.describe_t(new_columns => cols); 
end; 
 
 
procedure fetch_rows 
as 
  l_rowset dbms_tf.row_set_t; 
  l_vals_rowset dbms_tf.row_set_t; 
   
  l_row_count binary_integer; 
  l_col_count binary_integer; 
   
  l_vector_id_col binary_integer := 1; 
   
  l_col binary_integer; 
begin 
  -- fetch rowset: 
  dbms_tf.get_row_set(l_rowset, l_row_count, l_col_count); 
  
  for l_props_rec in (select id from props order by ord) 
  loop 
    for l_row in 1 .. l_row_count 
    loop 
      l_col := l_props_rec.id; 
      l_vals_rowset(l_col).tab_varchar2(l_row) := 
        fn_val(l_rowset(1).tab_number(l_row), l_props_rec.id); 
    end loop; 
  end loop; 
 
  dbms_tf.put_row_set(l_vals_rowset); 
exception
  when no_data_found then 
    raise_application_error ( -20001, q'!Oopsie, there's no data!' );
end; 
 
end; 
/

select * from pkg_ptf.fn_vectors_list(vectors);
/*
ID    PROP1     PROP2     PROP3     PROP4     PROP5     PROP6     PROP7     PROP8     PROP9     PROPS10    
    1 val 11    val 12    val 13    val 14    val 15    val 16    val 17    val 18    val 19    <null>     
    2 val 21    val 22    val 23    val 24    val 25    val 26    val 27    val 28    val 29    <null>     
    3 val 31    val 32    val 33    val 34    val 35    val 36    val 37    val 38    val 39    <null>
*/
insert into props values (11, 'props11', 11);

select * from pkg_ptf.fn_vectors_list(vectors);
/*
ID    PROP1     PROP2     PROP3     PROP4     PROP5     PROP6     PROP7     PROP8     PROP9     PROPS10    
    1 val 11    val 12    val 13    val 14    val 15    val 16    val 17    val 18    val 19    <null>     
    2 val 21    val 22    val 23    val 24    val 25    val 26    val 27    val 28    val 29    <null>     
    3 val 31    val 32    val 33    val 34    val 35    val 36    val 37    val 38    val 39    <null>   
*/


Now we're getting null for the new column (as expected). But adding a new property (intending to get a new column) still returns the original columns. PROPS11 is missing.

So why is this?

The shape of the result set for PTFs - which columns it includes - is decided at (hard) parse time. There's no reason to do a hard parse of the PTF query after inserting a row into PROPS. So the database doesn't and you still have the original columns.

Recompiling the package forces a hard parse. At this point, the PTF adds the new column.

So using PTFs to do a dynamic pivot is only really feasible if:

- The source data determining the columns change rarely
- You can invalidate any cursors based on the PTF and source table if/when you change the data

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

So, no way to force a hard parse ...

Eyad, March 01, 2023 - 3:26 pm UTC

Thank you for the answer, and thank you for the NDF correction!

I hoped for a way to recall the describe procedure, automatically or even manually… Perhaps in the future?

Again thank you very much.
Chris Saxon
March 03, 2023 - 11:20 am UTC

There are ways - we've seen recompiling the package can do this - but hard parsing uses lots of resources so is something you want to avoid as much as possible.

You can remove specific plans from the shared pool:

https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9542428300346881538

This will force a hard parse next time the statement executes. You need to ensure you've captured all the statements using the PTF using the table with data changes.

You could also gather stats on the table with no_invalidate => false to invalidate all cursors on the table; though this affects all queries on the table so is generally a bad idea.

I'm getting ORA-00904: "DEPT": invalid identifier

A reader, March 02, 2023 - 7:46 am UTC

Hi,

I tried to check the Polymorphic Table Functions feature.
We're on
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

I followed the very 1st example on
https://oracle-base.com/articles/18c/polymorphic-table-functions-18c

but

SELECT * FROM my_ptf(dept);

returns ORA-00904: "DEPT": invalid identifier.

Should this functionality be installed separately or it's also a part of oracle 19? I was able to compile package and function from the tutorial just fine.

Chris Saxon
March 03, 2023 - 11:21 am UTC

See the comment below - do you have a DEPT table in your schema?

to the above follow up on ORA-00904

Rajeshwaran Jeyabal, March 03, 2023 - 8:17 am UTC

when you get "ORA-00904", can you check if the object exists in your schema?

demo@ATP19C> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

demo@ATP19C> SELECT * FROM my_ptf(dept);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

demo@ATP19C> rename dept to dept1;

Table renamed.

demo@ATP19C> SELECT * FROM my_ptf(dept);
SELECT * FROM my_ptf(dept)
                     *
ERROR at line 1:
ORA-00904: "DEPT": invalid identifier


demo@ATP19C> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.1.0


demo@ATP19C>


Chris Saxon
March 03, 2023 - 11:21 am UTC

Indeed

A reader, March 06, 2023 - 7:20 am UTC

Yes, the table exists.

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 6 09:17:13 2023
Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Mon Mar 06 2023 09:16:38 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

SQL> CREATE TABLE DEPT (
  2    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  3    DNAME VARCHAR2(14),
  4    LOC VARCHAR2(13)
  5  ) ;

Table created.

SQL> CREATE TABLE EMP (
  2    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  3    ENAME VARCHAR2(10),
  4    JOB VARCHAR2(9),
  5    MGR NUMBER(4),
  6    HIREDATE DATE,
  7    SAL NUMBER(7,2),
  8    COMM NUMBER(7,2),
  9    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
 10  );

Table created.

SQL> INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

1 row created.

SQL>
SQL> INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE PACKAGE poly_pkg AS
  2
  3    FUNCTION describe (tab IN OUT DBMS_TF.table_t)
  4      RETURN DBMS_TF.describe_t;
  5
  6  END poly_pkg;
  7  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY poly_pkg AS
  2
  3    FUNCTION describe (tab IN OUT DBMS_TF.table_t)
  4      RETURN DBMS_TF.describe_t
  5    AS
  6    BEGIN
  7      RETURN NULL;
  8    END;
  9
 10  END poly_pkg;
 11  /

Package body created.

SQL> CREATE OR REPLACE FUNCTION my_ptf(tab IN TABLE)
  2    RETURN TABLE PIPELINED
  3    ROW POLYMORPHIC USING poly_pkg;
  4  /

Function created.

SQL> SELECT table_name FROM user_tables where table_name='DEPT';

TABLE_NAME
--------------------------------------------------------------------------------
DEPT

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> SELECT * FROM my_ptf(dept);
SELECT * FROM my_ptf(dept)
                     *
ERROR at line 1:
ORA-00904: "DEPT": invalid identifier

SQL>

Chris Saxon
March 06, 2023 - 2:46 pm UTC

As Rajesh says below, take this up with support. I'm unable to reproduce this.

may be you need to work with support on this.

Rajeshwaran Jeyabal, March 06, 2023 - 12:29 pm UTC

demo@ATP19C> CREATE OR REPLACE PACKAGE poly_pkg AS
  2
  3   FUNCTION describe (tab IN OUT DBMS_TF.table_t)
  4     RETURN DBMS_TF.describe_t;
  5
  6  END poly_pkg;
  7  /

Package created.

demo@ATP19C> CREATE OR REPLACE PACKAGE BODY poly_pkg AS
  2
  3   FUNCTION describe (tab IN OUT DBMS_TF.table_t)
  4     RETURN DBMS_TF.describe_t
  5   AS
  6   BEGIN
  7     RETURN NULL;
  8   END;
  9
 10  END poly_pkg;
 11  /

Package body created.

demo@ATP19C> CREATE OR REPLACE FUNCTION my_ptf(tab IN TABLE)
  2  RETURN TABLE PIPELINED
  3  ROW POLYMORPHIC USING poly_pkg;
  4  /

Function created.

demo@ATP19C> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

demo@ATP19C> select * from my_ptf(dept);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

demo@ATP19C> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.1.0


A reader, March 09, 2023 - 2:45 pm UTC

The reason for ORA-00904 error was that compatible for the db was set to 12.1.0.2. After removing it, the problem went away.
Chris Saxon
March 09, 2023 - 4:34 pm UTC

That would explain it! :) Thanks for letting us know

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library