Reputation: 7514
I get a return value back from this Oracle function when executing it from Oracle SQL Developer (SELECT OSIEXTN.Validate_ID ('pers',1315) FROM DUAL;
):
create or replace FUNCTION Validate_ID(v_Type IN VARCHAR2,
v_ID IN NUMBER)
RETURN VARCHAR2
IS
v_validation VARCHAR2 (10);
v_cnt NUMBER;
v_ext NUMBER;
BEGIN
IF v_type = 'org'
THEN
SELECT COUNT (*) INTO v_cnt
FROM org
WHERE orgnbr = v_ID;
CASE v_cnt
WHEN 1
THEN
v_validation := 'True';
ELSE
v_validation := 'False';
END CASE;
ELSIF v_type = 'pers'
THEN
SELECT COUNT (*) INTO v_cnt
FROM pers
WHERE persnbr = v_ID;
CASE v_cnt
WHEN 1
THEN
v_validation := 'True';
ELSE
v_validation := 'False';
END CASE;
ELSIF v_type = 'acct'
THEN
SELECT COUNT (*) INTO v_cnt
FROM acct
WHERE acctnbr = v_ID;
SELECT COUNT(*) INTO v_ext
FROM EXTENTITY
WHERE EXTENTITYID = v_ID;
CASE v_cnt
WHEN 1
THEN
v_validation := 'True';
ELSE
CASE v_ext
WHEN 1
THEN
v_validation := 'True';
ELSE
v_validation := 'False';
END CASE;
END CASE;
END IF;
RETURN v_validation;
END;
But when calling it from this C# code, the value coming back is simply {}
.
using (IDbConnection conn = DatabaseFactory.CreateConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "OSIEXTN.Validate_ID";
cmd.AddParameter(
"v_Type",
ParameterDirection.Input,
OracleDbType.Varchar2,
entityType);
cmd.AddParameter(
"v_ID",
ParameterDirection.Input,
OracleDbType.Long,
id);
OracleParameter retVal = new OracleParameter("retVal", OracleDbType.Varchar2);
retVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retVal);
cmd.ExecuteNonQuery();
object o = retVal.Value;
}
}
Why am I not seeing a "True" or "False"?
Upvotes: 0
Views: 763
Reputation: 71
Just a small comment; as Gary commented, the function call name doesn't match.
The issue is occurring because you are trying to call a function like a stored procedure with
cmd.CommandType = CommandType.StoredProcedure;
which doesn't work for functions. Either create a stored procedure that calls the function, or use the following approach(plsql implementation is mocked for demo purposes);
CREATE OR REPLACE FUNCTION SAMPLEFUNCTION (v_Type IN VARCHAR2,
v_ID IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN 'True';
END SAMPLEFUNCTION;
Then call the function with the following code;
var sqlToExecute = "BEGIN " +
"SELECT SAMPLEFUNCTION(:v_Type, :v_ID) INTO :retVal FROM DUAL; " +
"END;";
using (var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
{
conn.Open();
using (var cmd = new OracleCommand(sqlToExecute, conn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlToExecute;
// Input parameters
var oraParamVtype = new OracleParameter("v_Type", OracleDbType.Varchar2, ParameterDirection.Input);
oraParamVtype.Value = "A";
cmd.Parameters.Add(oraParamVtype);
var oraParamVid = new OracleParameter("v_ID", OracleDbType.Long, ParameterDirection.Input);
oraParamVid.Value = 1;
cmd.Parameters.Add(oraParamVid);
// Return value
var retVal = new OracleParameter("retVal", OracleDbType.Varchar2, ParameterDirection.Output) { Size = 5 };
cmd.Parameters.Add(retVal);
cmd.ExecuteNonQuery();
Console.WriteLine($"Query result: {retVal.Value}");
}
}
}
Please note that i used CommandType.Text
Upvotes: 1