Can anybody help me with this error that I am getting on VB.net: Any responses will be very helpful
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETCURRENCYFROMSCENARIO' ORA-06550: line 1, column
7: PL/SQL: Statement ignored
The Function written in VB.net that is calling the PLSQL function is pasted below: Public Function GetCurrencyFromScenario(ByVal DataSetType As String, ByVal DataSetName As String, ByVal CurrType As String)
Dim returnValue As String
Dim db As OracleDbHelper = GetHelper()
Dim params As New List(Of OracleParameter)
params.Add(OracleParameterFactory.CreateIn("asUserName", OracleDbType.Varchar2, _props.CurrentUser))
params.Add(OracleParameterFactory.CreateIn("asDatasetName", OracleDbType.Varchar2, DataSetName))
params.Add(OracleParameterFactory.CreateIn("asDatasetType", OracleDbType.Varchar2, DataSetType))
params.Add(OracleParameterFactory.CreateIn("asCurrType", OracleDbType.Varchar2, CurrType))
db.ExecSql_ReaderDataTable(ProcedureName(FPAGET, "GetCurrencyFromScenario"), CommandType.StoredProcedure, params)
The PL SQL code that contains the function is called as below: Procedure GetCurrencyFromScenario (
asCurrency out varchar2) is
asCurrency := 'EUR';
ODP.NET operates in bind by position mode by default. This can be changed by setting the BindByName property if desired.
In your case I don't see that you have set BindByName = true so you are likely operating in bind by position mode.
That being the case you need to add the parameters to the collection in the correct order (i.e. the same order as declared in the procedure).
You have "flipped" the order of the following two parameters vis-à-vis the procedure declaration:
As mentioned, I flipped the two variables and 'asDatasetName' and 'asDatasetType' and tried it. But is still failed.
Regarding the "fpa_operating_variable.value_text ", yes it is a varchar2. I rechecked the same.
Something must be happening in code that we can't see then.
I would confirm:
1. Whether or not any of the code we can't see does (or does not) set BindByName to true on the underlying OracleCommand object
2. OracleParameterFactory must be a custom implemented helper method - review to make sure it is doing the right thing (such as creating a large enough buffer for the output parameter, etc)
3. ExecSql_ReaderDataTable must be a custom implemented helper method - review to make sure it adds the values from the created parameter list to the OracleCommand object correctly
This to me seems like either of the following:
- BindByName is not set to true and therefore parameters must be added to the OracleCommand::Parameters collection in the correct order
- BindByName is set to true and therefore parameters must be added to the OracleCommand::Parameters collection with the correct names
I really appreciate your detailed response.
Can you tell me how to check the BindByName for the Oracle command object?
I have checked boths points 2 & 3 and they seem to be quite correct and inline with my code.
Looking at your original post again, I am wondering if this might be an issue due to BindByName actually being set to true. I say that because all of the parameters are declared as varchar2 in both the PL/SQL and .NET code.
I'm not sure how much of the code in the stack you have the ability to inspect, you would just check the BindByName property value on the OracleCommand object being used to execute your command.
Alternatively you might just change the names of the parameters to match those in the PL/SQL code:
This should work if the BindByName is false or true.
Thanks for the replies Mark.
I was able to tweak my code little bit and use the exec_sql command to execute the procedure. Also, I set one of the input parameters to output parameters and hence get the value returned in through that.
I am still finding a way to check the BindByName property value and I dont have that level of access to the stack as you said. But will update you when I am successful.