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>