Format Models in Oracle

A format model in oracle describes the format of datetime or numeric data that to be inserted into database field. It is a character literal that does not change any internal representation of a value. It just represents how oracle will interprets the string(date or number) while inserting data into oracle database.

Format model just represents how to be inserted into oracle database.

With an example, I will make this understand.

Let's create a table named test_format.
SQL> create table test_format(col1 date);

Table created.

Now we try to insert date field into the table.
SQL> insert into test_format values('11-09-09');
insert into test_format values('11-09-09')
*
ERROR at line 1:
ORA-01843: not a valid month

And it fails because '11-09-09' is not recognized as a valid date in oracle. Oracle could not understand this format input. So we need to specify format and make it recognize to oracle about our input format model.

Now try following sql.
SQL> insert into test_format values (to_date('11-09-09','DD-MM-YY'));

1 row created.

And successfully row is inserted because here we recognize oracle about our input data using 'DD-MM-YY' format. This 'DD-MM-YY' is called the format model.

If we query the table, we see.
SQL> select * from test_format;

COL1
---------
11-SEP-09

Which is not same as the format in which we inserted data. So we can say format models are just recognise the input data format, but it does not affect any internal represntation of stored data into database.

Let's now see another example of how we can insert data into database.
SQL> insert into test_format values('12-SEP-09');

1 row created.

Here, we see we did not use any format model but oracle recognize the data and successfully inserted into oracle database. It is because if you don't specify any format model of date then oracle implicitly using the initialization parameter NLS_TERRITORY and use the format model specified using NLS_TERRITORY parameter. Most specifically you can see it from NLS_DATE_FORMAT parameter.

SQL> col property_value for a10
SQL> select property_name, property_value from database_properties where property_name='NLS_DATE_FORMAT';

PROPERTY_NAME                  PROPERTY_V
------------------------------ ----------
NLS_DATE_FORMAT                DD-MON-RR

So you see here by default it can recongnize date format data specified in format DD-MON-RR and so it can recognize '12-SEP-09' but not 'SEP-12-09'.

So we must need to use format model while inserting data into the database in order to recognize the format of our input whenever necessary.

Related Documents

http://arjudba.blogspot.com/2009/02/different-ways-to-set-up-nls-parameters.html

http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes-in.html

http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html

http://arjudba.blogspot.com/2008/07/list-of-single-row-functions-in-oracle.html