SQL CONSTRAINTS

CONSTRAINTS:

---------------------

Constraints is rule or restriction which is imposed on table data.

we can create constraints on new table using CREATE command.

For existing table in database we can create constraints through ALTER command.

constraints are used to enforce business rules on data.

CATAGORIES OF THE CONSTRAINTS:

---------------------------------------------------

Domain integrity constraints:

------------------------------

1.NOT NULL

2.CHECK

Entity integrity constraints:

-----------------------------

1.UNIQUE

2.PRIMARY KEY

Referential integrity constraints:

----------------------------------

1.FORIEGN KEY

Different ways to add constraints:

----------------------------------------

1.COLUMN LEVEL  --along with the column definition

2.TABLE  LEVEL  --after the table different

3.ALTER  LEVEL  --using ALTER command

Constraint names:

--------------------

1.constraint name is unique to the owner.

2.if a constraint name is not specified ,the oracle server assign a unique name with the format

   SYS_C00N

  

3.A suggested format for naming a constraint is

 

  TABLENAME_CONSTRAINTTYPE_COLNAME

 

  EMP_NN_SAL

USER CONSTRAINTS: information about the columns in constraints owned by the user.

-----------------

SELECT  CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION

FROM USER_CONSTRAINTS

WHERE TABLE_NAME=<table_name>;

ALL CONSTRAINTS:  constraints definitions on accessible table.

--------------------------

SELECT  CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION

FROM ALL_CONSTRAINTS

WHERE TABLE_NAME=<table_name>;

DBA CONSTRAINTS: defintions on all tables.

---------------------------

SELECT  CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION

FROM DBA_CONSTRAINTS

WHERE TABLE_NAME=<table_name>;

NOT NULL CONSTRAINT:

----------------------------------

‘NOT NULL’ constraints are used to avoid null values for a specific column.

We can add ‘NOT NULL’ constraint at column level only.

Columns without the ‘NOT NULL’ constraint allow NULL values. (i.e. if no value is specified then NULL is the default) .

If Constraint name is not specified then system will generate a unique name for the constraint.

CONSTRAINT WITHOUT NAME:

--------------------------------------------

 CREATE TABLE emp (empno  NUMBER NOT NULL,

 ename   VARCHAR2 (100),

 sal     NUMBER (5));

If we want to give a name to the constraint, we have to use the constraint clause.

CONSTRAINT WITH NAME:

---------------------

SQL> Create TABLE emp (empno  NUMBER CONSTRAINT emp_nn NOT NULL,

    ename   VARCHAR2 (100),

    sal     NUMBER (5));

           

By using below query we can know the name of the constraint attached to a particular a table.

SQL> SELECT constraint_name,

    constraint_type,

    search_condition

    FROM user_constraints

    WHERE table_name=’EMP’;[/sql]

TEST CONSTRAINT:

---------------------------

Suppose if we try to insert null values into table column which has NOTNULL constraint then system will raise an error.

SQL> INSERT INTO emp (empno, ename, sal)

     VALUES (null,’MARK’, 2000);

Output:

ERROR at line 1:

ORA-01400: cannot INSERT NULL into ("EMP"."EMPNO")

ALTER LEVEL FOR NOT NULL CONSTRAINTS:

-------------------------------------

ALTER TABLE table_name

            MODIFY column_name datatype[(size)] NOT NULL;

 

CHECK CONSTRAINT:

-------------------------------

we can defined CHECK CONSTRAINT in three levels

column level

table  level

alter  level

column level:  without Constraint Name

---------------------------------------

create table sample

(sno  number(4),

 sname  varchar2(30),

 salary  number(5) CHECK (salary<5000)

);

With Constraint Name:

---------------------

create table sample

(sno number(4),

 sname   varchar2(30),

 salary number(5) CONSTRAINT SAMPLE_C_SALARY CHECK (salary<4000)

);

Table level: without Constraint Name

------------------------------------

create table sample

(sno number(5),

 sname varchar2(30),

 salary number(5),

 CHECK (salary<5000)

 );

With Constraint Name:

---------------------

create table sample

(sno number(5),

 sname varchar2(30),

 salary number(5),

 CONSTRAINT sample_ck_salary CHECK (salary<5000)

 );

 alter level:

 ------------

 alter table sample ADD CONSTRAINT CHECK sample_ck_salary CHECK (salary<5000);

 alter table sammple DROP CONSTRAINT CHECK sample_ck_salary CHECK (salary<5000);

 UNIQUE CONSTRAINT: Unique constraint a void duplicate data .but it allows null values.

 -------------------------------

 we can defined UNIQUE CONSTRAINT in three levels

 column level

 table  level

 alter level

column level :without constraint name

-------------------------------------

create table sample

( sno    number(5) UNIQUE,

  sname  varchar2(30),

  dob    date

 );

with constraint name:

---------------------

create table sample

(sno     number(5) CONSTRAINT sample_u_sno UNIQUE,

 sname   varchar2(30),

 dob     date

 );

 table level: without constraint name

 -------------------------------------

 create table sample

 ( sno    number(5),

   sname  varchar2(30),

   dob    date,

   UNIQUE(sno)

  );

 

  with constraint name:

  -------------------------

  create table sample

  ( sno   number(5),

    sname varchar2(30),

            dob   date,

            CONSTRAINT sample_u_sno UNIQUE(sno)

   );

  

 Alter level:

 ------------

 alter table sample ADD CONSTRAINT sample_u_sno UNIQUE(sno);

 alter table sample DROP CONSTRAINT sample_u_sno UNIQUE(sno);

 PRIMARY KEY CONSTRAINT: primary key is avoid duplicates and null values.

----------------- -----------------------

 column level

 table  level

 alter  level

 column level: without constraint name

 --------------

 create table sample

 ( sno   number(5) PRIMARY KEY,

   sname varchar2(30),

   dob   date

  );

 with constraint name:

 ---------------------

 create table sample

 ( sno   number(5) CONSTRAINT sample_pk_sno PRIMARY KEY,

   sname varchar2(30),

   dob   date

 );

 table level: without constraint name

 -----------------------------------

 create table sample

 ( sno   number(5),

   sname varchar2(30),

   dob   date,

   PRIMARY KEY (sno)

  );

 

  with constraint name:

  ---------------------

  create table sample

  ( sno  number(5),

    sname varchar2(30),

            dob   date,

            CONSTRAINT sample_pk_sno PRIMARY KEY(sno)

   );

  

COMPOSITE KEY:

 ------------------------

 composite key defined in table level only.defined primary key on more than one column. we can take a maximum

 32 columns in composite primary key.

 create table sample

 (sno   number(5),

  sname varchar2(30),

  dob   date,

  PRIMARY KEY (sno,sname)

  );

 alter level:

 ------------

 constraint without name:

 ------------------------

 alter table sample ADD PRIMARY KEY (sno);

 constraint with name:

 ---------------------

 alter table sample ADD CONSTRAINT sample_pk_sno PRIMARY KEY (sno);

 FORIEGN KEY CONSTRAINT:

---------------- -----------------------

 foreign keys are used to refer the parent table primary key column which does not allow duplicates.

 for a parent child relationship across table foreign key is always attached to the child table.

 column level

 table  level

 alter  level

 **to demonstrate the foreign key constraint we need parent child relationship between tables.thats why first

  we create parent table/master table as a dept make deptno column as primary key**

 

  create table dept

  ( deptno  number(3) PRIMARY KEY,

    dname   varchar2(30),                ------this table is parent table/master table----

            loc     varchar2(30)

   );

  

 column level : without constraint name:

 ---------------------------------------

 create table emp

 (empno      number(3),

  ename      varchar2(30),

  sal        number(5),

  deptno     number(4) REFERENCES dept(deptno)

  );

 with constraint name:

 ---------------------

 create table emp

 (empno      number(3),

  ename      varchar2(30),

  sal        number(5),

  deptno     number(4) CONSTRAINT emp_fk_deptno  REFERENCES dept(deptno)

  );

 table level: without constraint name

--------------------------------------

create table emp

 (empno      number(3),

  ename      varchar2(30),

  sal        number(5),

  deptno     number(4),

  FORIEGN KEY(deptno) REFERENCES dept(deptno)

  );

 

  with constraint name:

  ---------------------

  create table emp

 (empno      number(3),

  ename      varchar2(30),

  sal        number(5),

  deptno     number(4),

  CONSTRAINT emp_fk_deptno FORIEGN KEY(deptno) REFERENCES dept(deptno)

  );

 Alter level: without constraint name

 -------------------------------------

 alter table emp ADD FORIEGN KEY(deptno) REFERENCES dept(deptno);

 with constraint name:

 ---------------------

alter table emp ADD CONSTRAINT emp_fk_deptno FORIEGN KEY(deptno) REFERENCES dept(deptno);

ON DELETE CASCADE:

===================

on delete cascade is extension to Foreign key constraint.

1.if a parent child relationship existing tables than we will not able to delete the parent record

when child  exist for it.

2.By using ‘ON DELETE CASCADE’ clause we can remove the parent record even it Childs exists.

Because with ‘ON DELETE CASCADE’ whenever you remove parent record system automatically

removes all its dependent records from child table.

column level

table  level

alter  level

parent table:

-------------

create table dept

( deptno   number(5) PRIMARY KEY,

  dname    varchar2(30),

  loc      varchar2(30)

);

column level: without constraint name:

-------------------------------------

child table:

-------------

create table emp

(empno      number(4),

 ename      varchar2(30),

 sal        number(3),

 deptno     number(3) REFERENCES dept(deptno) ON DELETE CASCADE

 );

 with constraint name:

 ---------------------

 create table emp

 ( empno       number(4),

   ename       varchar2(30),

   sal         number(3),

   deptno      number(4) CONSTRAINT emp_fk_deptno REFERENCES dept(deptno) ON DELETE CASCADE

  );

 

table level:

------------

without constraint name:

------------------------

 create table emp

 ( empno       number(4),

   ename       varchar2(30),

   sal         number(3),

   deptno      number(4),

   FORIEGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE

  );

 

 

 with constraint name:

 ---------------------

 create table emp

 ( empno       number(4),

   ename       varchar2(30),

   sal         number(3),

   deptno      number(4),

   CONSTRAINT emp_fk_deptno FORIEGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE

  );

 

  alter level:

  ------------

  without constraint name:

  ------------------------

  alter table emp ADD FORIEGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

 

  with constraint name:

  ---------------------

 

  alter table emp ADD CONSTRAINT emp_fk_deptno FORIEGN KEY (deptno)

  REFERENCES dept(deptno) ON DELETE CASCADE;

 

SQL DATE FUNCTIONS

DATE FUNCTIONS:

----------------------------

ADD_MONTHS: this will add specified months to the given date.

---------------------

ADD_MONTHS(date,no.of months)

SELECT ADD_MONTHS(SYSDATE,5),ADD_MONTHS(SYSDATE,-5),ADD_MONTHS(SYSDATE,0) FROM DUAL;

SELECT ADD_MOTHS('04/JUN/1989',3) FROM DUAL;

SELECT ADD_MONTHS(TO_DATE('04/23/2013','DD/MM/YYYY')+5) FROM DUAL;

SELECT ADD_MONTHS(SYSDATE,NULL) FROM DUAL; --if no.of months null it will display nothing.

MONTHS_BETWEEN: this will give difference months between two DATES.

----------------------------

MONTHS_BETWEEN(date1,date2):

SELECT MONTHS_BETWEEN(SYSDATE,'12-JAN-2014') FROM DUAL;

SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;

SELECT MONTHS_BETWEEN(ADD_MONTHS(SYSDATE+5),SYSDATE) FROM DUAL;

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('12-JAN-2014','DD-MON-YYYY')) FROM DUAL;

NEXT_DAY: It produce next day of the given day from specified date.

-----------------

SELECT NEXT_DAY(SYSDATE,'WED') FROM DUAL;

SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;

LAST_DAY: It produce last day of the given day from specified date.

----------------

SELECT LAST_DAY(SYSDATE) FROM DUAL;

SELECT LAST_DAY('06-FEB-2016') FROM DUAL;

EXTRACT: This is used to extract the portion of the date value.

---------------

Syntax: – EXTRACT ((year | month | day | hour | minute | second) FROM date);

--------

 SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

 SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;

 SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;

 select extract (hour from cast(sysdate as timestamp)) from dual

 select extract (SECOND from cast(sysdate as timestamp)) from dual;

  SELECT EXTRACT( HOUR FROM interval '0 2:34' day to minute) FROM DUAL;

  SELECT EXTRACT( SECOND FROM interval '0 2:34:50' day to SECOND) FROM DUAL;

 

  SELECT EXTRACT(HOUR FROM NUMTODSINTERVAL(sysdate - trunc(sysdate), 'DAY')) FROM DUAL;

  SELECT EXTRACT(MINUTE FROM NUMTODSINTERVAL(sysdate - trunc(sysdate), 'DAY')) FROM DUAL;

  SELECT EXTRACT(SECOND FROM NUMTODSINTERVAL(sysdate - trunc(sysdate), 'DAY')) FROM DUAL;

 NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal.

  The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.

  The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype.

  The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

    'DAY'

    'HOUR'

    'MINUTE'

    'SECOND'

           

            SQL> SELECT NUMTODSINTERVAL(1,'DAY') FROM DUAL;

NUMTODSINTERVAL(1,'DAY')

---------------------------------------------------------------------

+000000001 00:00:00.000000000

SQL> SELECT NUMTODSINTERVAL(1,'HOUR') FROM DUAL;

NUMTODSINTERVAL(1,'HOUR')

---------------------------------------------------------------------

+000000000 01:00:00.000000000

SQL> SELECT NUMTODSINTERVAL(1,'MINUTE') FROM DUAL;

NUMTODSINTERVAL(1,'MINUTE')

---------------------------------------------------------------------

+000000000 00:01:00.000000000

SQL> SELECT NUMTODSINTERVAL(1,'SECOND') FROM DUAL;

NUMTODSINTERVAL(1,'SECOND')

---------------------------------------------------------------------

+000000000 00:00:01.000000000

SQL> SELECT NUMTODSINTERVAL(1,'MONTH') FROM DUAL;

SELECT NUMTODSINTERVAL(1,'MONTH') FROM DUAL;

                         *

ERROR at line 1:

ORA-01760: illegal argument for function

select to_char(sysdate, 'hh24:mi:ss') "Current Time",

  2     extract(hour from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Hour",

  3     extract(minute from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Minute",

  4     extract(second from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Second"

  5     from dual;

Current        Hour     Minute     Second

-------- ---------- ---------- ----------

12:40:02         12         40          2

=====================================================================================================================

GREATEST:

----------

SELECT  GREATEST(DATE1,DATE2);

LEAST:

------

SELECT LEAST(DATE1,DATE2);

ROUND: ROUND will ROUNDs the date to which it was equal to or greater than the given date.

------

Syntax: – ROUND (date, (day | month | year))

•           If the second parameter was year then ROUND will checks the month of the given date in

The following ranges.

JAN    —    JUN

JUL     —    DEC

•           If the month falls between JAN and JUN then it returns the first day of the current year.

•           If the month falls between JUL and DEC then it returns the first day of the next year.

EXAMPLE:

--------

SELECT ROUND(TO_DATE('12-FEB-2016','DD-MON-YYYY'),'YEAR') FROM DUAL;

SELECT ROUND(TO_DATE('12-AUG-2016','DD-MON-YYYY'),'YEAR') FROM DUAL;

MONTH:

-----

•           If the day falls between 1 and 15 then it returns the first day of the current month.

•           If the day falls between 16 and 31 then it returns the first day of the next month.

EXAMPLE:

--------

SELECT ROUND(TO_DATE('12-AUG-2016','DD-MON-YYYY'),'MONTH') FROM DUAL;

SELECT ROUND(TO_DATE('22-AUG-2016','DD-MON-YYYY'),'MONTH') FROM DUAL;

DAY:

----

•           If the second parameter was day then ROUND will checks the week day of the given date

•           In the following ranges.

SUN    —    WED

THU     —    SUN

•           If the week day falls between SUN and WED then it returns the previous Sunday.

•           If the weekday falls between THU and SUN then it returns the next Sunday.

•           If the second parameter was null then it returns nothing.

•           If we are not specifying the second parameter then ROUND will resets the time to the beginning of the current day in case of user specified date.

•           If we are not specifying the second parameter then ROUND will resets the time to the beginning of the next day in case of SYSDATE.

TRUNC:

------

•TRUNC will chops off the date to which it was equal to or less than the given date.

Syntax: – TRUNC (date, (day | month | year))

•           If the second parameter was year then it always returns the first day of the current year.

•           If the second parameter was month then it always returns the first day of the current month.

•           If the second parameter was day then it always returns the previous Sunday.

•           If the second parameter was null then it returns nothing.

•           If  you are not specifying the second parameter then trunk will resets the time to the beginning of the current day.

CONVERSION FUNCTIONS:

----------------------------------------

Explicite data type conversions:

-------------------------------------

sql provides 3 functions to convert a value from one datatype to another datatype.

TO_CHAR()

TO_NUMBER()

TO_DATE()

TO_CHAR:

--------------

THE TO_CHAR function will convert number or date to character string.

this function can be used in to different flavours

TO_CHAR(NUMBER CONVERSION TO CHARACTER CONVERSION)

TO_CHAR(DATE   CONVERSION TO CHARACTER CONVERSION)

SYNTAX FOR NUMBER TO CHARACTER CONVERSION:

------------------------------------------

TO_CHAR(NUMBER,fmt,nlsparms)

fmt:-it is the optional number formate

nlsparms:-character return by the number formate element.

1.DECIMAL INDICATOR:(D)

---------------------

SELECT 20345,TO_CHAR(20345,99999D99) FROM DUAL;

2.SCIENTIFIC NOTATION INDICATOR:(EEEE)

---------------------------------

SELECT TO_CHAR(1234,'9999EEEE') FROM DUAL;

SELECT TO_CHAR(123.34,'999.99EEEE') FROM DUAL;

SELECT TO_CHAR(123.34,'999.9EEEE') FROM DUAL;

3.GROUP SEPARATOR:(G)

------------------

SELECT TO_CHAR(4500,4G500) FROM DUAL;

4.TRAILING MINUS INDICATOR:(MI)

--------------------------------

SELECT -9000,TO_CHAR(-9000,'9999MI') FROM DUAL;

5.NEGATIVE NUMBER INDICATOR:(PR,PT)

-----------------------------------

SELECT TO_CHAR(-9000,'9999PR') FROM DUAL;--here PR indicates the < >

SELECT TO_CHAR(-9000,'9999PT') FROM DUAL;--here PT indicates the ( )

6.LOCAL CURRENCY INDICATOR:(L)

-------------------------------

SELECT TO_CHAR(4500,'999L') FROM DUAL;

7.GROUP SEPARATOR:(,)

---------------------

SELECT TO_CHAR(8400,'9,999) FROM DUAL;

8.DECIMAL INDICATOR:(.)

-----------------------

SELECT TO_CHAR(4500,'99.99') FROM DUAL;

9.DOLLAR INDICATOR:($)

----------------------

SELECT TO_CHAR(4500,'$9999) FROM DUAL;

SELECT TO_CHAR(4500,'9999$') FROM DUAL;

10.ROMAN NUMBER INDICATOR:(RN OR rn)

-----------------------------------

SELECT TO_CHAR(45,'RN') FROM DUAL;

11.HEXA DECIMAL INDICATOR:(X---XXXX)

--------------------------------------

SELECT TO_CHAR(10,'X') FROM DUAL;

SELECT TO_CHAR(11,'XX') FROM DUAL;

SELECT TO_CHAR(12,'XX') FROM DUAL;

SELECT TO_CHAR(13,'XX') FROM DUAL;

SELECT TO_CHAR(123,'XXX') FROM DUAL;

12.ZERO INDICATOR:(0)

--------------------

SELECT TO_CHAR(9,'09') FROM DUAL;

SELECT TO_CHAR(45,'990') FROM DUAL;

13.ISO CURRENCY INDICATOR:(C)

----------------------------

SELECT TO_CHAR(4560,'C9999') FROM DUAL;

DATE TO CHARACTER CONVERSION:

----------------------------------------------------

1.AD or A.D / BC or B.C INDICATOR: indicates with or without periods.

----------------------------------

SELECT TO_CHAR(SYSDATE,'AD'),TO_CHAR(SYSDATE,'BC') FROM DUAL;

2.MERIDIAN INDICATOR: (AM or A.M/PM or P.M)

-------------------------------------------

It indicates with or without periods.

SELECT TO_CHAR(SYSDATE,'AM'),TO_CHAR(SYSDATE,'PM') FROM DUAL;

3.CENTURY INDICATOR:(CC/SCC)

----------------------------

it indicates the century, S prefixes BC date with '-'

SELECT TO_CHAR(SYSDATE,'CC-AD'),TO_CHAR(SYSDATE,'SCC-AD') FROM DUAL;

4.NUMERIC WEEK DAY INDICATOR:(D-->1-7)

--------------------------------------

SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;

5.NUMERIC MONTH INDICATOR:(MM)

-------------------------------

SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;

6.NUMERIC YEAR INDICATOR:(YYYY OR SYYYY):

----------------------------------------

7.MONTH DAY INDICATOR:(DD-->1-31)

---------------------------------

8.YEAR DAY INDICATOR:(DDD--->1-366)

----------------------------------

9.ABBREVATED DAY INDICATOR:(DY)

---------------------------

SELECT TO_CHAR(SYSDATE,'DD-DY-DAY') FROM DUAL;

10.WEE DAY INDICATOR:(DAY)

--------------------------

11.ISO STANDARD YEAR WEEK INDICATOR:(IW)

---------------------------------------

it spefies the week of the year (1-52 or 1-53) based on ISO standard.

12.ISO STANDARD 4 DIGIT INDICATOR:(IYYY)

------------------------------------------

can be used combination of IYY,IY,I

13.FOUR DIGIT YEAR INDICATOR:(YYYY or SYYYY)

------------------------------------------

14.SPELLED YEAR INDICATOR:(YEAR or SYEAR)

---------------------------------------

15.WEEK OF THE MONTH:(W--->1-5)

---------------------------------

16.YEAR WEEK INDICATOR:(1-53  ANSII BASED WW)

--------------------------------------------

17.QUARTER OF THE YEAR:(Q)

-------------------------

18.JULIAN DAY INDICATOR:(J)

---------------------------

it is number of days since jan1-4712BC

19.ABBREVATED MONTH INDICATOR:(MON)

----------------------------------

20.MONTH SPELLED INDICATOR:(MONTH)

----------------------------------

21.TWELVE  HOUR CLOCK MODE:(HH or HH12)

---------------------------------

22.TWENTY FOUR HOUR CLOCK MODE:(HH24)

--------------------------------------

23.MINUTES INDICATOR:(MI)

24.SECONDS INDICATOR:(SS)

-------------------------

25.DATE FORMATE ELEMENT SUFFIXES:(TH or SP or RD or ND or ST)

---------------------------------------------------------------

========================================================================

TO_NUMBER: TO_NUMBER(CHAR,fmt,nlsparms)

-------------------------------------

IT CONVERTS CHARACTER INTO NUMBER

EX:TO_NUMBER('$1,000','L9G999') FROM DUAL;  --1000

EX:SELECT TO_CHAR(TO_NUMBER(1,234.00,'9G999D99)+TO_NUMBER(2,346.89,'9,999.99),'9G999D99')RESULT FROM DUAL;

TO_DATE:TO_DATE(CHAR,fmt,nlsparms)

----------------------------------

IT converts CHAR TO CHAR or VARCHAR2 data type

SELECT TO_DATE('12-JUN-2013','DD-MON-YYYY') FROM DUAL;

SELECT TO_CHAR(TO_DATE('12-JUN-2013','DD-MON-YYYY'),'DDSP-MONTH-YEAR') FROM DUAL;

SELECT '12-JUN-2013'+3 FROM DUAL;

SELECT TO_CHAR(TO_DATE('12-DEC-2013','DD-MON-YYYY'),'DD-MONTH-DAY') FROM DUAL;