Oracle DBA FAQ - Understanding SQL Basics

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

What Are the ANSI Data Types Supported in Oracle?

The following ANSI data types are supported in Oracle:

CHARACTER(n) / CHAR(n)
CHARACTER VARYING(n) / CHAR VARYING(n)
NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n)
NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n)
NUMERIC(p,s)
DECIMAL(p,s)
INTEGER / INT
SMALLINT
FLOAT
DOUBLE PRECISION
REAL

How To Write Text Literals?

There are several ways to write text literals as shown in the following samples:

SELECT 'FYICenter.com' FROM DUAL -- The most common format
FYICenter.com

SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped
It's Sunday! 

SELECT N'Allo, C''est moi.' FROM DUAL -- National chars
Allo, C'est moi.

SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter
It's Sunday!

How To Write Numeric Literals?

Numeric literals can coded as shown in the following samples:

SELECT 255 FROM DUAL -- An integer
255

SELECT -6.34 FROM DUAL -- A regular number
-6.34

SELECT 2.14F FROM DUAL -- A single-precision floating point
2.14 

SELECT -0.5D FROM DUAL -- A double-precision floating point
-0.5

How To Write Date and Time Literals?

Date and time literals can coded as shown in the following samples:

SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format
03-OCT-02

SELECT TIMESTAMP '1997-01-31 09:26:50.124' FROM DUAL
31-JAN-97 09.26.50.124000000 AM
-- This is ANSI format

How To Write Date and Time Interval Literals?

Date and time interval literals can coded as shown in the following samples:

SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH
  FROM DUAL 
  -- 123 years and 2 months is added to 2002-10-03
03-DEC-25

SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL
  -- 123 years is added to 2002-10-03
03-OCT-25

SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL
  -- 299 months years is added to 2002-10-03
03-SEP-27 

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL
04-FEB-97 02.39.00.346000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL
04-FEB-97 02.38.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL
07-MAR-98 02.26.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '400' DAY(3) FROM DUAL
07-MAR-98 09.26.50.124000000 AM

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL
31-JAN-97 08.39.00.346222200 PM 

SELECT TIMESTAMP '1997-01-31 09:26:50.124' 
  + INTERVAL '30.12345' SECOND(2,4) FROM DUAL
31-JAN-97 09.27.20.247500000 AM

(Continued on next part...)

Part: 1 2 3 4 5 6

More...