dbms_session.set_nls works for SQLPlus, not SQL Developer?

This is in SQLPlus,

SQL> alter session set nls_date_format=’YYYY-MM-DD HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

2006-10-12 12:31:20

SQL> begin
  2   dbms_session.set_nls(‘nls_date_format’,”’DD-MON-YY”’);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;


dbms_session.set_nls works perfectly, however, in SQLDeveloper, when I run the same thing:
alter session set succeeded.

2006-10-12 12:30:42      

1 rows selected

anonymous block completed

2006-10-12 12:30:42       <– Notice how the format is not changed.

1 rows selected


2 Responses to “dbms_session.set_nls works for SQLPlus, not SQL Developer?”

  1. Werner de Jong Says:


    In SQl developer you are required to use the execute statement when fireing this procedure.

    For example:

    exec dbms_session.set_nls(‘NLS_LANGUAGE’,”’DUTCH”’);

  2. Dong Says:


    exec is just a short hand for begin..end block, not a requirement. The same is true in any Oracle client.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: