CODEWITHSUNDEEP
stored-proceduresplsqloracle11g
theDbGuy
theDbGuy

Reputation: 931

Where to change the NLS_DATE_FORMAT in oracle 11g?

i need to change the NLS_DATE_FORMAT in my Stored procedure.seems like its a session variable and where should i alter the variable mask.

should it be done in global declaration or elsewhere(i mean from frontend).

Upvotes: 3

Views: 22014

Answers (1)

Ben
Ben

Reputation: 52853

If you want to change a NLS parameter in a procedure then you can use DBMS_SESSION.SET_NLS. Given an environment that looks like this:

SQL> select value
  2    from nls_session_parameters
  3   where parameter = 'NLS_DATE_FORMAT';

VALUE
------------------------------------------

DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
---------
25-NOV-14

executing the procedure changes the format (note the triple single quotes):

SQL> begin
  2     dbms_session.set_nls('nls_date_format', '''yyyy-mm-dd''');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
----------
2014-11-25

Alternatively you can use dynamic SQL, EXECUTE IMMEDIATE, for instance:

SQL> begin
  2     execute immediate 'alter session set nls_date_format = ''yyyy-mm''';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
-------
2014-11

However, as Justin Cave notes this is highly unusual. If you're converting between datatypes you should always do this explicitly with an explicit format. In the case of dates the correct function to use is TO_DATE()

Upvotes: 4

Related Questions