Nls_timestamp_format

How to set nls patameters as default value?
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='HH24:MI:SS';

Session altered.

SQL> CREATE TABLE TEMP(TIME TIMESTAMP);

Table created.

SQL> INSERT INTO TEMP
2 VALUES('12:03:50');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEMP;

TIME
---------------------------------------------------------------------------
12:03:50

SQL> CONN SCOTT/TIGER
Connected.
When i again logged in i got defalut timestamp where as my requirement was time column should be set to timestamp='HH24:MI:SS'.

SQL> CONN SYS/SYS AS SYSDBA
Connected.
SQL> SELECT * FROM TEMP;

TIME
---------------------------------------------------------------------------
01-AUG-06 12.03.50.000000 PM


In oracle9 how to set nls_timestamp_format only for perticular table instead of whole session?

its not done at the table level, its done at the session level

You could create a view where the date is converted to a to_char(yourdatecolumn,'hh24:MI:SS')

Then just query the view... Or just write the query that way in the first place.