Using Oracle SQL
Tips and Techniques
Database Management Systems
Fundamentals
CSCI 2020
Oracle's Structure
SQL PHP
Data Interface
Plus
SQL
JDBC
PL-SQL
Interface
CORE
ODBC
HTTP Server Interface
Pro-C
Oracle at ETSU
• The dept. has an Oracle server
▫ Server [Link]
▫ Database SID [Link]
• You have an account:
▫ User Name is the same as your campus account
▫ Password is initially dbms
Choices
• SQL-Developer
▫ Available for download from ORACLE
• ECLIPSE add-on
▫ Full package on course site
• SQL-Plus Instant Client
▫ Available for download from ORACLE
▫ Install Instructions on course site
• .Visual Studio add-on
▫ Available for download from ORACLE
SQL Plus Instant Client
SQL
Developer
ECLIPSE in the labs
From Window choose
Open Perspective
then choose
Other…
ECLIPSE in the labs
Choose SQL Explorer
Then click OK
ECLIPSE in the labs
If the Connection Window is not visible
go to Window then choose Show View and
select the Connections window
ECLIPSE in the labs
In the Connection Window
right click ETSU_SERVER and
select Connect New User
ECLIPSE in the labs
Fill in your User and
Password and then
uncheck the
AutoCommit box
and press OK
You will connect
ECLIPSE in the labs
The SQL Editor will appear
here you can enter your
commands. When ready to
execute them click the
‘Running Man’ icon on the
toolbar
ECLIPSE in the labs
Your output will
appear in the pane
right below . To
clear the SQL
Editor Window
for the next
command click the
Eraser Icon on
the tool bar.
ECLIPSE in the labs
To load a file of
commands click
on the Folder
Icon on the
toolbar and
locate the file
Your can save the
ECLIPSE in the labs contents of an SQL
Editor Window
click the Diskette
Icon on the tool
bar.
Then choose no
to save the code
outside of a
project.
ECLIPSE in the labs
The Save as
dialog box
will appear to
allow you to
save the file
ECLIPSE in the labs
All of your past commands
are accessible through the
SQL History Window
ECLIPSE in the labs
The Database Structure Window
allows you to browse the tables in your
user
ECLIPSE in the labs
The Database Detail Window gives many type of
information on the tables from the Database
Structure Window the Columns Tab lists every
column in the table and their column types
ECLIPSE in the labs
The Preview Tab shows you the content of the table
ECLIPSE in the labs
When you are done go
to the Connection
Window, highlight your
user name and close all
of the connections for
your user
Commit Protocol
• COMMIT
▫ Make changes part of permanent DB
• ROLLBACK
▫ Undo changes back to beginning or last or
specified SAVEPOINT
• Auto-Commit
▫ SET AUTOCOMMIT ON Immediate
▫ SET AUTOCOMMIT OFF Deferred
Data Types
• Character
▫ VARCHAR2(n)
▫ CHAR (n)
▫ LONG
▫ CLOB
• Numeric
▫ NUMBER (p,s)
INTEGER, INT s = 0
REAL, FLOAT, DECIMAL
• Times
▫ DATE
▫ TIMESTAMP
• Binary
▫ RAW(N) LONGRAW
▫ BLOB
▫ XML
Data Type Surprises
• NULL <> any other value
▫ Int NULL <> 0
▫ Char NULL <> “”
▫ Date NULL <> “”
• Date
▫ Default Format ‘dd-mon-yyyy’
▫ To get it to display correctly (sql+ only) add this to
your command files
alter session set nls_date_format = 'dd-mon-yyyy';