2

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.

Thomas Strub
  • 1,275
  • 7
  • 20
Capricon
  • 21
  • 3
  • 1
    If you can't share the real code, then please [create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) that demonstrates your problem. –  Feb 14 '20 at 11:22
  • PFB the sample. – Capricon Feb 14 '20 at 12:05

1 Answers1

2

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.

Thomas Strub
  • 1,275
  • 7
  • 20
  • I dont find any difference between my package spec and your code. Added { to differenciate. nothing else. – Capricon Feb 14 '20 at 13:43
  • For each and every cursor present in my code, i can't create a view right. Why the behavior changed in 19C? Is there any way to bring back the behavior of 11g in the new version? – Capricon Feb 14 '20 at 15:41
  • Probably support can help you here. Perhaps if you change compatibility mode. – Thomas Strub Feb 16 '20 at 19:08
  • Connor McDonald, from Ask Tom, informed me to raise a request to Oracle team as it is bug. https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-19c-database-issue-with-table-types-and-pipelining#followup- – Capricon Feb 17 '20 at 07:54
  • Can the cursor and type be defined in the function instead of at the package level? Is the {} there to draw attention to the answer or is that the actual syntax? – Superdooperhero Mar 16 '22 at 12:12