ORA-06553:请-801:测试函数返回ROWTYPE时的内部错误[55018]

Question

我需要测试一些函数返回ROWTYPE变量在蟾蜍。当我尝试运行它时，我得到了Internal error。

我跑就像

SELECT MYPACKAGE.MyFunction(param1, aram2, param3) FROM DUAL

有没有任何方法来测试一个函数返回ROWTYPE的蟾蜍？

Answer 1

由于您只是想测试这个函数，所以可以使用一个匿名PL/SQL块调用它，并将其结果分配给一个匹配的行类型变量，例如：

declare
  l_row mytable%rowtype;
begin
  -- call the function and assign the result to a variable
  l_row := mypackage.myfunction(1, 2, 3);
  -- do something with the result
  dbms_output.put_line(l_row.some_columns);
end;
/

快速演示与一个化妆表和扩展的功能：

create table mytable (col1, col2, col3, col4, col5) as
select 1, 2, 3, 'test', sysdate from dual;

create or replace package mypackage as 
  function myfunction (param1 number, param2 number, param3 number)
  return mytable%rowtype;
end mypackage;
/

create or replace package body mypackage as 
  function myfunction (param1 number, param2 number, param3 number)
  return mytable%rowtype is
    l_row mytable%rowtype;
  begin
    select * into l_row
    from mytable
    where col1 = param1
    and col2 = param2
    and col3 = param3;

    return l_row;
  end myfunction;
end mypackage;
/

从SQL调用得到与现在看到的相同的错误：

    select mypackage.myfunction(1, 2, 3) from dual;

    SQL Error: ORA-06553: PLS-801: internal error [55018]

但是使用一个块(在这里通过启用输出的运行)：

set serveroutput on

declare
  l_row mytable%rowtype;
begin
  -- call the function and assign the result to a variable
  l_row := mypackage.myfunction(1, 2, 3);
  -- do something with the result
  dbms_output.put_line(l_row.col4 ||':'|| l_row.col5);
end;
/

test:2019-04-29


PL/SQL procedure successfully completed.

db<>fiddle

Answer 2

是的，这不管用。函数在SQL查询中使用时，应该返回SQL数据类型，而%ROWTYPE是PL/SQL记录。

这可能就是你现在所拥有的：

SQL> create or replace function f_test (par_deptno in number)
  2    return dept%rowtype
  3  is
  4    retval dept%rowtype;
  5  begin
  6    select deptno, dname, loc
  7      into retval
  8      from dept
  9      where deptno = par_deptno;
 10    return retval;
 11  end;
 12  /

Function created.

SQL> select f_test(10) From dual;
select f_test(10) From dual
       *
ERROR at line 1:
ORA-06553: PLS-801: internal error [55018]


SQL>

可以选择的选项是创建(并返回)一个对象类型。下面是一个例子：

SQL> create or replace type dept_type as object
  2    (deptno number,
  3     dname  varchar2(20),
  4     loc    varchar2(20));
  5  /

Type created.

SQL> create or replace function f_test (par_deptno in number)
  2    return dept_type
  3  is
  4    retval dept_type;
  5  begin
  6    select dept_type(deptno, dname, loc)
  7      into retval
  8      from dept
  9      where deptno = par_deptno;
 10    return retval;
 11  end;
 12  /

Function created.

SQL> select f_test(10).dname From dual;

F_TEST(10).DNAME
--------------------
ACCOUNTING

SQL>