Simple things to remember when working with Oracle

by Heathesh 12. January 2010 22:55

In no way am I an Oracle expert. However in my experience there are a few things that I've figured out that I thought would be helpful to people. Here they are in random order:

1. For parameters, you use a colon ":" and not the at "@" symbol. For example, see the SQL statement versus the Oracle statement:

SQL Server: SELECT * FROM Table WHERE ID = @Id
Oracle: SELECT * FROM Table WHERE ID = :Id

2. If you're using the Oracle data provider, I find it's best to always add your parameters to your OracleCommand in the exact same order as in the query. For example:

    public void Update(string field1, string field2, int idField)
    {
        using (OracleConnection connection = new OracleConnection(ConfigurationReader.OracleConnectionString))
        {
            connection.Open();

            //note the order of the parameters in the query: field1, field2, idField
            string commandText = "UPDATE table SET field1 = :field1, field2 = : field2 WHERE idField = :idField";

            using (OracleCommand command = new OracleCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;

                //NB: add the parameters to the command in the same order as the query
                command.Parameters.Add(new OracleParameter("field1", OracleDbType.Varchar2, 255,
                    field1, System.Data.ParameterDirection.Input));
                command.Parameters.Add(new OracleParameter("field2", OracleDbType.Varchar2, 255,
                    field2, System.Data.ParameterDirection.Input));
                command.Parameters.Add(new OracleParameter("idField", OracleDbType.Int32, idField,
                    System.Data.ParameterDirection.Input));

                command.ExecuteNonQuery();
            }

            connection.Close();
        }
    }


3. One thing that can happen with Oracle, is if you have a parameter that is repeated, I don't know why but sometimes the query just won't work. I've found that duplicating the parameter is the best way to get around this:

    public void Select(int idField)
    {
        using (OracleConnection connection = new OracleConnection(ConfigurationReader.OracleConnectionString))
        {
            connection.Open();

            //note that I'm using two parameters, namely idField1 and idField2
            string commandText = "SELECT * FROM table1, table2 WHERE table1.Id = :idField1 AND table2.Id = :idField2";

            using (OracleCommand command = new OracleCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;

                //now I'm setting both parameter values to the value passed in
                command.Parameters.Add(new OracleParameter("idField1", OracleDbType.Int32, idField,
                    System.Data.ParameterDirection.Input));
                command.Parameters.Add(new OracleParameter("idField2", OracleDbType.Int32, idField,
                    System.Data.ParameterDirection.Input));

                using (OracleDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        //do what you want with the data
                    }
                }
            }

            connection.Close();
        }
    }


4. When using SQL Developer / Toad or any client application to connect to your Oracle database, always remember to commit your changes. After running an insert, update or delete, failing to commit the changes could result in database locks and you left wondering why your application just froze.

Happy coding!

Tags: , , , ,

.Net | Oracle

Comments are closed



Powered by BlogEngine.NET 1.5.0.7 (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  July 2017  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

http://heathesh.com