Dapper cannot run procedure and return exceptions PLS-00221

Question

I have the package in Oracle which contains the pipelined function. The function is worked. But I have problem to run this procedure via Dapper.

If I use next code, I get results.

var hostname = "calculation_workers";
var functionName = "MURR_IMPORT.MURR_TASKS.GET_ALL_SERVICES";
var parameters = new OracleDynamicParameters();
parameters.Add("inHostName", hostname, OracleMappingType.Varchar2, ParameterDirection.Input);
var result = dbConnection.Query<DbHostsMap>("SELECT * FROM MURR_IMPORT.MURR_TASKS.GET_ALL_SERVICES(:inHostName)", 
   parameters, 
   commandType: CommandType.Text);

If I try use CommandType.StoredProcedure, I get the exception PLS-00221: 'GET_ALL_SERVICES' is not a procedure or undefined

var hostname = "calculation_workers";
var functionName = "MURR_IMPORT.MURR_TASKS.GET_ALL_SERVICES";
var parameters = new OracleDynamicParameters();
parameters.Add("inHostName", hostname, OracleMappingType.Varchar2, ParameterDirection.Input);
//Here I get the exception
var result = dbConnection.Query<DbHostsMap>(functionName, 
   parameters, 
   commandType: CommandType.StoredProcedure);

How to solve problem with PLS-00221 ?

Answer 1

You need to wrap it with table():

SELECT * 
FROM table(MURR_IMPORT.MURR_TASKS.GET_ALL_SERVICES(:inHostName))