Oracle 19C数据库问题 [英] Oracle 19C database issue

问题描述

我有一个可以在11g版本中正常工作的软件包.

I have a package working fine in 11g version.

但是当我在19c版本中部署相同的程序包时，其行为是不同的.

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

PFB描述.

包装说明中有一个游标，并使用cursor％rowtype创建了一个表类型.具有返回表类型的流水线函数.

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

将函数与table子句一起使用

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.

在11g中，该函数返回与游标列名称相同的列标题.但是在19c中，该函数返回的列标题为"Attr_1，Attr_2等".

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.

样本:PFB样品.

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));

在11g中，以上选择将列标题指定为"id，description"，但在19c中，我将其获取为"ATTR_1，ATTR_2".

In 11g, the above select gives column headers as "id, description", but in 19c i am getting as "ATTR_1, ATTR_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. 能够重现所解释的行为.在19c-> Attr_1，在11上是Attr_2-> ID，说明

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

解决方法是使用基表/视图％rowtype 而不是 cursor％rowtype .

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

这篇关于Oracle 19C数据库问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！