I received the following question from someone in my company:

"Here is the synopsis: we are on Oracle 8.1.7 and making extensive use of ref cursors in stored procedures. This works fine as long as the executing ID is the same as the schema owner. Problem is this runs counter to **** policy, which says the application id under IIS cannot be the schema owner. Ideally it would seem reasonable to expect a non schema owner to be able to execute and retrieve data from stored procs as long as execute and select permission were correct but we get an PLS_00306 "Wrong number or type of arguments in call to <> " Any insight into this issue would be most appreciated. Thanks. "

Here is a smple header:

PROCEDURE stp_GetSonarUsers
---iDummy IN varchar2,
---oRefCur IN OUT PKG_Utility.refcur

PKG_Utility.RefCur is of course defined as REF CURSOR. Now, if they run this from the app (via Oracle OLEDB provider - 8.1.7) as the owner - no problem. From the app as auser with execute rights on the proc and full CRUD rights on the table, they get the error. However, if they run as this same user from SQL*PLUS, it works fine.

Anybody have any thoughts on this one.


- Chris