Knowledgebase
Experienced the error “PLS-00306: wrong number or types of arguments in call…” when calling the Oracle stored procedure
Posted by Nico Wang on 16 April 2014 01:16 PM

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.

(7 votes)
This article was helpful
This article was not helpful

Comments (0)
Post a new comment 
 

Please login if you wish to leave a comment about this article.

Help Desk Software by Kayako Fusion