How Can I Correctly Pass a Table to a Stored Procedure in Oracle in SQL Plus?

Being new to Oracle, I'm almost certain I'm missing something simple here. This is what I'm trying to run:

declare 
type string_table IS TABLE OF VARCHAR2(512);
outuser nvarchar2(512);
outrole nvarchar2(512);
result number(38);
usernames string_table;
rolenames string_table;
begin
usernames(1) := 'Administrator';
rolenames(1) := 'Admins';
result := ASPNETDB.ORA_ASPNET_UIR_ADDUSERSTOROLES('/', usernames, 1, rolenames, 1, outuser, outrole);
END;

This is the error:

ERROR at line 11:
ORA-06550: line 11, column 11:
PLS-00306: wrong number or types of arguments in call to
'ORA_ASPNET_UIR_ADDUSERSTOROLES'

This is the output of DESC for the function:

FUNCTION ASPNETDB.ORA_ASPNET_UIR_ADDUSERSTOROLES RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 APPLICATIONNAME_               NVARCHAR2               IN
 USERNAMES_                     TABLE OF VARCHAR2(512)  IN
 USERNAMES_ARR_SIZE             NUMBER(38)              IN
 ROLENAMES_                     TABLE OF VARCHAR2(512)  IN
 ROLENAMES_ARR_SIZE             NUMBER(38)              IN
 USERNAME                       NVARCHAR2               OUT
 ROLENAME                       NVARCHAR2               OUT

I discovered that collections must have the same data type for an assignment to work. Having the same element type is not enough.

Armed with this information, I set out to figure out what type I needed to use:

SQL> select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = '
PACKAGE' AND OWNER='ASPNETDB' order by OWNER, OBJECT_NAME;

OBJECT_NAME                    OWNER
------------------------------ ------------------------------
ORA_ASPNET_PROF_PKG            ASPNETDB
ORA_ASPNET_ROLES_PKG           ASPNETDB

select line, text from user_source where TYPE = 'PACKAGE'; returned a bunch of garbage which included a line having: 1ASSOCARRAYVARCHAR2_T. That prompted me to try changing my two variables to:

usernames ora_aspnet_roles_pkg.assocarrayvarchar2_t;
rolenames ora_aspnet_roles_pkg.assocarrayvarchar2_t;

That is the correct solution to my problem. If someone can suggest a less roundabout way of finding what type to use inside a package, I'll gladly accept that answer.

StackExchange Database Administrators Q#10428, answer score: 2