CODEWITHSUNDEEP
c#oracle-databaseoracle11g
Sachu
Sachu

Reputation: 7766

calling a function from oracle in C# giving error

I am calling a function from oracle package which will return a string as below

using(OracleConnection con = AppConn.Connection)
{
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = con;
    cmd.CommandText = "select P_Pkg.found(@p_id) from dual";
    OracleParameter p_id = new OracleParameter();
    p_id.OracleDbType = OracleDbType.Int64;
    p_id.Direction = ParameterDirection.Input;
    p_id.Value = requestHeader.approval_id;

    cmd.Parameters.Add(p_id);
    try
    {
        con.Open();
        string found = cmd.ExecuteScalar().ToString();

    }
    catch(Exception ex)
    {

    }
    finally
    {
        con.Close();
    }
}

but i am getting the below error . I can't find the problem after a lot of search. Please help.

ORA-06550: line 1, column 51:

PL/SQL: ORA-00936: missing expression

ORA-06550: line 1, column 7:

Function signature in oracle

P_Pkg.found(p_id IN NUMBER) 
RETURN varchar2 //(YES , NO)

I run this from oracle as below and got the result with out any error

select P_Pkg.found(1053) from dual

Upvotes: 2

Views: 679

Answers (2)

Matteo Umili
Matteo Umili

Reputation: 4017

To call a stored procedure you just have to set the command text to the stored procedure name, then add the parameters with the correct name

using (OracleConnection con = new OracleConnection(AppConn.Connection))
{
    using (OracleCommand cmd = con.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.CommandText = "P_Pkg.found";
        OracleParameter p_id = new OracleParameter();
        p_id.ParameterName = "p_id";
        p_id.OracleDbType = OracleDbType.Int64;
        p_id.Direction = ParameterDirection.Input;
        p_id.Value = requestHeader.approval_id;
        OracleParameter retVal = new OracleParameter();
        retVal.ParameterName = "ReturnValue";
        retVal.OracleDbType = OracleDbType.Varchar2; // Whatever the type the SP returns
        retVal.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(p_id);
        cmd.Parameters.Add(retVal);
        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            string found = cmd.Parameters["ReturnValue"].Value.ToString();
        }
        catch (Exception ex)
        {

        }
        finally
        {
            con.Close();
        }
    }
}

Upvotes: 3

Patrick Hofman
Patrick Hofman

Reputation: 156978

The problem lies in the use of the SQL Server specific prefix for parameters / bind variables. Oracle uses : instead of @. The SQL parser doesn't understand your query now.

Use this code instead:

select P_Pkg.found(:p_id) from dual

Upvotes: 3

Related Questions