Home > DeveloperSection > Forums > Call Oracle Function From .NET As Stored Procedure
ezra heywood
ezra heywood

Total Post:145

Points:1019
Posted on    June-11-2013 8:02 AM

 VB.Net VB.Net 
Ratings:


 1 Reply(s)
 1087  View(s)
Rate this:
Hi Developers, 

My Function return following error

"System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:" & vbLf & "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;

Any help would be amazing thank you!



AVADHESH PATEL

Total Post:604

Points:4228
Posted on    June-12-2013 7:40 AM

Hi Ezra,

You can try as following 

replace your line of code from the following line of code 

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)

I hope it resolve your problem. 

Don't want to miss updates? Please click the below button!

Follow MindStick