Call Oracle Function From .NET As Stored Procedure

Question

I have written a simple Oracle function called Testing with no parameters that returns a string. When I try to call it from my .NET app, I get an error:

System.Data.OracleClient.OracleException:

ORA-06550: line 1, column 7:" "PLS-00221: 'TESTING2' is not a procedure or is undefined".

When I change it to do "Select testing() from dual" and change CommandType to Text, it works. What am I missing?

Dim oracleConn As OracleConnection = CreateConnection(<connection info here>)

    Dim oracleCmd As New OracleCommand()
    oracleCmd.Connection = oracleConn
    'oracleCmd.CommandText = "SELECT TESTING2() FROM DUAL" 'this works
    oracleCmd.CommandText = "TESTING2"   'this does not work

    oracleCmd.CommandType = CommandType.StoredProcedure

    'oracleCmd.ExecuteReader()  'also tried this
    Dim tmpVar As String = oracleCmd.ExecuteScalar()

create or replace FUNCTION testing2
RETURN VARCHAR2
AS      
begin
  return 'hello';
end;

have you granted execution on that sproc for the user you're using to log on the oracle server? have u defined a sproc inside a package ? — codingadventures
I did do execute grants for all the roles assigned to my user. I have tried calling the function as part of a package, too, but same error. — Kelly

Steve Steve · Accepted Answer · 2013-06-10T14:41:00

I don't work with Oracle anymore, so I can't test it now, but you tell me if this example works or not

Dim oracleConn As OracleConnection = CreateConnection(<connection info here>)
Dim oracleCmd As New OracleCommand()
oracleCmd.Connection = oracleConn
oracleCmd.CommandText = "TESTING2"   
oracleCmd.CommandType = CommandType.StoredProcedure
Dim prm = new OracleParameter("returnvalue", OracleType.VarChar)
prm.Size = 1024
prm.Direction = ParameterDirection.ReturnValue
oracleCmd.Parameters.Add(prm)
oracleCmd.ExecuteNonQuery()

Console.WriteLine(prm.Value.ToString)

And by the way, I suppose that CreateConnection returns an OPEN connection right?

Call Oracle Function From .NET As Stored Procedure

1 Answers