Oracle Database

The main differences between BLOB and CLOB in Oracle are:

* BLOB stores values as LOB (Large OBject) in bitstreams.

* CLOB stores values as LOB (Large OBject) in character steams.

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

SELECT 'rendc.org' FROM DUAL -- The most common format

rendc.org

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!

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

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

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

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

03-OCT-07

SELECT TIMESTAMP '0227-01-31 09:26:50.124' FROM DUAL

31-JAN-07 09.26.50.124000000 AM

-- This is ANSI format

Oracle Database XE is free for runtime usage with the following limitations:

* Supports up to 4GB of user data (in addition to Oracle system data)

* Single instance only of Oracle Database XE on any server

* May be installed on a multiple CPU server, but only executes on one processor in any server

* May be installed on a server with any amount of memory, but will only use up to 1GB RAM of available memory

You can convert numeric values to characters by using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(4123.4570) FROM DUAL

123.457

SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL

$4,123.46

SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL

-4.12E+03

Oracle Database 10g Express Edition is available for two types of operating Systems:

* Linux x86 - Debian, Mandriva, Novell, Red Hat and Ubuntu

* Microsoft Windows

You can convert characters to numbers by using the TO_NUMBER() function as shown in the following examples:

SELECT TO_NUMBER('4123.4570') FROM DUAL

4123.457

SELECT TO_NUMBER(' $4,123.46','$9,999,999.99') FROM DUAL

4123.46

SELECT TO_NUMBER(' -4.12E+03') FROM DUAL

-4120

To install 10g universal edition, double click, OracleXEUniv.exe, the install wizard starts. It will guide you to finish the installation process. You should take notes about:

* The SYSTEM password you selecte: globalguideline.

* Database server port: 1521.

* Database HTTP port: 8080.

* MS Transaction Server port: 2030.

* The directory where 10g XE is installed: oraclexe

* Hard disk space taken: 1655MB.

If you want to check your fresh installation of 10g Express Edition without using any special client programs, you can use a Web browser with this address, http://localhost:8080/apex/.

You will see the login page. Enter SYSTEM as the user name, and the password (globalguideline), you selected during the installation to log into the server.

Visit different areas on your 10g XE server home page to make sure your server is running OK.

You can also get to your 10g XE server home page by going through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Go To Database Home Page

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;

-- SYSDATE returns the current date

07-MAY-2006

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;

2006/05/07

SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;

MAY 07, 2006

SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL;

May 7, 2006

SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL;

SUNDAY, MAY 7, 2006

You can convert dates to characters using the TO_DATE() function as shown in the following examples:

SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL;

07-MAY-06

SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL;

07-MAY-06

SELECT TO_DATE('MAY 07, 2006', 'MONTH DD, YYYY')

FROM DUAL;

07-MAY-06

SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL;

07-MAY-06

SELECT TO_DATE('SUNDAY, MAY 7, 2006',

'fmDAY, MONTH DD, YYYY') FROM DUAL;

07-MAY-06

If you want to shutdown your 10g Express Edition server, go to the Services manager in the control panel. You will a service called OracleServiceXE, which represents your 10g Express Edition server.

Select OracleServiceXE, and use the right mouse click to stop this service. This will shutdown your 10g Express Edition server.

You can also shutdown your 10g XE server through the start menu. Select All Programs, then Oracle Database 10g Express Edition, and then Stop Database.

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;

04:49:49

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL;

-- Error: SYSDATE has no fractional seconds

SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL;

16:52:57.847000000

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

-- Seconds past midnight

69520

You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'),

'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

-- Default date is the first day of the current month

01-MAY-2006 04:49:49

SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000',

'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9')

FROM DUAL;

01-MAY-2006 16:52:57.847000000

SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'),

'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

01-MAY-2006 19:18:40