Using Oracle Function with return value in c# application

Question

CREATE OR REPLACE
FUNCTION xxpos.IS_USER_VALID(
  user_name     IN VARCHAR,
  user_password IN VARCHAR)
RETURN boolean
AS
user_count INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO user_count
    FROM xxpos.service_users
    WHERE user_id     = user_name
    AND user_password = user_password;
    IF user_count     >0 THEN
        return true;
    ELSE
       return false;
    END IF;
END;

I created the oracle function. And i tried to use it in C# my application Like:

        Oracle.DataAccess.Client.OracleParameter[] parameters = new Oracle.DataAccess.Client.OracleParameter[3];
        parameters[0] = new Oracle.DataAccess.Client.OracleParameter("user_name", this.UserName);
        parameters[1] = new Oracle.DataAccess.Client.OracleParameter("user_password", this.Password);
        parameters[2] = new Oracle.DataAccess.Client.OracleParameter("is_valid", 
            Oracle.DataAccess.Client.OracleDbType.Object, 
            System.Data.ParameterDirection.ReturnValue);
        using (MyOracleClient myOracleClient = new MyOracleClient())
        {
            myOracleClient.MyExicuteNonQuery(Command.IsUserValid, parameters);
            this.userValid = (bool)parameters[2].Value;
        }

But It doesn't work out. Cloud you Please enplane me, the right way of using Parameters with c# Database connection.

Getting a exception on the parameter "is_valid" Invalid parameter binding Parameter name: is_valid — Sujith Radhakrishnan
– Sujith Radhakrishnan, Commented Jun 10, 2013 at 12:55
Did you try setting the value of Size for the ReturnValue param — V4Vendetta
– V4Vendetta, Commented Jun 11, 2013 at 4:39

tbone · Accepted Answer · 2013-06-10 13:59:22Z

4

From the Oracle docs:

When binding by position (default) to a function, ODP.NET expects the return value to be bound first, before any other parameters.

Picky, I know.

answered Jun 10, 2013 at 13:59

tbone

15.5k3 gold badges37 silver badges42 bronze badges

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

Comments

justdan23 · Accepted Answer · 2017-10-13 16:05:05Z

0

Try this:

this.userValid = ( (Oracle.DataAccess.Types.OracleBoolean)  parameters[2].Value).IsTrue;

answered Oct 13, 2017 at 16:05

justdan23

6178 silver badges10 bronze badges

Collectives™ on Stack Overflow

Using Oracle Function with return value in c# application

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related