Oracle 19C database issue

Question

I have a package working fine in 11g version.

But when I deploy the same package in 19c version, the behavior is different.

PFB the description.

Package specification has an cursor and created a table type with cursor%rowtype. Having a pipelined function which returns the table type.

Using the function with table clause

select * from table(function)

so that the return value can act as a table and I can read the result with column names.

In 11g, the function is returning the column headers same as the cursor column names. But in 19c, the function is returning column headers like 'Attr_1, Attr_2, etc'.

I need the function to return the column headers as the cursor columns names.

Note: Code can't be shared as it is very sensitive.

Sample: PFB the sample.

Create table tb_test (id number, description varchar2 (50));  

create or replace package pkg_test is 
    cursor cur_test is 
        select * 
        from tb_test 
        where 1=2; 
    type typ_cur_test is table of cur_test%rowtype; 
    function fn_test(p_rows in number) return typ_cur_test pipelined; 
end;

create or replace package body pkg_test is 
    function fn_test(p_rows in number) return typ_cur_test pipelined as 
    l_tab typ_cur_test := cur_typ_test(); 
    begin 
        for i in 1..p_rows loop l_tab.extend; 
            l_tab(i).Id := i; 
            l_tab(i). Description := 'test'; 
            pipe roe(l_tab(i)); 
        end loop; 
    return ; 
    end; 
end pkg_test;


Select * from table(pkg_test.fn_test(2));

In 11g, the above select gives column headers as "id, description", but in 19c i am getting as "ATTR_1, ATTR_2".

Please help.

Answer 1

Solution for your issue could be:

create or replace package pkg_test is 
    cursor cur_test is 
        select * 
        from tb_test 
        where 1=2; 
    type typ_cur_test is table of {tb_test}%rowtype; 
    function fn_test(p_rows in number) return typ_cur_test pipelined; 
end;

1. Was able to reproduce the explained behavior. On 19c -> Attr_1, Attr_2 on 11 -> ID, Description

2. Workaround I found is use base table/view%rowtype instead of cursor%rowtype.