3

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.

3

1 Answer 1

1

As stated in the comments, both errors have been caused by declaring @error_msg varchar where it should have been ,@error_msg varchar(max)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.