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);
CodePudding user response:
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);