I am getting an intermittent error while trying to execute oracle stored
procedure from .net.
From vb.net i am trying to execute the following vb.net code and oracle
procedure.
Dim Conn As Oracle.DataAccess.Client.OracleConnection = New OracleConnection
Conn.ConnectionString = _connectionString
'command
Dim Cmd As New OracleCommand
Cmd.Connection = Conn
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandText = "SUBMIT_SAVE"
Conn.Open()
'Dim dateformatvar As String = "MM/dd/yyyy HH:MI:ss AM"
'Dim threadGlob As OracleGlobalization =
OracleGlobalization.GetThreadInfo()
'threadGlob.DateFormat = dateformatvar
'parameters
Dim Param0 As New OracleParameter("o_RETURNVALUE",
OracleDbType.Int32, ParameterDirection.Output)
Dim Param1 As New OracleParameter("i_SEQ", OracleDbType.Int32,
ParameterDirection.Input)
Dim Param2 As New OracleParameter("i_DISPUTED_FLAG",
OracleDbType.Int32, ParameterDirection.Input)
Dim Param3 As New OracleParameter("i_EXPENDITURE",
OracleDbType.Varchar2, ParameterDirection.Input)
Param3.Value = CStr(EXPENDITURE_TYPE)
Dim Param4 As New OracleParameter("i_DISPUTED_CLEARING_ID",
OracleDbType.Int32, ParameterDirection.Input)
Dim Param5 As New OracleParameter("i_PERSONAL_FLAG",
OracleDbType.Int32, ParameterDirection.Input)
Dim Param6 As New OracleParameter("i_SUBMITTED_DATE",
OracleDbType.Date, ParameterDirection.Input)
Dim Param7 As New OracleParameter("i_SUBMITTED_GEN",
OracleDbType.Varchar2, ParameterDirection.Input)
Param7.Value = SUBMITTED_GEN
Dim Param8 As New OracleParameter("i_DESCRIPTION",
OracleDbType.Varchar2, ParameterDirection.Input)
Dim Param9 As New OracleParameter("i_TRANSACTION_AMT",
OracleDbType.Decimal, ParameterDirection.Input)
Param9.Value = TRANSACTION_AMT
Dim Param10 As New OracleParameter("i_RECEIPT_BATCH_NO",
OracleDbType.Varchar2, ParameterDirection.Input)
Dim Param11 As New OracleParameter("i_PROJECT_NO",
OracleDbType.Varchar2, ParameterDirection.Input)
Param11.Value = PROJECT_NO
Dim Param12 As New OracleParameter("i_DATE_MODIFIED",
OracleDbType.Date, ParameterDirection.Input)
Dim Param13 As New OracleParameter("i_ACCOUNT_NUMBER",
OracleDbType.Varchar2, ParameterDirection.Input)
Param13.Value = AccountNumber
Dim Param14 As New OracleParameter("i_TRANS_REF_NUMBER",
OracleDbType.Varchar2, ParameterDirection.Input)
Param14.Value = TransRefNumber
Dim Param15 As New OracleParameter("i_SEQUENCE_NUMBER",
OracleDbType.Int32, ParameterDirection.Input)
Param15.Value = SequenceNumber
Dim Param16 As New OracleParameter("i_POSTING_DATE",
OracleDbType.Date, ParameterDirection.Input)
Dim d3 As Date = Format(Convert.ToDateTime(PostingDate), "MM/dd/yyyy")
Param16.Value = d3
'
Dim Param17 As New OracleParameter("i_REJECTIONINFO",
OracleDbType.Varchar2, ParameterDirection.Input)
Dim Param19 As New OracleParameter("i_NEWDATE_MODIFIED",
OracleDbType.Date, ParameterDirection.Input)
'Param19.Value = DateTime.Parse(NewDateModified.ToString("M/d/yyyy
h:mm tt"))
Dim d As New Oracle.DataAccess.Types.OracleDate(2005, 10, 14)
Param19.Value = d
Cmd.Parameters.Add(Param0)
Cmd.Parameters.Add(Param1)
Cmd.Parameters.Add(Param2)
Cmd.Parameters.Add(Param3)
Cmd.Parameters.Add(Param4)
Cmd.Parameters.Add(Param5)
Cmd.Parameters.Add(Param6)
Cmd.Parameters.Add(Param7)
Cmd.Parameters.Add(Param8)
Cmd.Parameters.Add(Param9)
Cmd.Parameters.Add(Param10)
Cmd.Parameters.Add(Param11)
Cmd.Parameters.Add(Param12)
Cmd.Parameters.Add(Param13)
Cmd.Parameters.Add(Param14)
Cmd.Parameters.Add(Param15)
Cmd.Parameters.Add(Param16)
Cmd.Parameters.Add(Param17)
Cmd.Parameters.Add(Param19)
Try
'Conn.Open()
Cmd.ExecuteNonQuery()
'System.Threading.Thread.Sleep(1000)
Dim Out As Integer = CType(Param0.Value.ToString, Integer)
If Out = 0 Then
For i As Integer = 0 To Cmd.Parameters.Count - 1
Debug.WriteLine(Cmd.Parameters(i).ParameterName & " = "
& Cmd.Parameters(i).Value.ToString)
Next
ReturnValue = False
End If
Cmd.Dispose()
Catch e As Oracle.DataAccess.Client.OracleException
ReturnValue = False
For i As Integer = 0 To Cmd.Parameters.Count - 1
Debug.WriteLine(Cmd.Parameters(i).ParameterName & " = " &
Cmd.Parameters(i).Value.ToString)
Next
Dim oError As Oracle.DataAccess.Client.OracleError
For Each oError In e.Errors
Debug.Write(oError.Message.ToString())
Next
Throw e
Catch ex As Exception
ReturnValue = False
Throw ex
Finally
Conn.Close()
Conn.Dispose()
End Try
Oracle stored procedure..
CREATE OR REPLACE PROCEDURE "SUBMIT_SAVE"
(
o_RETURNVALUE OUT NUMBER,
i_SEQ NUMBER DEFAULT NULL ,
i_DISPUTED_FLAG NUMBER DEFAULT NULL ,
i_EXPENDITURE VARCHAR2 DEFAULT NULL ,
i_DISPUTED_CLEARING_ID NUMBER DEFAULT NULL ,
i_PERSONAL_FLAG NUMBER DEFAULT NULL ,
i_SUBMITTED_DATE DATE DEFAULT NULL ,
i_SUBMITTED_GEN VARCHAR2 DEFAULT NULL ,
i_DESCRIPTION VARCHAR2 DEFAULT NULL ,
i_TRANSACTION_AMT NUMBER DEFAULT NULL ,
i_RECEIPT_BATCH_NO VARCHAR2 DEFAULT NULL ,
i_PROJECT_NO VARCHAR2 DEFAULT NULL ,
i_DATE_MODIFIED DATE DEFAULT NULL,
i_ACCOUNT_NUMBER VARCHAR2 DEFAULT NULL,
i_TRANS_REF_NUMBER VARCHAR2 DEFAULT NULL,
i_SEQUENCE_NUMBER NUMBER DEFAULT NULL,
i_POSTING_DATE DATE ,
i_REJECTIONINFO VARCHAR2 DEFAULT NULL,
i_NEWDATE_MODIFIED DATE DEFAULT NULL
)
IS
x NUMBER :=0;
x2 NUMBER(5) :=0;
vgo NUMBER :=0;
newid NUMBER(10);
vmerchant_name VARCHAR2(26);
vperiod NUMBER(15);
vtransaction_date DATE;
vtransaction_gen VARCHAR2(20); -- CARD_ACCOUNT type 3 field 2
vbillingcurrency_code NUMBER(15); -- CARD_TRANSACTION type 5
BEGIN
-- if i_date_modified is null then go ahead (transaction does not exist yet)
IF (i_date_modified IS NULL) THEN
vgo := 1;
ELSE
-- see if any transactions exist with the same date modified
-- if yes, then it is ok to update it
SELECT count(*) INTO x2 FROM SUPP WHERE
ACCOUNT_NUMBER=i_ACCOUNT_NUMBER AND
TRANS_REF_NUMBER=i_TRANS_REF_NUMBER AND
SEQUENCE_NUMBER=i_SEQUENCE_NUMBER AND
POSTING_DATE = i_POSTING_DATE AND
DATE_MODIFIED = i_DATE_MODIFIED
AND ( (i_SEQ IS NULL) OR (i_SEQ=0)) ;
IF (i_SEQ IS NOT NULL) OR (i_SEQ>0) THEN
vgo :=1;
END IF;
-- if there already is same date modified and either i_seq is null or is
zero
-- then vgo=1
-- this should only catch the first row in multi row transaction
-- only check first row in multirow
-- if i_SEQ IS NOT NULL OR i_SEQ>0 that means 2nd row in multirow
IF (x2>0) THEN
vgo := 1;
END IF;
END IF;
IF (vgo > 0) THEN
SELECT SUPPLIER_NAME,
PERIOD,
TRANS_DATE,
CH.EMPLOYEE_ID,
CT.BILLING_CURRENCY_CODE
INTO
vmerchant_name,
vperiod,
vtransaction_date,
vtransaction_gen,
vbillingcurrency_code
FROM VCF_CARD_TRANSACTION CT LEFT JOIN VCF_CARD_ACCOUNT CA ON
CT.ACCOUNT_NUMBER = CA.ACCOUNT_NUMBER INNER JOIN VCF_CARDHOLDER CH ON
CA.CARDHOLDER_IDENT = CH.CARDHOLDER_IDENT
WHERE CT.ACCOUNT_NUMBER=i_ACCOUNT_NUMBER AND
TRANS_REF_NUMBER=i_TRANS_REF_NUMBER AND
SEQUENCE_NUMBER=i_SEQUENCE_NUMBER AND
POSTING_DATE = i_POSTING_DATE;
-- count of number of rows that dont match new modified date
-- todo:fix up
SELECT count(*) INTO x FROM SUPP WHERE
ACCOUNT_NUMBER=i_ACCOUNT_NUMBER AND
TRANS_REF_NUMBER=i_TRANS_REF_NUMBER AND
SEQUENCE_NUMBER=i_SEQUENCE_NUMBER AND
POSTING_DATE = i_POSTING_DATE
AND DATE_MODIFIED <> i_NEWDATE_MODIFIED ;
-- if transaction exists in supp table x>0
IF (x>0) THEN
DELETE FROM SUPP WHERE
ACCOUNT_NUMBER=i_ACCOUNT_NUMBER AND
TRANS_REF_NUMBER=i_TRANS_REF_NUMBER AND
SEQUENCE_NUMBER=i_SEQUENCE_NUMBER AND
POSTING_DATE = i_POSTING_DATE
AND DATE_MODIFIED <> i_NEWDATE_MODIFIED ;
Commit;
END IF;
-- SELECT sysdate INTO vnewmodified_date FROM DUAL;
SELECT SEQ_ID.NEXTVAL INTO newID FROM DUAL;
INSERT INTO SUPP
(
ID,
SEQ,
DISPUTED_FLAG,
DISPUTED_CLEARING_ID,
PERSONAL_FLAG,
SUBMITTED_DATE,
SUBMITTED_GEN,
DESCRIPTION,
TRANSACTION_AMT,
RECEIPT_BATCH_NO,
PROJECT_NO,
MERCHANT_NAME
,TRANSACTION_DATE
,TRANSACTION_GEN
,DATE_MODIFIED
,ACCOUNT_NUMBER
,TRANS_REF_NUMBER
,SEQUENCE_NUMBER
,POSTING_DATE
,REJECTIONINFO
,EXPENDITURE_TYPE
,PERIOD
,INVOICE_CURRENCY_CODE
) VALUES (
newid,
i_SEQ,
i_DISPUTED_FLAG,
i_DISPUTED_CLEARING_ID,
i_PERSONAL_FLAG,
i_SUBMITTED_DATE,
i_SUBMITTED_GEN,
i_DESCRIPTION,
i_TRANSACTION_AMT,
i_RECEIPT_BATCH_NO,
i_PROJECT_NO
,vmerchant_name
,vtransaction_date
,vtransaction_gen
,i_NEWDATE_MODIFIED
,i_ACCOUNT_NUMBER
,i_TRANS_REF_NUMBER
,i_SEQUENCE_NUMBER
,i_POSTING_DATE
,i_REJECTIONINFO
,i_EXPENDITURE
,vperiod
,vbillingcurrency_code
);
Commit;
/*
IF i_PROJECT_NO is not NULL THEN
MRU_SAVE(i_SUBMITTED_GEN, i_PROJECT_NO);
END IF;
*/
END IF;
o_RETURNVALUE :=vgo;
END;
It seems to work intermittently and I am unable to determine the source of
the problem. I get the following exception when it does not run correct.
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
The last time, it did not run i had the following values
o_RETURNVALUE =
i_SEQ =
i_DISPUTED_FLAG =
i_EXPENDITURE = EQUIPMENT-CONSUMABLE
i_DISPUTED_CLEARING_ID =
i_PERSONAL_FLAG =
i_SUBMITTED_DATE =
i_SUBMITTED_GEN = 197
i_DESCRIPTION =
i_TRANSACTION_AMT = 72.18
i_RECEIPT_BATCH_NO =
i_PROJECT_NO =
i_DATE_MODIFIED =
i_ACCOUNT_NUMBER = 123456
i_TRANS_REF_NUMBER = 78910
i_SEQUENCE_NUMBER = 5
i_POSTING_DATE = 8/4/2006 12:00:00 AM
i_REJECTIONINFO =
i_NEWDATE_MODIFIED = 14-OCT-05
I am not sure if the problem is associated with date variable types or is
associated with something else.
