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;
-
能够重现所解释的行为.在19c-> Attr_1,在11上是Attr_2-> ID,说明
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屋!