Oracle, Oracle Database August 18, 2010 3

Creating an online Phone Book with Oracle Database and SQLDeveloper

This tutorial shows how to create an Online Phone Book with search feature using
Oracle Database and SQLDeveloper.

What’s needed?

  1. Oracle Database
  2. Oracle SQLDeveloper
  3. A data set – names and phone numbers.

Let’s roll…

1.    First let’s prepare our data set in excel sheet.

  • Create a dump of your phone book into an excel sheet; something like this:

  • The following formula will create a set of SQL inserts that we can use to load data into a database table:
= "INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( '"&A2&"', '"&B2&"', '"&C2&"');"

Apply above formula to all rows in excel sheet:

2.    Next, let’s create a table “MYPHONEBOOK” in a database:

The following SQL DDL does the job (a table with three columns):

CREATE TABLE MYPHONEBOOK
(
"FIRST_NAME"     VARCHAR2(20 CHAR),
"LAST_NAME"      VARCHAR2(20 CHAR),
"PHONE_NUMBER"   VARCHAR2(20 CHAR)
)
;

 

3.    Our Table is ready for data load; copy and paste all insert statements into SQLDeveloper’s worksheet and run it;

INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( 'Donald', 'OConnell', '650.507.9833');
INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( 'Douglas', 'Grant', '650.507.9844');
INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( 'Jennifer', 'Whalen', '515.123.4444');
...
...
...

 

4.    Now let’s create an Online Phone Book using SQLDeveloper’s export feature:

  • Open MYPHONEBOOK’s table “Data” tab > and right click > Export Data > html…

  • Save exported data as MYPHONEBOOK.htm file:

5.    We are done! Now embed htm file at any webhosting plan, web server or use it locally. Enjoy your online phone book with searching …

Click here to see a working example of the Online Phone Book.


Cheers!!