We have been trying to create an application user that would have just select and execute rights on the objects by dev-user. After encountering problems during our creating of synonyms (Oracle 8 does not allow synonyms for user-defined types), we just created the user_derfined types under the app-user as well.
Upon executing some packages which make reference to these types, we have the errors: ora 06550, pls-0306. This only happens when reference is made to one type: id_array
I created the arrays with the follwoing commands:
create type id_array as varray (10000) of number
create type string_array as varray (10000) of varchar2(254)
References to string _array work fine. Also if we login and run the packages with the dev-user, it works, but whe we login as the app-user, the id_array gives an error.
There are two definitions of the types id_array and string_array? How do you use the types as app_user? Do you pass a parameter of id_array to a package procedure/function of dev_user? In this case, you have a app_user.id_array and a dev_user.id_array, Oracle considers this as two different types.
Why you created the types in the schema of app_user also, why not use schema_name.type_name notation?
We have since removed the array types created in the app-user, granted execute rights to public. When, in the code,we are getting the message "invalid name pattern: fndryr0200.STRING_ARRAY"
we are telling the code to use fndryr0200.string_array (fndryr0200 is the dev user)