I have an application which uses Oracle 9i and Visual Basic 6.0 ( Fron end). I am passing a user defined text message by using raise_application_error (-20700, error_desc) from Oracle 9i to Visual Basic 6.0 . Here, Oracle may send upto 10000 characters to Visual Basic front end.
I am capturing oracle error message (raised by raise_application_error procedure) in Visual Basic in below way i.e .
-- session creation
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.dbopendatabase(UserID.sOraSecDB, "XXXXX/XXXXX", 0&)
Public Sub Validate (dbConnection As Object, TSRPData As datTSRP_Record)
On Error GoTo Error
sysError.HandleOracleError Err.Number, dbConnection
On Error GoTo 0
'Invoke Stored Procedure to validate the VCA of the Timed SRP
sSQL = "begin PA_ATD_ATS.PR_VALIDATE_TSRP(:pnTim_Srp_Id); end;"
After running above procedure PR_VALIDATE_TSRP , raise_application_error procedure will returns the error text message which is captured by user defined method "HandleOracleError"
Public Sub HandleOracleError(ErrNum As Variant, OraDatabase As Object)
If ErrNum = 440 Then
'OLE Error - i.e. Oracle Objects Error
ovnSysErrorNumber = OraDatabase.LastServerErr
ovsSysErrorText = OraDatabase.LastServerErrText
In the above user defined method, OraDatabase.LastServerErrText captures only 532 characters.. if raise_application_error returns more than 532 characters in the sense, OraDatabase.LastServerErrText captures only 532 characheters and rest of characacters are truncated.
Please remember above coding is working fine. My only issue is that error truncation i.e OraDatabase.LastServerErrText not capturing more than 532 characters. And also When I try in debug mode, if i try to pring Err.description value after running PA_ATD_ATS.PR_VALIDATE_TSRP , it's also not capturing more than 532 characters..
I guess OLE error object's LastServerErrText property NOT able to capture more than 532 characheters. When I look at the LastServerErrText in oracle documentation , I don't see any limit is specified for method OraDatabase.LastServerErrText ( http://download.oracle.com/docs/cd/B19306_01/win.102/b14309/serprop.htm#CHDIBDGF )
Is there any limit OLD object LastServerErrText can capture only this much charachters ( 532 characheters)?
and Please guide me how i can make the adjustment to capture more than 532 characters by using same OLE object method. my user looking to see at least 10,000 characheters , he does not want to see that error message truncation.
I tested 11g, and see 511 as the max. I'd assume this is an undocumented limitation of LastServerErrText, as I wasnt able to find it in the docs.
Although it seems odd to me that you'd want to return that much data in an error message, if you have a requirement for that you might consider storing the data in a table instead, and querying the table after receiving notification of the problem.