calling a function from oracle in C# giving error

Question

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

Can't you set CommandText to just P_Pkg.found and then add the parameter giving it the correct name? (p_id) — Matteo Umili
– Matteo Umili, Commented Sep 30, 2015 at 7:20

Patrick Hofman · Accepted Answer · 2015-09-30 07:11:12Z

3

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

answered Sep 30, 2015 at 7:11

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sachu Over a year ago

it's giving me the erro ORA-06550: line 1, column 75: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 1, column 7:

Sachu Over a year ago

select P_Pkg.found(1053) from dual from toad is working fine

Sachu Over a year ago

u need entire pckg? or only the function im calling?

Matteo Umili Over a year ago

Toad executes a statement, in C# you are telling that the type of the command is StoredProcedure, your statement may work if you specify CommandText

Patrick Hofman Over a year ago

Just a dummy package would do, if you can reproduce the issue with it.

|

Matteo Umili · Accepted Answer · 2015-09-30 08:34:43Z

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();
        }
    }
}

Collectives™ on Stack Overflow

calling a function from oracle in C# giving error

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related