dbms_session. package

SQL> EXEC dbms_session.set_nls('nls_date_format','''DD.MM.YYYY''');

PL/SQL procedure successfully completed.

SQL> SELECT Sysdate from dual;

SYSDATE
----------
30.03.2009

SQL> EXEC dbms_session.set_nls('nls_date_format','''DD/MM/YYYY HH24:MI:SS''');

PL/SQL procedure successfully completed.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
30/03/2009 11:40:35

SQL> desc dbms_session;
PROCEDURE CLEAR_ALL_CONTEXT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMESPACE VARCHAR2 IN
PROCEDURE CLEAR_CONTEXT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMESPACE VARCHAR2 IN
CLIENT_ID VARCHAR2 IN DEFAULT
ATTRIBUTE VARCHAR2 IN DEFAULT
PROCEDURE CLEAR_IDENTIFIER
PROCEDURE CLOSE_DATABASE_LINK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DBLINK VARCHAR2 IN
PROCEDURE FREE_UNUSED_USER_MEMORY
FUNCTION IS_ROLE_ENABLED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROLENAME VARCHAR2 IN
FUNCTION IS_SESSION_ALIVE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNIQUEID VARCHAR2 IN
PROCEDURE LIST_CONTEXT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LIST TABLE OF RECORD OUT
LSIZE NUMBER OUT
PROCEDURE MODIFY_PACKAGE_STATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ACTION_FLAGS BINARY_INTEGER IN
PROCEDURE RESET_PACKAGE
PROCEDURE SESSION_TRACE_DISABLE
PROCEDURE SESSION_TRACE_ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
PROCEDURE SET_CLOSE_CACHED_OPEN_CURSORS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CLOSE_CURSORS BOOLEAN IN
PROCEDURE SET_CONTEXT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMESPACE VARCHAR2 IN
ATTRIBUTE VARCHAR2 IN
VALUE VARCHAR2 IN
USERNAME VARCHAR2 IN DEFAULT
CLIENT_ID VARCHAR2 IN DEFAULT
PROCEDURE SET_IDENTIFIER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CLIENT_ID VARCHAR2 IN
PROCEDURE SET_NLS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARAM VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE SET_ROLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROLE_CMD VARCHAR2 IN
PROCEDURE SET_SQL_TRACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_TRACE BOOLEAN IN
PROCEDURE SWITCH_CURRENT_CONSUMER_GROUP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NEW_CONSUMER_GROUP VARCHAR2 IN
OLD_CONSUMER_GROUP VARCHAR2 OUT
INITIAL_GROUP_ON_ERROR BOOLEAN IN
FUNCTION UNIQUE_SESSION_ID RETURNS VARCHAR2

SQL>

SQL> desc scott.emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select to_char(HIREDATE,'dd.mon.yyyy','nls_date_language=french') from scott.emp;

TO_CHAR(HIREDATE,'DD.MON.YYYY','NLS_
------------------------------------
17.d¨¦c..1980
20.f¨¦vr..1981
22.f¨¦vr..1981
02.avr. .1981
28.sept..1981
01.mai .1981
09.juin .1981
19.avr. .1987
17.nov. .1981
08.sept..1981
23.mai .1987

TO_CHAR(HIREDATE,'DD.MON.YYYY','NLS_
------------------------------------
03.d¨¦c..1981
03.d¨¦c..1981
23.janv..1982

14 rows selected.

SQL> select to_char(HIREDATE,'dd.mon.yyyy','nls_date_language=french') from scott.emp where

HIREDATE>'01.JAN.1982';

TO_CHAR(HIREDATE,'DD.MON.YYYY','NLS_
------------------------------------
19.avr. .1987
23.mai .1987
23.janv..1982

SQL>

SQL> select to_char(sal,'99G999D99','NLS_NUMERIC_CHARACTERS='',.''') FROM SCOTT.EMP;

TO_CHAR(SAL,'99G999D
--------------------
800,00
1.600,00
1.250,00
2.975,00
1.250,00
2.850,00
2.450,00
3.000,00
5.000,00
1.500,00
1.100,00

TO_CHAR(SAL,'99G999D
--------------------
950,00
3.000,00
1.300,00

14 rows selected.

SQL>