CODEWITHSUNDEEP
c#asp.netoracle-database
BlueKnot
BlueKnot

Reputation: 33

Assign a literal to a bind variable (C# output parameter) in an oracle block

I am working on a C# application and am converting the SQL statements from SQL Server to Oracle. I have one block where values are being inserted to a main table and several records in a related table. I need to determine if a duplicate record exists in the main table and return a flag value to the C# page if so, otherwise insert the records, get the PK for the main table and use it for the inserts to the related table. So far the best way I have found to return values from Oracle inside a block has been to use InputOutput parameters. (If there is a BEGIN/END block anywhere in the statement it will not let me SELECT without INTO.)

In SQL Server, attempting to load a variable from a SELECT statement that returns no records simply results in the variable being unchanged. In Oracle I have found that I need to set the NO_DATA_FOUND exception to handle this. I want the exception to set the return flag; but I receive "ORA-06502: PL/SQL: numeric or value error".

BEGIN 
    SELECT ID INTO :REPORTID FROM Report WHERE ExtractDt=:EXTRACTDT 
        AND REGION=:RGN; 
    EXCEPTION WHEN NO_DATA_FOUND THEN 
        SELECT -1 INTO :REPORTID FROM DUAL; 
END;

This is the closest I have come. The error occurs on the SELECT -1 ... line

REPORTID is passed in as a parameter of type NUMBER and direction InputOutput; EXTRACTDT is a date with a valid value and RGN is a varchar(2) string; both Input parameters.

EDIT: Sample of how the block is being sent to Oracle from C#:

OracleCommand psCmd = new OracleCommand();
psCmd.Connection = Conn; //Previously defined, valid & opened
psCmd.CommandType = CommandType.Text;
psCmd.CommandText = "{SQL STRING CONTAINING BLOCK, AS ABOVE}";
psCmd.Parameters.Add(new OracleParameter("REPORTID", OracleType.Number, 10) { Direction = ParameterDirection.InputOutput, Value = DBNull.Value });
psCmd.Parameters.Add(new OracleParameter("EXTRACTDT", OracleType.DateTime) { Value = dtExtract }); //C# DateTime with valid date
psCmd.Parameters.Add(new OracleParameter("RGN", OracleType.VarChar, 2) { Value = sRegion }); //C# String, 2 characters
psCmd.ExecuteNonQuery();
Int32 iID = (Int32)psCmd.Parameters["REPORTID"].Value;
//Cleanup, etc.

Upvotes: 3

Views: 2092

Answers (1)

tbone
tbone

Reputation: 15473

The problem is probably that you're using OracleDbType instead of DbType. I usually use OracleDbType, but in this case you're selecting a number into some variable (select -1 into ...) and its returning a Decimal type instead of an Int. The simple example below works for me:

string connStr = "User Id=myuser;Password=mypass;Data Source=myinstance;";
                using (OracleConnection con = new OracleConnection(connStr))
                {
                    string s = @"BEGIN
                    select 1 into :TESTID from dual where 1=0;
                    EXCEPTION
                    when no_data_found then
                    select -1 into :TESTID from dual; 
                    END;";

                    using (OracleCommand cmd = new OracleCommand(s, con))
                    {
                        con.Open();
                        Console.WriteLine("Running statement");

                        OracleParameter prm = new OracleParameter();
                        cmd.Parameters.Clear();
                        cmd.BindByName = true;

                        //prm = new OracleParameter("TESTID", OracleDbType.Int32, ParameterDirection.InputOutput); prm.Value = 0; cmd.Parameters.Add(prm);
                        prm = new OracleParameter();
                        prm.ParameterName = "TESTID";
                        prm.DbType = DbType.Int32; // don't use OracleDbType, will return decimal in some cases, not int
                        prm.Direction = ParameterDirection.InputOutput;
                        prm.Value = 0;
                        cmd.Parameters.Add(prm);

                        cmd.ExecuteNonQuery();

                        // may have cast/conversion issues here if using OracleDbType
                        int id = Convert.ToInt32(cmd.Parameters["TESTID"].Value);

                        Console.WriteLine("ID is: " + id);

                        con.Close();
                    }
                }

Upvotes: 1

Related Questions