Hi,
I am having a problem but it occurs at irregular intervals. I have a code which refreshes data every 5 secs(used threads) which it does correctly. But after every few mins(10-15 approx) I get the following error.
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Below is my vb.net calling code.....
Code:
Public Shared Function fnRunSPReturnDataTable(ByVal strStorProcName As String, ByVal arlInpParmValu As ArrayList _
, ByRef arlOutParmValu As ArrayList, ByVal intNoOfParam As Integer) As DataTable
Try
If oConnection Is Nothing Or sDataSource <> oGlobal.gsDataSource Or sDBUserId <> oGlobal.gsDBUserId _
Or sDBPassword <> oGlobal.gsDBPassword Then
If subConnectDB() = "Failure" Then
Return Nothing
End If
End If
Dim outParm As OleDbParameter
Dim inptParm As OleDbParameter
oCommand = New OleDbCommand()
arlOutParmValu.Clear()
oCommand.Parameters.Clear()
oCommand.Connection = oConnection
oCommand.CommandType = CommandType.StoredProcedure
oCommand.CommandText = strStorProcName
For ctInpParam As Integer = 0 To arlInpParmValu.Count - 1
inptParm = oCommand.Parameters.Add("inptparm" & ctInpParam, OleDbType.Variant)
inptParm.Value = arlInpParmValu(ctInpParam)
Next
For ctOutParam As Integer = 0 To intNoOfParam - 1
outParm = oCommand.Parameters.Add("opparm" & ctOutParam, OleDbType.Variant)
outParm.Direction = ParameterDirection.Output
Next
oDataAdapter = New OleDbDataAdapter(oCommand)
oDataSet = New DataSet
oDataAdapter.Fill(oDataSet)
For ctParam As Integer = 0 To oCommand.Parameters.Count - 1
If CStr(oCommand.Parameters.Item(ctParam).ParameterNa me).Substring(0, 6) = "opparm" Then
arlOutParmValu.Add(oCommand.Parameters.Item(ctPara m).Value)
End If
Next
If oConnection.State = ConnectionState.Open Then subDisConnectDB()
Return oDataSet.Tables(0)
Catch ex As Exception
subWriteErrorToDB(gsAppName, "clsDataAccess", "fnRunSPReturnDataTable", ex.Message)
Return Nothing
End Try
End Function
Below is my oracle code.....
Code:
CREATE OR REPLACE PACKAGE S_Pk_Test
AS
num_no_data_found CONSTANT ERR_MST.erm_err_id%TYPE := 97503;
num_gen_error CONSTANT ERR_MST.erm_err_id%TYPE := 97504;
num_success CONSTANT ERR_MST.erm_err_id%TYPE := 94000;
num_others CONSTANT ERR_MST.erm_err_id%TYPE := 10002;
TYPE curtyp IS REF CURSOR;
Procedure s_pr_details ( cur_out_dtls OUT curtyp, num_out_retrn_code OUT NUMBER);
END S_Pk_Test;
/
CREATE OR REPLACE PACKAGE BODY S_Pk_Test
AS
PROCEDURE s_pr_details (
cur_out_dtls OUT curtyp, num_out_retrn_code OUT NUMBER
)
AS
----Declaring constants
program_name CONSTANT ERR_LOG.erl_prgrm_name%TYPE
:= 'S_Pk_Test.s_pr_details';
cur_dtls curtyp;
BEGIN
OPEN cur_dtls FOR
SELECT XXX,YYY FROM ZZZ;
cur_out_dtls := cur_dtls;
num_out_retrn_code :=0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
S_Pr_Log_Error ( program_name,
SQLERRM || 'no data found in ZZZ table' || SQLCODE );
WHEN OTHERS THEN
S_Pr_Log_Error ( program_name, SQLERRM || '' || SQLCODE );
END s_pr_details;