Q 1:
Whenever an Oracle SP wants to return error code/message, it fails returning below exeption:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB.GET_COST", line 244
ORA-01403: no data found
ORA-06512: at line 1".
I am calling it from MS SQL via a linked server as follows:
declare
@id1 int
,@id2 varchar(8)
,@code varchar(10)
,@curr varchar(4)
,@ed datetime
,@return_status int
,@error_code varchar(10)
,@error_msg varchar
,@landed_cost numeric(14,5)
begin try
exec ('BEGIN db.get_cost(?,?,?,?,?,?,?,?,?); END;'
,@code
,@id1
,@id2
,@curr
,@ed
,@return_status output
,@error_code output
,@error_msg output
,@cost output
) AT ORASRV
end try
begin catch
select
@return_status as return_status
,@error_code as error_code
,@error_msg as error_msg
end catch
It seems that the input parameters work fine, as their values are such that when the SP is returning a correct result, everything is working. Only the IN parameter values could cause the SP to return an error code, when the error above is thrown.
The declaration on the Oracle side is as follows:
PROCEDURE get_cost (
code IN VARCHAR2(10 BYTE),
id1 IN NUMBER(7,0),
id2 IN VARCHAR2(8 BYTE),
curr IN VARCHAR2(4 BYTE),
ed IN DATE,
return_status OUT NUMBER
error_code OUT VARCHAR2(10 BYTE)
error_msg OUT VARCHAR2,
cost OUT NUMBER(14,5)
)
Q 2:
To add insult to injury, after throwing the above exception 3 times, and working fine once, the script then dies with the following MS SQL error message:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
It dies even if I exclude the 1st 3 calls that cause the original error and just call the SP successfully once.
Best Answer
As stated in the comments, both errors have been caused by declaring
@error_msg varchar
where it should have been,@error_msg varchar(max)