.Net/C# - Oracle SQL Not All Variables Bound - Managed Data Access

Question

I am relatively unfamiliar with Oracles flavor of SQL. I cannot figure out why this is returning the following exception:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01008: not all variables bound

                    OracleCommand cmd1 = con.CreateCommand();
                    cmd1.CommandType = CommandType.Text;
                    cmd1.CommandText = @"MERGE INTO SECURITY_PRICES$V tgt USING (select * from DUAL) src ON (src.DTE = tgt.DTE AND src.SECURITY = tgt.SECURITY AND src.SOURCE = tgt.SOURCE) WHEN MATCHED THEN UPDATE SET tgt.TYPE = 'I', tgt.SOURCE = src.Source, tgt.CURRENCY = 'JMD' ,tgt.SECURITY = src.Security, tgt.LAST_PRICE = :Close, tgt.HIGH_PRICE = :High, tgt.LOW_PRICE = :Low, tgt.DTE = src.DTE, tgt.Volume = :Vol WHEN NOT MATCHED THEN INSERT (TYPE, SOURCE, CURRENCY, SECURITY, LAST_PRICE, HIGH_PRICE, LOW_PRICE, DTE, Volume) VALUES ('I', 'E', 'JMD', :insID,:Close,:High, :Low, :Time, :Vol)";


                    cmd1.Parameters.Add("insID", secid);
                    cmd1.Parameters.Add("Close", data.Close);
                    cmd1.Parameters.Add("High", data.High);
                    cmd1.Parameters.Add("Low", data.Low);
                    cmd1.Parameters.Add("Time", data.Time);
                    cmd1.Parameters.Add("Vol", data.Volume);

                    int suc = cmd1.ExecuteNonQuery();

I have tried the following. Adding the colon (':') to parameters.add. Using less variables, ensured that it was not passing in null parameters.

Here is the data. It gets parsed into an object, each property is a string. Does oracle require strongly typed parameters? Passing in a string in MS SQL Server, would not cause an issue - that is my only guess though.

insID: 228IL
Low: 0
High 0
Time: 2018-01-29T13:05:26
Vol: 0
Close:0

Meenangel · Accepted Answer · 2018-02-07 10:10:42Z

4

Try adding this line to the opening code:

OracleCommand cmd1 = con.CreateCommand();
cmd1.BindByName = true;
cmd1.CommandType = CommandType.Text;

If I recall, if BindByName is not set, then the OracleCommand class will bind parameters in the query in the order they are submitted, not by their name. Hence, as you have multiple uses of some of the bind placeholders in the SQL string they need to be bound again - leading to the "not all variables bound" exception.

answered Feb 7, 2018 at 10:10

Meenangel

663 bronze badges

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

Collectives™ on Stack Overflow

.Net/C# - Oracle SQL Not All Variables Bound - Managed Data Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related