Oracle's SQL Developer Tool: Setting either session or default date formatting

by Heathesh 11. June 2010 00:36

When working with date fields in Oracle's SQL Developer, it's important to note that tools like SQL developer have different ways to set how the date is displayed in result sets. For example I believe the standard date display format in SQL developer is DD/MON/RR which would look something like 11/JUN/10.

There are two ways to change this date format. The one is to alter the current SQL Developer session and set the date format for your current session alone. This is done by running the following command in any SQL Developer Worksheet:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:mi:ss';


That will change the date format for the current session, i.e. the connection to the current database you are running queries against. This means any worksheets you open that connect to the database you ran the query against will have the specified data format for the duration of your connection to that database, and any worksheets that connect to any other database will retain the default date format as they exist in separate sessions.

You can also set the default date format for SQL developer, this can be done inside SQL Developer:

1. Select "Tools" -> "Preferences"
2. Expand the "Database" node and click on "NLS"
3. You will see a text box with the label "Date Format", simply enter your desired default date format there

Oracle's date formatting options include:

MM Numeric month (e.g., 06)
MON Abbreviated month name (e.g., JUN)
MONTH Full month name (e.g., JUNE)
DD Day of month (e.g., 11)
DY Abbreviated name of day (e.g., FRI)
YYYY 4-digit year (e.g., 2010)
YY Last 2 digits of the year (e.g., 10)
RR Like YY, but the two digits are 'rounded' to a year in the range 1950 to 2049. Thus, 10 is considered 2010 instead of 1910
AM (or PM) Meridian indicator
HH Hour of day (1-12)
HH24 Hour of day (0-23)
MI Minute (0-59)
SS Second (0-59)

Happy dating!

Tags: , , , ,

Oracle | SQL Developer

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

Connecting to an Oracle database using .Net and Oracle's 11G Client

by Heathesh 22. December 2009 20:05

Firstly, if you don't have the Oracle 11G client you will need to download it here (WARNING: The download is about 487MB):
http://download.oracle.com/otn/nt/oracle11g/win32_11gR1_client.zip

You may have to register on Oracle's site to be able to download it, a bit of a bummer but it is necessary.

Once you've downloaded it, I would advise installing the "Administrator" version of the client, as this will give you all the tools etc. you required to work with Oracle.

After it's been installed, you will need to setup your TNSNAMES.ORA file to include any and all databases you will be connecting to. You can find your TNSNAMES.ORA file in the following folder:

    [Your chosen or set installation folder]\product\11.1.0\client_1\network\admin

If you don't see a TNSNAMES.ORA file there, don't worry, simply create a new text document and call it "TNSNAMES.ORA". If you don't have a TNSNAMES.ORA file or don't want to use it, you will still be able to connect to the database but your connection string will need to look something like this (I personally prefer using the TNSNAMES file):

    Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

Now, although I have worked with Oracle extensively I have never written a TNSNAMES.ORA file or entry from scratch, I normally ask a DBA or someone who can already connect to the relevant database to either send me the TNSNAMES.ORA entry for the relevant database, or to send me their TNSNAMES.ORA file. A normal entry in the TNSNAMES.ORA looks like so:

    # USE HASH FOR COMMENTS
    ORACLE_SID=
      (DESCRIPTION =
        (FAILOVER = ON)
        (LOAD_BALANCE = ON)
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1522))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1522))
        (CONNECT_DATA =
          (SERVICE_NAME = ORACLE_SERVICE_NAME)
          (SERVER = DEDICATED)
          (FAILOVER_MODE =
            (TYPE = session)
            (METHOD = basic)
            (DELAY = 5)
            (RETRIES = 100)
          )
        )
      )


Of course all the info in the example is garbage, but you should get the basic idea. Once you've got your TNSNAMES.ORA entry setup, you will be able to connect to the database by doing the following:

1. In your Visual Studio project, add a reference (found under the .Net tab) to "Oracle.DataAccess". This should have been installed when you installed the "Administrator" version of the Oracle client.
2. Next add the following using to your code:

    using Oracle.DataAccess.Client;

3. You should then be able to connect to your database using the following code (depending on whether or not you created your TNSNAMES.ORA file, your connection string might be the longer one indicated above):

    using (OracleConnection connection = new OracleConnection("Data Source=ORACLE_SID;User Id=user;Password=password;"))
    {
        connection.Open();

        using (OracleCommand command = new OracleCommand())
        {
            //ADD CODE TO RUN RELEVANT COMMAND
        }

        connection.Close();
    }


That should be it, happy coding!

Tags: , , ,

Development | .Net | Oracle



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

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

http://heathesh.com