Skip to Main Content
  • Questions
  • Oracle 19C database issue with table types and pipelining

Breadcrumb

Easter

Question and Answer

Connor McDonald

Thanks for the question, Muniappan.

Asked: February 14, 2020 - 3:54 pm UTC

Answered by: Connor McDonald - Last updated: February 18, 2020 - 2:48 am UTC

Category: Database Development - Version: 19C

Viewed 100+ times

You Asked

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

and we said...

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.


and you rated our response

  (1 rating)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

Followup

February 17, 2020 - 8:10 am UTC

Reviewer: Muniappan Mohanraj from Chennai, India

Hi McDonald,

But it is working correct in the below link of livesql.oracle.com.

It has a version of Oracle 19C.

https://livesql.oracle.com/apex/livesql/s/jnuzj5we72k94qomt9eac4srv

Please advice.
Connor McDonald

Followup  

February 18, 2020 - 2:48 am UTC

LiveSQL is 19.5 - what is your 19c version ?

More to Explore

PL/SQL

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