Format Masking

nls_parameters:
Optional national language support parameters (nls_parameters) are useful for specifying the language and format in which the names of date and numeric elements are returned. These parameters are usually absent, and the default values for elements such as day or month names and abbreviations are used.

There is a publicly available view called NLS_SESSION_PARAMETERS that contains the NLS parameters for your current session.

 SELECT * FROM NLS_SESSION_PARAMETERS;

 SHOW PARAMETER NLS_CURR

 SELECT * FROM NLS_SESSION_PARAMETERS;

By default, NLS_CURRENCY would be '$'. We can change this to Session level.

ALTER SESSION set NLS_CURRENCY='Rs';

ALTER SESSION set NLS_CURRENCY='$'  ;

Numeric format Masks:
SELECT TO_CHAR(000201,'$000000.099')||' represents the price in Dollars' from DUAL;

SELECT TO_CHAR(000201,'L000000.099')||' represents the price in local Currency' from DUAL;

Date format Masks: 

SELECT  to_char(sysdate)||' is today''s date' from DUAL;

SELECT  TO_CHAR(sysdate,'Month')||'is a special time' from DUAL;

By prefixing the format model with the letters fm, Oracle is instructed to trim all spaces from the names of days and months. There are many formatting options for dates being converted into characters, some of which are listed in below diagram.

SELECT TO_CHAR(sysdate,'fmMonth')||'is a special time' from DUAL;

How to convert a oracle table into XML

SQL> SELECT DBMS_XMLGEN.GETXML('SELECT * FROM EMP') TEST_XML FROM DUAL;   

Split Functionality

SQL> CREATE OR REPLACE type split_tbl AS   TABLE OF VARCHAR2(32767);

SQL> CREATE OR REPLACE
  FUNCTION split(
      p_list VARCHAR2,
      p_del  VARCHAR2 := ',' )
    RETURN split_tbl pipelined
  IS
    l_idx pls_integer;
    l_list  VARCHAR2(32767) := p_list;
    l_value VARCHAR2(32767);
  BEGIN
    LOOP
      l_idx   := instr(l_list,p_del);
      IF l_idx > 0 THEN
        pipe row(SUBSTR(l_list,1,l_idx-1));
        l_list := SUBSTR(l_list,l_idx +LENGTH(p_del));
      ELSE
        pipe row(l_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  END split;

SQL>   SELECT split('Raju,Kishore,Swathi,Lars') FROM dual;

Above procees seems a bit clumsy. More over we can get this result with out creating a function.

SQL> SELECT REGEXP_SUBSTR ('Raju,Kishore,Swathi,Lars', '[^,]+', 1, level) val
        FROM DUAL
        CONNECT BY 
        level <= LENGTH(REGEXP_REPLACE('Raju,Kishore,Swathi,Lars','[^,]*'))+1;

New 11g Result Cache

select /*+ result_cache */ SUM(sal)
from   scott.emp
where  deptno=20;


When a query with RESULT_CACHE hint is run, Oracle will see if the results of the query have already been executed, computed, and cached, and, if so, retrieve the data from the cache instead of querying the data blocks and computing the results again. Take the following important points into consideration before using this feature:

The Result Cache feature is useful only for SQL queries that are executed over and over again frequently.

The underlying data doesn’t change very often. When the data changes, the result set is removed from the cache.

If you are executing the same queries over and over, using the RESULT_CACHE hint often makes subsequent queries run faster


Note that the Result Cache does not work when you are logged in as sysdba.

Query to get Invalid Objects in Oracle Schema

SQL>  SELECT SUBSTR(object_name,1,20) "Object",
           OBJECT_TYPE "OType",
           TO_CHAR(LAST_DDL_TIME, 'DD-MON-YYYY HH24:MI:SS') "Change Date",
            status
           FROM USER_OBJECTS
           WHERE STATUS <> 'VALID';