Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.