Collection of Useful functions in Oracle 11g Part 6

1.Generate XML of Output of Query

http://nimishgarg.blogspot.com/2010/11/generate-xml-of-output-of-query.html

This is script to help to generate query output as XML format.

Query

select dbms_xmlgen.getxml('select empno, ename, dname from scott.emp e, scott.dept d where d.deptno=e.deptno') from dual;

Output 

2.Compare Two Tables Structures

http://nimishgarg.blogspot.com/2010/11/compare-two-tables-structures.html

In this script help to compare two tables structure in oracle database.

Script

SELECT
    A.COLUMN_NAME, 
    A.DATA_TYPE, 
    A.DATA_LENGTH,
    B.COLUMN_NAME, 
    B.DATA_TYPE, 
    B.DATA_LENGTH
FROM 
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE1') A, 
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE2') B
WHERE
    A.COLUMN_NAME = B.COLUMN_NAME (+)
UNION
SELECT
    A.COLUMN_NAME, 
    A.DATA_TYPE, 
    A.DATA_LENGTH,
    B.COLUMN_NAME, 
    B.DATA_TYPE, 
    B.DATA_LENGTH
FROM 
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE1') A, 
    (SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME  = 'TABLE2') B
WHERE
    A.COLUMN_NAME (+) = B.COLUMN_NAME.

Output

3.Date Functions

http://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html

3.1 How to get today day in oracle ?

select to_char(sysdate,'DAY') AS "TODAYS DAY" from dual

Output

TODAYS DAY
------------------------------------
MONDAY

3.2 How to get day of week in oracle?

select to_char(sysdate,'D') AS "DAY OF WEEK" from dual

Output

D
-
2

3.3 How to get week no of current year?
 

select to_char(sysdate,'IW') AS "WEEK OF THE YEAR" from dual

Output

WE
--
23

3.4 How to get current month name (Short name )in Oracle?

select to_char(sysdate,'MON') AS "MONTH" from dual

Output

MONTH
------------
JUN

3.5 How to get full name of the Month in Oracle?

select to_char(sysdate,'MONTH') AS "MONTH" from dual

Output

MONTH
------------------------------------
JUNE

3.6 How to get current month Number in oracle?

select to_char(sysdate,'MM') AS "MONTH" from dual

Output

MO
--
06

I hope these functions are will help to lot.i will publish more in upcoming articles.thank you reading.