CODEWITHSUNDEEP
c#sqloracle-databaseselect
James
James

Reputation: 2911

I need to select into a parameter through the Oracle .NET connector

I need to do this select statement in Oracle:

(example in TSQL for MS SQL):

select @maxvalue = isnull(max(MYFIELD),0) from mytable

Oracle I think is something like this:

select nvl(max(MYFIELD),0) into maxvalue from mytable

BUT, can I just call the oracle statement having loaded up the parameter in the .NET connector maxvalue or do I need some BEGIN END voodoo? (I am using the Oracle .NET connetor rather than the MS one.)

Upvotes: 0

Views: 1501

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

You can do both (with or without "voodoo"):

        // without voodoo

        OracleCommand ncmd = new OracleCommand("select nvl(max(MYFIELD),0) from mytable", conn);

        ncmd.CommandType = CommandType.Text;

        object r = ncmd.ExecuteScalar();

        Console.WriteLine("a: " + r);
        Console.WriteLine("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");

        // with the voodoo

        ncmd.CommandText = "begin select nvl(max(MYFIELD),0) into :maxvalue from mytable; end;";
        ncmd.CommandType = CommandType.Text;

        OracleParameter res = new OracleParameter(":res", OracleDbType.Double);
        res.Direction = ParameterDirection.ReturnValue;
        ncmd.Parameters.Add(res);

        ncmd.ExecuteNonQuery();

        Console.WriteLine("b: " + res.Value);
        Console.WriteLine("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");

Upvotes: 2

Related Questions