How to set nls_timestamp_format per procedure call

Question

I have a stored procedure that will insert into an xmltype column. The column refers to a schema with a column of datetime type. The date needs to be formatted like so : YYYY-MM-DD"T"HH24:MI:SS

Testing it via anonymous block, I can issue an ALTER SESSION to change the time format to the current session

alter session set nls_timestamp_format = 'YYYY-MM-DD"T"HH24:MI:SS';

insert into table1 (id, xmlcolumn) values (1, updatexml(
   '/root/order/id/text()', p_order_id,
   '/root/order/date/text()', to_timestamp(sysdate,'DD-MON-RRRRHH:MI:SS.FF')
));

Moving this to stored proc though, Oracle prevents me from using the ALTER SESSION, I tried:

DBMS_SESSION.SET_NLS('nls_timestamp_format','YYYY-MM-DD"T"HH24:MI:SS');

but SQL Developer says invalid option.

I'm very new to Oracle (being a SQL DB Dev),any pointers on how to set the time format?

Thanks in advance

Gaurav Soni Gaurav Soni · Accepted Answer · 2012-07-17T19:33:35

The DBMS_SESSION.SET_NLS procedure does not raise any exceptions. However, if the value parameter represents a format mask (which would be double-quoted in the ALTER SESSION command) it needs to be enclosed in triple quotes. Otherwise, the following Oracle error will be raised: ORA-00922: Missing or invalid option. Try this:

DBMS_SESSION.SET_NLS('nls_timestamp_format','''YYYY-MM-DD "T"HH24:MI:SS''');

How to set nls_timestamp_format per procedure call

1 Answers