Phenomenon:
When calling the Oracle stored procedure you may get the error like “PLS-00306: wrong number or types of arguments in call…”, and if you check the Appeon error.log file you will get the following information.
|
2014-04-15 10:00:28.414 : [Application name=intelliminqa,conn cache=BSPLQA,SQLOrProcedure=EVEREST.DBR_POL_SEARCH_RESULTS]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DBR_POL_SEARCH_RESULTS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) |
Cause Analysis:
This issue is probably caused by the limitation of the user permission.
Solution:
Step 1- Since Appeon would try to get the argument information of stored procedure by executing the SQL statements like below, please check if the user has enough permission to access View ALL_ARGUMENTS and ALL_OBJECTS and ALL_SYNONYMS.
You can login with the user that you use to connect to the database in the web application and change the 'owner', 'spName' and 'packageName' accordingly and then directly execute the SQL statements as below to check if the user has the enough permission. Alternatively, you can create a Datawindow object with these SQL statements and then do retrieve on web to check if the user has enough permission.
|
SELECT a.position as No, b.object_type as ObjectType, decode(a.position, 0, 'RETURN_VALUE', a.argument_name) as ArgName, decode(a.position, 0, 5,decode(a.in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', 4)) as InOutType, a.data_type as DataType FROM ALL_ARGUMENTS a, ALL_OBJECTS b WHERE (b.object_type in ('PROCEDURE','PACKAGE','FUNCTION')) AND b.object_id = a.object_id AND a.data_level = 0 AND b.OWNER ='owner' AND a.OBJECT_NAME ='spName' AND b.OBJECT_NAME ='packageName' UNION SELECT a.position as No, b.object_type as ObjectType, decode(a.position, 0, 'RETURN_VALUE', a.argument_name) as ArgName, decode(a.position, 0, 5,decode(a.in_out, 'IN', 1, 'IN/OUT', 2, 'OUT', 4)) as InOutType, a.data_type as DataType FROM ALL_ARGUMENTS a, ALL_OBJECTS b ,ALL_SYNONYMS c WHERE (b.object_type in ('PROCEDURE','PACKAGE','FUNCTION')) AND b.object_id = a.object_id AND a.data_level = 0 AND (b.owner=c.table_owner and (c.owner='owner' or c.owner='PUBLIC') AND (a.OBJECT_NAME ='spName' or a.OBJECT_NAME=c.table_name) and c.synonym_name='packageName' AND b.OBJECT_NAME =c.table_name ) ORDER BY No |
Step 2- If the user doesn’t have enough permission, then please grant the proper permission to the user.
Step 3- Create the view with the same name for this user to make the user get the correct result.
For example, if the user cannot get the correct result from the view ALL_ARGUMENTS but can get the correct result from the view User_ARGUMENTS, then you cacn create view ALL_ARGUMENTS for this user with the same definition as view User_ARGUMENTS to work around this issue.