ORA-06553: PLS-801: 测试函数返回 ROWTYPE 时的内部错误 [55018] [英] ORA-06553: PLS-801: internal error [55018] when testing function returning ROWTYPE

问题描述

我需要在 Toad 中测试一些返回 ROWTYPE 变量的函数.当我尝试运行它时，我收到内部错误.

I need to test some function returning ROWTYPE variable in Toad. When I try to run it, I aget the Internal error.

我运行的是

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

有什么方法可以测试为 Toad 返回 ROWTYPE 的函数吗?

Is there any way to test a function returning ROWTYPE for Toad?

推荐答案

由于您只想测试该函数，您可以使用匿名 PL/SQL 块来调用它并将其结果分配给匹配的 rowtype 变量，例如:

As you just want to test the function you could use an anonymous PL/SQL block to call it and assign its result to a matching rowtype variable, e.g.:

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

带有制作表和扩展功能的快速演示:

Quick demo with a made-up table and expanded function:

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 调用得到与您现在看到的相同的错误:

Calling from SQL gets the same error you see now:

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

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

但是有一个块(在此处通过启用输出的 SQL Developer 运行):

But with a block (run here through SQL Developer with output enabled):

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

这篇关于ORA-06553: PLS-801: 测试函数返回 ROWTYPE 时的内部错误 [55018]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！