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 row(l_tab(i));
end loop;
return ;
end;
end pkg_test;
Select * from table(pkg_test.fn_test(2));
<b>In 11g, the above select gives column headers as "id, description", but in 19c i am getting as "ATTR_1, ATTR_2".</b>
Please help.
Stackover flow:
https://stackoverflow.com/questions/60225275/oracle-19c-database-issue
I've replicated your results on 18 as well. That's a bug - please log it with Support (it gets more priority if it comes from customers than if I do it).
SQL> Create table tb_test (id number, description varchar2 (50));
Table created.
SQL>
SQL> create or replace package pkg_test is
2 cursor cur_test is
3 select *
4 from tb_test
5 where 1=2;
6 type typ_cur_test is table of cur_test%rowtype;
7 function fn_test(p_rows in number) return typ_cur_test pipelined;
8 end;
9 /
Package created.
SQL> create or replace package body pkg_test is
2 function fn_test(p_rows in number) return typ_cur_test pipelined as
3 l_tab typ_cur_test := typ_cur_test();
4 begin
5 for i in 1..p_rows loop l_tab.extend;
6 l_tab(i).Id := i;
7 l_tab(i). Description := 'test';
8 pipe row(l_tab(i));
9 end loop;
10 return ;
11 end;
12 end pkg_test;
13 /
Package body created.
SQL> Select * from table(pkg_test.fn_test(2));
ATTR_1 ATTR_2
---------- --------------------------------------------------
1 test
2 test
2 rows selected.