Data provider internal error(-3000)
One particular procedure always throws:
ApplicationDALC.UpdateSession</message>
<source>EI.Application.Data.DALC</source>
<stackTrace>at EI.Application.Data.DALC.ApplicationDALC.UpdateSession(String sessionGUID, String sXMLSession) at EI.Application.Web.AWBasePage.SaveViewState() at System.Web.UI.Control.SaveViewStateRecursive() at System.Web.UI.Page.SavePageViewState() at System.Web.UI.Page.ProcessRequestMain()</stackTrace>
- <innerException>
- <exception>
<message>Data provider internal error(-3000)</message>
<source>Oracle Data Provider for .NET</source>
<stackTrace>at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure, String[] args) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String[] args) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at EI.Application.Data.DALC.ApplicationDALC.UpdateSession(String sessionGUID, String sXMLSession)
Below is the code both the middle teir and the db respectively:
Public Sub RetrieveSession(ByVal tmpPassword As String, ByVal sOrderedSharedSecrets As String, ByRef sXMLResult As String)
Dim oraConnection As New Oracle.DataAccess.Client.OracleConnection(sConnString)
Dim oraCommand As New Oracle.DataAccess.Client.OracleCommand()
Dim reader As Oracle.DataAccess.Client.OracleDataReader
Dim sMethodName As String = "ApplicationDALC.RetrieveSession"
Try
With oraCommand
.Connection = oraConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "AW__CREATE_APP.retrieveSession"
End With
oraCommand.Parameters.Add("tmpPassword", Oracle.DataAccess.Client.OracleDbType.Varchar2, tmpPassword, ParameterDirection.Input)
oraCommand.Parameters.Add("sOrderedSharedSecrets", Oracle.DataAccess.Client.OracleDbType.Varchar2, sOrderedSharedSecrets, ParameterDirection.Input)
oraCommand.Parameters.Add("sXMLResult", Oracle.DataAccess.Client.OracleDbType.Clob, DBNull.Value, ParameterDirection.Output)
oraConnection.Open()
reader = oraCommand.ExecuteReader()
reader.Read()
sXMLResult = CType(oraCommand.Parameters("sXMLResult").Value, Oracle.DataAccess.Types.OracleClob).Value
reader.Dispose()
Catch OraError As Oracle.DataAccess.Client.OracleException
If OraError.Number = 20101 Then
Throw New TemporaryPasswordException()
ElseIf OraError.Number = 20102 Then
Throw New SharedSecretException()
ElseIf OraError.Number = 20103 Then
Throw New AttemptsExceededException()
ElseIf OraError.Number = 20104 Then
Throw New MoreThanValidSessionException()
Else
Throw New System.Exception(sMethodName, OraError)
End If
Catch VBerror As Exception
Throw New System.Exception(sMethodName, VBerror)
Finally
oraCommand.Dispose()
oraConnection.Close()
End Try
End Sub
Public Sub UpdateSession(ByVal sessionGUID As String, ByVal sXMLSession As String)
Dim oraConnection As New Oracle.DataAccess.Client.OracleConnection(sConnString)
Dim oraCommand As New Oracle.DataAccess.Client.OracleCommand()
Dim oraTransaction As Oracle.DataAccess.Client.OracleTransaction
Dim iChunk As Integer
Dim sMethodName As String = "ApplicationDALC.UpdateSession"
Try
oraConnection.Open()
oraTransaction = oraConnection.BeginTransaction()
For iChunk = 1 To Math.Ceiling(sXMLSession.Length / CHUNK_SIZE)
With oraCommand
.Connection = oraConnection
'.Transaction = oraTransaction
.CommandType = CommandType.StoredProcedure
.CommandText = "AW__CREATE_APP.updateSession"
End With
oraCommand.Parameters.Add("sessionGUID", Oracle.DataAccess.Client.OracleDbType.Varchar2, sessionGUID, ParameterDirection.Input)
oraCommand.Parameters.Add("sXMLSession", Oracle.DataAccess.Client.OracleDbType.Varchar2, sXMLSession, ParameterDirection.Input)
oraCommand.Parameters.Add("iChunk", Oracle.DataAccess.Client.OracleDbType.Decimal, iChunk, ParameterDirection.Input)
If iChunk <> Math.Ceiling(sXMLSession.Length / CHUNK_SIZE) Then
oraCommand.Parameters("sXMLSession").Value = sXMLSession.Substring((iChunk - 1) * CHUNK_SIZE, CHUNK_SIZE)
Else
oraCommand.Parameters("sXMLSession").Value = sXMLSession.Substring((iChunk - 1) * CHUNK_SIZE)
End If
oraCommand.ExecuteNonQuery()
Next
oraTransaction.Commit()
Catch ex As System.Exception
oraTransaction.Rollback()
Throw New System.Exception(sMethodName, ex)
Finally
oraCommand.Dispose()
oraConnection.Close()
oraConnection.Dispose()
End Try
End Sub
Public Sub UpdateSession(ByVal sessionGUID As String, ByVal sXMLSession As String, ByVal sOrderedSharedSecrets As String)
Dim oraConnection As New Oracle.DataAccess.Client.OracleConnection(sConnString)
Dim oraCommand As New Oracle.DataAccess.Client.OracleCommand()
Dim oraTransaction As Oracle.DataAccess.Client.OracleTransaction
Dim iChunk As Integer
Dim sMethodName As String = "ApplicationDALC.UpdateSession"
Try
oraConnection.Open()
oraTransaction = oraConnection.BeginTransaction()
For iChunk = 1 To Math.Ceiling(sXMLSession.Length / CHUNK_SIZE)
With oraCommand
.Connection = oraConnection
'.Transaction = oraTransaction
.CommandType = CommandType.StoredProcedure
.CommandText = "AW__CREATE_APP.updateSession"
End With
oraCommand.Parameters.Add("sessionGUID", Oracle.DataAccess.Client.OracleDbType.Varchar2, sessionGUID, ParameterDirection.Input)
oraCommand.Parameters.Add("sXMLSession", Oracle.DataAccess.Client.OracleDbType.Varchar2, sXMLSession, ParameterDirection.Input)
oraCommand.Parameters.Add("sOrderedSharedSecrets", Oracle.DataAccess.Client.OracleDbType.Varchar2, sOrderedSharedSecrets, ParameterDirection.Input)
oraCommand.Parameters.Add("iChunk", Oracle.DataAccess.Client.OracleDbType.Decimal, iChunk, ParameterDirection.Input)
If iChunk <> Math.Ceiling(sXMLSession.Length / CHUNK_SIZE) Then
oraCommand.Parameters("sXMLSession").Value = sXMLSession.Substring((iChunk - 1) * CHUNK_SIZE, CHUNK_SIZE)
Else
oraCommand.Parameters("sXMLSession").Value = sXMLSession.Substring((iChunk - 1) * CHUNK_SIZE)
End If
oraCommand.ExecuteNonQuery()
Next
oraTransaction.Commit()
Catch ex As System.Exception
oraTransaction.Rollback()
Throw New System.Exception(sMethodName, ex)
Finally
oraCommand.Dispose()
oraConnection.Close()
oraConnection.Dispose()
End Try
End Sub
AND
PROCEDURE updateSession (
sessionGUID in varchar2,
sXMLSession in varchar2,
sOrderedSharedSecrets in varchar2,
iChunk in number
) IS
lob CLOB;
BEGIN
--update the CLOB
updateSession(sessionGUID, sXMLSession, iChunk);
--update the validation_string_text and audit the last update
UPDATE ei_web_user_session
SET validation_string_text = upper(substr(sessionGUID,29,4) || '-' || substr(sessionGUID,33)) || sOrderedSharedSecrets,
unsuccessful_attempt_count = 0
WHERE session_id = sessionGUID;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
PROCEDURE updateSession (
sessionGUID in varchar2,
sXMLSession in varchar2,
iChunk in number
) IS
lob CLOB;
BEGIN
--If we have the first chunk of the CLOB, we need to empty the CLOB first,
--otherwise we just write the chunk at the end of the existent clob
if iChunk = 1 then
--empty the existent clob
update ei_web_user_session
set session_xml = empty_clob()
where session_id = sessionGUID;
end if;
--update the clob
select session_xml
into lob
from ei_web_user_session
where session_id = sessionGUID
for update;
DBMS_LOB.write(lob, length(sXMLSession), (DBMS_LOB.GETLENGTH(lob)+1), sXMLSession);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
Thanks for any advise