I need return a variable oracle to C#, however I use Dapper, so exist a way to receive a output by dapper?
I tried like this
ORACLE ... ,NULL /*CLIENTE_GRUPO_CPF*/ ,'P' /*LOTE_PRODUCAO_PEDIDO*/ ,'N' /*LIBERACAO_COMERCIAL*/ ,NULL /*USUARIO_LIB_COMERCIAL*/ ,NULL /*DATA_LIB_COMERCIAL*/ ,NULL /*HORA_LIB_COMERCIAL*/ ,'L' ); COMMIT; dbms_output.put_line(var_zi_controle_id); END ; C# var id = conn.QueryFirstOrDefault<int>(sql);
but this way the answer is 0.
dbms_output.put_line(var_zi_controle_id); to Output a variable is incorrect, this would not work with programming languages / database driver like ODP.Net, this is for console printing and verification.
Dapper consist of set of extension methods which extends
IDbConnection interface of ADO.Net, internally for Oracle you would fill in
ODP.Net - OracleConnection object, now when you check the ODP.Net guide for calling any of the PL SQL Stored procedures, you have following options:
DynamicParameterfor binding parameters beside Input like Output / Return
Selectstatement to return the value as integer, so what you need is
Select var_zi_controle_idin the procedure, which will come as a QueryResult, but still you would need a Type T, which contains an integer value, here you would get a Type / class with one integer property
My Preferred way would be adding the Output parameter, assign in the Stored Procedureand retrieve value, check the links underneath for good reference. A simple example would be:
var dynamicParameters = new DynamicParameters(); dynamicParameters.Add("var_zi_controle_id",0,DbType.Int32,ParameterDirection.Output)
Parameter Direction options
ReturnValue can also be used, but
ReturnValue needs explicit return value from the procedure
// Execute the Stored Procedure
conn.Execute(@"ProcedureName", dynamicParameters, commandType: CommandType.StoredProcedure);
// Fetch O/p Parameter Value from
var id = dynamicParameters.Get<int>("var_zi_controle_id");
Its important that Output parameter
var_zi_controle_id, which bind to the stored procedure is set, before the call returns, for value to be fetched