You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When occurs error in input/output parameters of SP/Function, error message non informative or show part of information.
CREATE OR ALTER PROCEDURE TEST_SP (
IN_DATE DATE NOT NULL,
PARAM VARCHAR(10) NOT NULL)
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE VR VARCHAR(5);
BEGIN
RESULT = :PARAM;
VR = :PARAM;
SUSPEND;
END
Example 1:
SELECT RESULT FROM TEST_SP('31.02.2018', '1234567890')
Overflow occurred during data type conversion.
conversion error from string "31.02.2018".
Example 2:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, '12345678901')
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 10, actual 11.
Example 3:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, NULL)
The insert failed because a column definition includes validation constraints.
validation error for variable PARAM, value "*** null ***".
At procedure 'TEST_SP'.
Example 4:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, 'qqq')
Overflow occurred during data type conversion.
conversion error from string "qqq".
At procedure 'TEST_SP' line: 9, col: 5.
Example 5:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, '123456')
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 5, actual 10.
At procedure 'TEST_SP' line: 10, col: 5.
In examples 1 and 2, unknown name of SP.
In examples 1, 2, 4 and 5, unknown name of parameter.
Sometimes it's make find reason of error is hard. And it takes a lot of time to finding name of parameter, where error occurred.
Here need extend info about a place and show is in uniform format, like:
Example 1:
Overflow occurred during data type conversion.
conversion error from string "31.02.2018".
At input parameter 'IN_DATE' of procedure 'TEST_SP'.
Example 2:
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 10, actual 11.
At input parameter 'PARAM' of procedure 'TEST_SP'.
Example 3:
The insert failed because a column definition includes validation constraints.
Value "*** null ***".
At input parameter 'IN_DATE' of procedure 'TEST_SP'.
Example 4:
Overflow occurred during data type conversion.
conversion error from string "qqq".
At output parameter 'RESULT' of procedure 'TEST_SP' line: 9, col: 5.
Example 5:
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 5, actual 10.
At variable 'VR' of procedure 'TEST_SP' line: 10, col: 5.
The text was updated successfully, but these errors were encountered:
Submitted by: Maxim Kuzmin (cybermax)
Votes: 1
When occurs error in input/output parameters of SP/Function, error message non informative or show part of information.
CREATE OR ALTER PROCEDURE TEST_SP (
IN_DATE DATE NOT NULL,
PARAM VARCHAR(10) NOT NULL)
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE VR VARCHAR(5);
BEGIN
RESULT = :PARAM;
VR = :PARAM;
SUSPEND;
END
Example 1:
SELECT RESULT FROM TEST_SP('31.02.2018', '1234567890')
Overflow occurred during data type conversion.
conversion error from string "31.02.2018".
Example 2:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, '12345678901')
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 10, actual 11.
Example 3:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, NULL)
The insert failed because a column definition includes validation constraints.
validation error for variable PARAM, value "*** null ***".
At procedure 'TEST_SP'.
Example 4:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, 'qqq')
Overflow occurred during data type conversion.
conversion error from string "qqq".
At procedure 'TEST_SP' line: 9, col: 5.
Example 5:
SELECT RESULT FROM TEST_SP(CURRENT_DATE, '123456')
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 5, actual 10.
At procedure 'TEST_SP' line: 10, col: 5.
In examples 1 and 2, unknown name of SP.
In examples 1, 2, 4 and 5, unknown name of parameter.
Sometimes it's make find reason of error is hard. And it takes a lot of time to finding name of parameter, where error occurred.
Here need extend info about a place and show is in uniform format, like:
Example 1:
Overflow occurred during data type conversion.
conversion error from string "31.02.2018".
At input parameter 'IN_DATE' of procedure 'TEST_SP'.
Example 2:
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 10, actual 11.
At input parameter 'PARAM' of procedure 'TEST_SP'.
Example 3:
The insert failed because a column definition includes validation constraints.
Value "*** null ***".
At input parameter 'IN_DATE' of procedure 'TEST_SP'.
Example 4:
Overflow occurred during data type conversion.
conversion error from string "qqq".
At output parameter 'RESULT' of procedure 'TEST_SP' line: 9, col: 5.
Example 5:
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
expected length 5, actual 10.
At variable 'VR' of procedure 'TEST_SP' line: 10, col: 5.
The text was updated successfully, but these errors were encountered: