CODEWITHSUNDEEP
oracle-databaseplsql
Coding Duchess
Coding Duchess

Reputation: 6899

ORA-06553: PLS-801: internal error [55018] when testing function returning ROWTYPE

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

I run is as

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

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

Upvotes: 2

Views: 5655

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

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

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]

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

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

True, it won't work. Function, when used in a SQL query, is supposed to return a SQL datatype, while %ROWTYPE is a PL/SQL record.

This is what you, probably, have now:

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>

Option you might choose is to create (and return) an object type. Here's an example:

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>

Upvotes: 3

Related Questions