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

Question

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.

This is a simplified portion of the larger block; the full statement would test the return value and do inserts if no duplicate found as well as return the flag or ID to the C# page. — BlueKnot
– BlueKnot, Commented Aug 15, 2016 at 13:53
You say the direction for REPORTID is InputOutput (I presume you are referring to your C# code). How is REPORTID defined in your pl/sql code? If this is a procedure, is it defined as IN or IN OUT ? Not clear based on your one code snippet — tbone
– tbone, Commented Aug 15, 2016 at 14:39
@tbone Not a pre-defined procedure; the block is created as a SQL statement (in a C# string) and passed to the OracleCommand object along with the parametercollection. (the string is built dynamically as the number of entries in the related table [not shown in snippet] is variable). In the SQL Server version I had a DECLARE at the top of the block; but Oracle gave me a 'Not all paramters bound' error and I had to remove that and pass it in as part of the parameter collection instead. — BlueKnot
– BlueKnot, Commented Aug 15, 2016 at 14:49

tbone · Accepted Answer · 2016-08-15 16:22:13Z

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

Thanks! That got me there. Hadn't known that DBType was an option here (it doesn't work in the constructor, but I was able to re-write as below) psCmd.Parameters.Add(new OracleParameter() { ParameterName = "REPORTID", DbType = System.Data.DbType.Int32, Direction = ParameterDirection.InputOutput, Value = DBNull.Value });

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related