Generating test script for package function returning a record generates non-compilable test code

[Claus Pedersen]

I have a package function returning a PL/SQL record.
When I right-click and select Test, the test script generated will look like this:

declare
  -- Non-scalar parameters require additional processing
  result  record;
begin
  -- Call the function
  result := my_package.my_function(param1 => :param1,
                                   param2 => :param2);
end;

The error lies in line three:
result record;
It should read:
result my_package.my_record;

Please, could this bug be fixed ASAP?

 

[Marco Kalter]

This works fine for me. I created the following test package specification:

create or replace package RecordTest is

  type TestRecord is record(id integer);

  procedure TestProc(p_record in TestRecord);

end RecordTest;

For RecordTest.TestProc I get the following Test Script:

declare
  -- Non-scalar parameters require additional processing
  p_record recordtest.testrecord;
begin
  -- Call the procedure
  recordtest.testproc(p_record => p_record);
end;

Can you let me know your package specification?

 

[Claus Pedersen]

E.g. on scott/tiger:

create or replace package my_package is

  subtype my_record is emp%rowtype;

  function my_function (param1 number,
                        param2 number)
  return my_record;

end my_package;

The problem maybe lies in the fact that the record is of type %rowtype and not defined as a 'hardcoded' record.

 

[Marco Kalter]

I see. For %rowtype arguments the data type is unfortunately not available in the sys.all_arguments view:

select argument_name, type_owner, type_name, type_subname
from sys.all_arguments
where owner = user
and package_name = 'MY_PACKAGE'
and data_level = 0

For other record types the type owner, name and subname are known.

 

[Claus Pedersen]

Will this be fixed at any time? PLD is able to show the content of the method in a hover box when the mouse is over the method, so why not parse the code from here when the Oracle database does not provide enough information?

 

[Marco Kalter]

We'll investigate this again.

 

[Marco Kalter]

It turned out that on Oracle18 and later the %rowtype information is available in the sys.all_arguments view. We have enhanced the Test function so that it correctly declares the variables for the parameters and return value.

 

[Claus Pedersen]

OK, thanks.

 

[Claus Pedersen]

Solved in version 14.0.3.1981, thx