Getting a value from a SQL Function using ParameterDirection

Question

This is the SQL Function:

create or replace FUNCTION "TEMPXML"
(
 ID IN NUMBER
)
RETURN CLOB IS
val CLOB;
BEGIN
 val:=null;
 BEGIN
  SELECT TEMPID INTO val FROM TEMPDATA WHERE TempdataID=I;
  exception
  when no_data_found then
   null;
 end;
return val;
END TEMPXML;

In C# I am using Parameter.ReturnValue but the value returned is always DBNull and I get an Exception Unable to cast object of type 'System.DBNull' to type 'System.String'

sqlCmdSelectReports.Parameters.Add(new OleDbParameter("@xmldata", OleDbType.LongVarChar)).Direction = ParameterDirection.ReturnValue;
sqlCmdSelectReports.ExecuteNonQuery();
String xmlValue = (String)sqlCmdSelectReports.Parameters["@xmldata"].Value;

The last line throws an Exception. Do I use a wrong code in C# or the SQL Function is wrong coded?

Answer 1

It looks like you're simply getting a null back from the database, which isn't entirely unexpected (no_ata_found, etc)

Just check for that; one way (of several) is:

string xmlValue = sqlCmdSelectReports.Parameters["@xmldata"].Value as string;