Data types in Test window

[Ivan C.]

When one creates a Test window for a Procedure of Function, the data types aren't properly created for all variables.

Here's an example:

declare
-- Non-scalar parameters require additional processing
ata_import_attribute table of record;
ata_index_import_attribute table of number;
begin
...

Above, is the declaration part of the Test window, created by PL/SQL Developer. Although, I yet have no idea, how does it figure out that ATA_INDEX_IMPORT_ATTRIBUTE is a PL/SQL Table of Number (maybe it parses type's package's specification), this is the code that should be there:

declare
-- Non-scalar parameters require additional processing
ata_import_attribute DBO.PKG_IMPORT.T_IMPORT_ATTRIBUTE;
ata_index_import_attribute DBO.PKG_BENEFIT.NUMTABLE;
begin
...

Notice the data types of the ATA_IMPORT_ATTRIBUTE and ATA_INDEX_IMPORT_ATTRIBUTE variables. PL/SQL Developer should read the following columns in the ALL_ARGUMENTS data dictionary view for "User Defined" data types:

TYPE_OWNER
TYPE_NAME
TYPE_SUBNAME

to properly define the data type.

Thank you in advance.

 

[Marco Kalter]

The parameter data type is queried from the sys.all_arguments view. It may be that the package is not included in this view (you can check this), but is available in the sys.dba_arguments view. In that case you need to enable the "Use DBA views if available" preference (Tools > Preferences > Options).

 

[Ivan C.]

I agree that parameters' data type should be queried from the sys.all_arguments view (and may be they are, but in an incorrect way).
I'm logged in as the owner of the procedure, and here are the results I get:

SELECT ' ' || LOWER (argument_name) || ' ' || LOWER (type_owner) || '.' || LOWER (type_name) || '.' || LOWER (type_subname) || ';'
FROM sys.all_arguments
WHERE object_name = 'P_IMPORT_AA_PAYROLL_DEPARTMENT'
AND argument_name IN ('ATA_IMPORT_ATTRIBUTE', 'ATA_INDEX_IMPORT_ATTRIBUTE');

ata_import_attribute dbo.pkg_import.t_import_attribute;
ata_index_import_attribute dbo.pkg_benefit.numtable;

So, PL/SQL Developer should be able to do the same exact thing (for arguments of "User Definded" data types), but it doesn't.

Thank you.

 

[Marco Kalter]

Can you send me a package specification with this function and the type that is referenced, so that I can try to reproduce this?

 

[Ivan C.]

Sure thing...

This happens with any procedure referencing packaged "User Defined" types.

Here's a package (specification only):

CREATE OR REPLACE PACKAGE a_test_pkg IS
TYPE t_emp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;

TYPE t_numtab IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
END a_test_pkg;

...and here's a procedure, referencing both of the User Defined table types:

CREATE OR REPLACE
PROCEDURE a_test_p
(pit_emp a_test_pkg.t_emp
,pit_nbr a_test_pkg.t_numtab
)
IS
BEGIN
NULL;
END a_test_p;

Both objects (package and procedure) are owned by the same user.

Here's the content of the Test Window:

declare
-- Non-scalar parameters require additional processing
pit_emp table of record;
pit_nbr table of binary_integer;
begin
-- Call the procedure
a_test_p(pit_emp => pit_emp,
pit_nbr => pit_nbr);
end;

... when I would have expected the following:

declare
-- Non-scalar parameters require additional processing
pit_emp a_test_pkg.t_emp;
pit_nbr a_test_pkg.t_numtab;
begin
-- Call the procedure
a_test_p(pit_emp => pit_emp,
pit_nbr => pit_nbr);
end;

... (notice the data types of the variables in the declaration part).

I'm using Oracle 8.1.7.4.0, and PL/SQL Developer 6.0.1.848 - if that makes any diference.

Again, thank you.

 

[Marco Kalter]

Thanks, that does indeed not work as expected. We'll fix it.