Home Dapper making Oracle call with output parameters gives "PL/SQL: numeric or value error: DML Returning: Error writing to host variable"

Questions

Dapper making Oracle call with output parameters gives "PL/SQL: numeric or value error: DML Returning: Error writing to host variable"

byMR

December 8, 2021

I am trying to use Dapper to call some Oracle SQL statements that I need to run, but am getting this error

ORA-06502: PL/SQL: numeric or value error: DML Returning: Error writing to host variable
ORA-06512: at line 11

I currently have the code below, I just have a problem getting new Key that is generated NewNameAddRKey I am passing this in the parameter as an output one, but I get the above error, The R_KEY column is a VARCHAR2(5 BYTE) column, so for some reason it can’t convert that to a string?, I could really do with some help on this, as its very slow going.

var parameters = new DynamicParameters(rowToAdd);

parameters.Add(name: "NewNameAddRKey", dbType: DbType.String, direction: ParameterDirection.Output);
parameters.Add(name: "firstCharacterOfName", value: rowToAdd.FirstCharacterOfName, dbType: DbType.String, direction: ParameterDirection.Input);

await context.Connection.ExecuteAsync(
    @"
    DECLARE 
    initialChar VARCHAR2(1) := :firstCharacterOfName;
    startsWith VARCHAR2(1) := 'L';
    newKey VARCHAR2(5);
    status NUMBER;
    message VARCHAR2(200);
    BEGIN
        CUSTOMER_PKG.name_add_key(initialChar, startsWith, newKey, status, message);
        
        INSERT INTO NAME_ADD(R_KEY, ADDRESS_1, ADDRESS_2,ADDRESS_3, ADDRESS_4, POST_CODE, CONTACT, FAX_NO, NAME, PHONE_NO, TELEX_NO)
        VALUES(newKey, :AddressLine1, :AddressLine2, :AddressLine3, :AddressLine4, :PostCode, :OfficeContact, :Fax, :Name, :HomePhone, :Telex)
        RETURNING R_KEY INTO :NewNameAddRKey;

    END;",
    parameters,
    context.Transaction);

>Solution :

In the code you’ve posted there is not size for the string variable defined whereas in Oracle every variable of that kind must have a predefined length.

The Dapper, probably defines some default length which is not enough in your case.

As we discussed in the comments, this is the solution:

parameters.Add(name: "NewNameAddRKey", 
               dbType: DbType.String, 
               /* --> */ size: 5, /* <--- */
               direction: ParameterDirection.Output);