I'm trying to call a function that returns a cursor but I always get the same error:
ORA-06550: linha 1, coluna 7:
PLS-00306: wrong number or types of arguments in call to 'SF_OBTER_ALVOS'
ORA-06550: linha 1, coluna 7:
PL/SQL: Statement ignored
The function in the package is:
FUNCTION SF_OBTER_MENS( IDDIS IN MENSAGERIA.DIS_DISPOSITIVO.DIS_ID%type )
RETURN SYS_REFCURSOR
AS
MENSAGS SYS_REFCURSOR;
BEGIN
OPEN MENSAGS FOR
SELECT ALV.ALV_ID,
MSA.MSA_ID, TO_CHAR( MSA.MSA_DTNOTIFIC, 'YYYYMMDDHH24MI') AS MSA_DTNOTIFIC,
MSG.MSG_ID, MSG.MSG_DCTITULO,
MSG.MSG_CDUSUARIO, MSG.MSG_DLREMETENTE, MSG.MSG_DTCRIACAO,
MSG.MSG_STCONFIDEN
FROM MENSAGERIA.DIS_DISPOSITIVO DIS
INNER JOIN MENSAGERIA.ALV_ALVO ALV ON
ALV.DIS_ID=DIS.DIS_ID AND
ALV.ALV_DTFIM IS NULL
INNER JOIN MENSAGERIA.MSA_MENSAGEM_ALVO MSA ON MSA.ALV_ID=ALV.ALV_ID AND
MSA.MSA_DTRECEBE IS NULL AND MSA.MSA_DTDELETADA IS NULL
INNER JOIN MENSAGERIA.MSG_MENSAGEM MSG ON MSG.MSG_ID=MSA.MSG_ID
WHERE DIS.DIS_ID=IDDIS;
RETURN MENSAGS;
END SF_OBTER_MENS;
where MENSAGERIA.DIS_DISPOSITIVO.DIS_ID%type is NUMBER(10,0)
and the program section where I call the function is:
var conn = ctx.Database.GetDbConnection();
await conn.OpenAsync();
var parm = new OracleDynamicParameters();
parm.Add("IDDIS", dbType: Dapper.Oracle.OracleMappingType.Int32, direction: ParameterDirection.Input);
parm.Add("RESULT", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
return ctx.Database.GetDbConnection().Query("MENSAGERIA.PKG\_FALA\_SANTOS.SF\_OBTER\_MENS", param: parm, commandType: CommandType.StoredProcedure);
I appreciate any help.