Oracle ODP.NET stored procedure help needed (PLEASE)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using asp.net 2.0 and the latest 10.x odp.net driver to execute an
update stored procedure in Oracle 9.x.

I have been getting the following intermittent error
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

It seems to run sometimes and not other times. I have been thinking it was
related to my date parameters but I am really not sure.

I found a post recently that mentioned setting the date format after the
connection is opened (see following)....

Dim dateformatvar As String = "MM/dd/yyyy HH:MI:ss AM"
Dim threadGlob As OracleGlobalization =
OracleGlobalization.GetThreadInfo()
threadGlob.DateFormat = dateformatvar

Dim Param19 As New OracleParameter("i_MODIFIED", OracleDbType.Date,
ParameterDirection.Input)
Param19.Value = DateTime.Parse(NewDateModified.ToString("M/d/yyyy
h:mm tt"))

the above is an example of how i am passing in dates. As mentioned I am not
sure if this is the source of my error. It seems that the sproc will run
fine once, but minutes later it will error out with the 6502 oracle error. I
have searched online but am at a loss at this point.
Any help or advice dealing with odp.net and executing procedures with
parameters would be very much appreciated.

I have ensured I am passing in the correct type and order of the parameters.
thx
dave
 
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.
 
I can't tell just from the proc, but the best way to debug it is through SQL
Plus (also note that the error may be in MRU_SAVE)
Open SQL*PLUS and execute the procedure using the values that you put when
it was failing.

I'd create a the exact same procedure and add _TEST, a few
DBMS_OUTPUT.PUT_LINE('something to help debug') throught the procedure and
then do something like:

SET SERVEROUTPUT ON
EXEC SUBMIT_SAVE_TEST (NULL, NULL, 'EQUIPMENT-CONSUMABLE', NULL, NULL,
NULL,197,NULL72.18,NULL,NULL,NULL,'123456','78910',5, '8/4/2006 12:00:00
AM',NULL,'14-OCT-05');

Also, in Oracle, you should error handle the procedure and never issue the
commit in the proc... but that's a whole different subject
 
I appreciate the tips. The problem however is not in the oracle proc. I
have been able to execute fine in pl/sql developer. The MRU_Save proc I have
commented out to elminate that as a potential problem.
For a while I was thinking it had to to with 1) how i was passing in the
date's and and potentially how i was comparing them in the proc. (thinking
that from .net the date is getting passed in differently than throuhg sqlplus
or pl/sql developer)
Considering that I do not get the error when I run through oracle I presume
the error is related to .net and how i am passing in the parameters.
Any other tips related to passing in dates or value from .net would be very
much appreciated.
thx
dave
 
Open sql*plus and see if this works:

-->

declare
v_out number;
begin
SUBMIT_SAVE (v_out, NULL, null, 'EQUIPMENT-CONSUMABLE', null, NULL,
NULL,197,NULL,72.18,NULL,NULL,NULL,'123456','78910',5, '8/4/2006 12:00:00
AM',NULL,'14-OCT-05');
end;
/
--<--

Then try


-->
declare
v_out number;
begin
SUBMIT_SAVE (v_out, NULL, null, 'EQUIPMENT-CONSUMABLE', null, NULL,
NULL,197,NULL,72.18,NULL,NULL,NULL,'123456','78910',5,
'4-AUG-2006',NULL,'14-OCT-05');
end;
/
--<--

Both work?
Both Fail?
Second one work?
 
I too had this same problem and like you, I thought the problem was
outside the Oracle stored procedure as the sp would work properly at
the SQLPlus prompt. It turned out that I had to pass all arguments to
the sp.

Vint
 
Back
Top