Archive

Archive for July, 2012

Remove constraints for existing columns in Oracle

July 27, 2012 Comments off

Syntax: ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

Example1: ALTER TABLE ORDERS DROP CONSTRAINT PK_NEW1;

Advertisements
Categories: SQL, Technical

Add constraints to the existing column in Oracle

July 27, 2012 Comments off

Syntax: ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME CONSTRAINT

Example 1: ALTER TABLE ORDERS MODIFY O_CODE PRIMARY KEY;

Example 2: ALTER TABLE ORDERS MODIFY O_CODE CONSTRAINT PK_NEW1 PRIMARY KEY;

Example 3: ALTER TABLE ORDERS ADD CONSTRAINT PK_NEW1  PRIMARY KEY(O_CODE);

Example 4: ALTER TABLE ORDERS ADD CONSTRAINT PK_NEW1  PRIMARY KEY(O_CODE,SALES_CODE);

Example 4: ALTER TABLE ORDERS ADD CONSTRAINT CHK_NEW1  CHECK(SALES_CODE>=1000);

 

 

Categories: SQL, Technical

DROP columns and set columns unused in Oracle

July 27, 2012 Comments off

DROP COLUMN

——————-

Syntax 1: ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

Example 1: ALTER TABLE ORDERS DROP COLUMN O_CODE;

Syntax 2: ALTER TABLE TABLE_NAME DROP(COLUMN_NAME);

Example 2: ALTER TABLE ORDERS DROP(O_CODE);

SET COLUMN UNUSED:

—————————–

Syntax: ALTER TABLE TABLE_NAME SET UNUSED COLUMN COLUMN_NAME;

Example: ALTER TABLE ORDERS SET UNUSED COLUMN O_CODE;

 

DROP UNUSED COLUMNS:

———————————-

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

 

Categories: SQL, Technical

Rename a column name in Oracle

July 27, 2012 Comments off

Syntax: ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME to NEW_COLUMN_NAME;

Example: ALTER TABLE ORDERS RENAME ORDER_CODE to O_CODE;

Categories: SQL, Technical

Modifying existing column name in Oracle

July 27, 2012 Comments off

Syntax: ALTER TABLE TABLE_NAME MODIFY(Column_name datatype …constraints);
Example: ALTER TBALE ORDERS MODIFY( ORDER_CODE NUMBER(10) NOT NULL);

Categories: SQL, Technical

Adding column to existing table in Oracle

July 27, 2012 Comments off

Syntax: Alter table table_name add(column_name datatype(size));

Example: ALTER TABLE ORDERS add(ORDER_CODE NUMBER(10));

Categories: SQL, Technical

Conversion functions in Oracle SQL

July 26, 2012 Comments off

Function 1: TO_NUMBER

Purpose : Returns the given format of data to number

Syntax: TO_NUMBER(‘param1′,’Format’)

Example : SELECT TO_NUMBER(‘$15,789.22′,’$999,999.99’)  FROM DUAL;

Output:  15789.22

———————————————————————————————

Function 2: TO_CHAR

Purpose : Returns the given data into character datatype

Syntax: TO_char(‘param1′,’Format’)

Example : SELECT TO_char(sysdate,’YYYY-MM-DD’)  FROM DUAL;

Output:  2012-07-26

———————————————————————————————

Function 3: TO_DATE

Purpose : Returns the given data into date datatype

Syntax: TO_DATE(‘param1′,’Format’)

Example : SELECT TO_DATE(sysdate,’YYYY-MM-DD’)  FROM DUAL;

Output:  12-JUL-26

———————————————————————————————

Function 3: TO_TIMESTAMP

Purpose : Returns the given data into timestamp datatype

Syntax: TO_TIMESTAMP(‘param1′,’Format’)

Example : SELECT TO_TIMESTAMP(sysdate,’YYYY-MM-DD’)  FROM DUAL;

Output:  12-JUL-26 12.00.00.000000000 AM

———————————————————————————————

Function 4: TO_DSINTERVAL

Purpose : Returns day to second interval

Syntax: TO_DSINTERVAL(‘interval Data’)

Example : SELECT TO_DSINTERVAL(‘1 08:45:45.7846’)  FROM DUAL;

Output:  1 8:45:45.784600000

———————————————————————————————

Function 5: TO_YMINTERVAL

Purpose : Returns Years to months interval

Syntax: TO_YMINTERVAL(‘y-m’)

Example : SELECT TO_YMINTERVAL(’04-05′)  FROM DUAL;

Output:  4-5

Categories: SQL, Technical