Personal Oracle8i

• Create a new user

• Create a new table

• Enter data into a new table

• Export & import data

• Start and exit SQL Plus

• SQL Plus Syntax

Create A New User - 1

• Start• Select Program• Select Oracle-oracle8i_home• Select Application Development• Select Oracle8i Navigator• Select Oracle8i Personal Edition• Select Local Database• Select User icon• Right click mouse• Select New

Create A New User - 2

• Enter your name for the Name

• Enter your password for the New

• Enter your password again for the Confirm

• Select Role/Privilege tab

• Select Role

• Select <<

• Select Privilege

Create A New User - 3

• Select <<

• Select OK

Create a New Table - 1

• Start• Select Program• Select Oracle oracle8i_home• Select Application Development• Select Oracle8i Navigator• Select Oracle8i Personal Edition• Select Local Database• Select Table icon• Right Click mouse• Select New

Create a New Table - 2

• Select create table manually

• Select OK

• Select General tab

• Enter Faculty for the Name

• Select your name for the Owner

• Select Design tab

• Enter fid as first column name

Create a New Table - 3

• Select number for fid type

• Enter 4 as the size for fid

• Make sure the can be null box is clear

• Make sure the unique box is checked

• Select fid as primary key for the table

• Enter flname as second column name

• Select char for flname type

Create a New Table - 4

• Enter 10 for the size

• Make sure the can be null box is clear

• Make sure the unique box is clear

• Enter ffname for the third column

• Select char for the ffname type

• Enter 10 for the size

Create a New Table - 5

• Make sure the can be null box is clear

• Make sure the unique box is clear

• Enter locid as fourth column

• Select number for the locid type

• Enter 4 for the size

• Make sure the can be null box is clear

• Make sure the unique box is clear

Create a New Table - 6

• Enter fphone for the fifth column

• Select number for the fphone type

• Enter 10 for the size

• Make sure the can be null box is clear

• Make sure the unique box is clear

Create a New Table - 7

• Enter frank for the fifth column

• Select char for the frank type

• Enter 4 for the size

• Make sure the can be null box is clear

• Make sure the unique box is clear

• Select OK

Input Data Into A Table -1• Start• Select Program• Select Oracle oracle8i_home• Select Application Development• Select Oracle8i Navigator• Select Oracle8i Personal Edition• Select Local Database• Select Table icon• Highlight and right click table named faculty

Input Data Into A Table - 2

• Select Open

• Enter the first record

• Select the row label for row1 to select the row and right click

• Select Insert after, a blank row appear under row 1

• Enter the second record

Input Data Into A Table - 3

• Repeat the above processes until you finish entering data

• Select close button (X icon in the upper right corner)

• Select Yes to commit the data

Export Data - Method 1

• Program• MS-DOS Prompt• Type exp user name/password (such as exp

nancy/nancy)• Specify export file (such as a:extf1.dmp

where a: is the drive)• Answer the questions to execute the expert

program

Import Data - Method 1

• Program• MS-DOS Prompt• Type imp user name/password (such as imp

nancy/nancy)• Specify Import file name (such as

a:extf1.dmp)• Answer the questions to execute the import

program

Start and Exit SQL Plus

• Start SQL Plus– Start– Select Program– Select Oracle oracle8i_home– Select Application Development– Select SQL– Enter your name for User Name– Enter Enter your password for Password

• Exit SQL Plus– Enter exit at SQL prompt

SQL Plus Syntax - 1

• Use semicolon (;) to terminate a statement

• SQL is not case-sensitive

• SQL statement can be entered into several lines

• Use comma (,) to separate attributes

• Use a pair of single quotes (‘) for any character string

SQL Plus Syntax - 2

• Use percentage sign (%) for a group of wildcard characters

• Use underscore (_) for one wildcard character

• Table name limited to 30 characters, no reserved word

SQL Plus Syntax - 3

• Attribute name limited to 30 characters, no reserved word

SQL Plus Statement Syntax - 1

• LIST - displays most current SQL statements from buffer

• Line number - changes the current line number to the entered line number and shows the listed line number

• LIST line number - show the indicated line number text

SQL Plus Statement Syntax - 2

• CHANG/old text/new text/ - replaces an existing text string (old text) in the line with a new text string (new text)

• DELETE - deletes the current line

SQL Plus Statement Syntax - 3

• SAVE file name - saves the most current SQL statements to a file

• GET file name - retrieves SQL statements from the file name

• R or / - runs the most current statements• SPOOL - saves SQL statements, its query, and

other results to a file– SPOOL file name

– SPOOL OFF - terminates SPOOL

SQL Plus SPOOL File - Menu

• SPOOL file– Select File– Select Spool– Select Spool file– Type drive (such as a:)– Type file name (such as f1)– Select spool file (*.lst)

• SPOOL off– File– Spool– Spool off