Due to a surpising change in the rules of AQUA we are going to
use SQL Developer instead. While it is slightly less user-friendly
and more limited to Oracle, it has everything that we urgently need.
Step 1: Download
Download SQL Developer from here
• Click: O Accept License Agreement
• Click on the appropriate Download on the page.
• At this point you need to create an Oracle account.
It is fairly problem-free. I suggest you use the NJIT email
address for that purpose.
• Log in
I will continue with Windows 7, because this is what I am using.
Hopefully it is more or less the same for Mac users.
• Click on the downloaded zip file.
• Close the RAR options menu.
• Double-click on the the sqldeveloper directory.
• Double-click on the the sqldeveloper.exe file.
• Click No (If you already have installations then you don't need this explanation.)
• Un-Check the Allow button and click ok.
Step 2: Connect to Oracle
• In the upper left window called Connections right-click on the word
Connections next to the drawing of a disk.
• Click on New Connection
• Invent a name for this connection. I am using the name Prophet2019.
• Type in your DATABASE Username and Password. The Username is
probably identical to your UCID. However, the password is a database
password you either have from last semester or that you need to generate.
The syllabus contains a link to the database password web page.
If you have such a password it is a randomly generated string.
• If you are on your own computer, click on SAVE. In a lab, DO NOT click
on SAVE.
• The Hostname is prophet.njit.edu
• Leave the port as it is.
• The SID is course
• No authentication is required as of today.
• Click on connect.
You should now see a "Worksheet" in the right window.
Also in the upper left window you will now see your
server (for me: prophet2018).
Step 3: Autocommit
• In the top line of menus click on Tools.
At the bottom of the Tools menu click on Preferences.
• Scroll down to Database and click on the + sign.
• Click on Advanced
• In the main area of Preferences click on Autocommit
Then click OK.
Step 4: Writing a simple PL/SQL program and
making the output visible
Clear everything you had in the Worksheet.
To avoid strange interactions.
• In the top line of menus click on View.
Scroll down to DBMS Output.
At this moment the Right Window should split itself
and at the bottom there should be a subwindow alled
Dbms Output.
• Click on the Green Plus sign.
Click OK
In the Worksheet write the following program:
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
Run the program, and the output should appear at the bottom.
1
2
3
4
5
6
7
8
9
10
Note that if you have more than one program in the Worksheet
or if you mix SQL statements and program, then you need to
put a slash / after each program.
Step 5: Importing a data file
This is ONLY AN EXAMPLE.
Different files require different approaches.
We will not do exactly tis example.
This is fairly complicated. Thus I will
walk you through it step by step.
• First you need a file.
Open a new EXCEL file.
Below is the data we will use.
This is of course only the beginning of a much bigger file.
Birmingham;AL;Birmingham-Shuttlesworth International (BHM)
Dothan;AL;Dothan Regional (DHN)
Huntsville;AL;Huntsville International-Carl T Jones Field (HSV)
Mobile;AL;Mobile Downtown (BFM)
Mobile;AL;Mobile Regional (MOB)
Montgomery;AL;Montgomery Regional (MGM)
Muscle Shoals;AL;Northwest Alabama Regional (MSL)
Adak Island;AK;Adak (ADK)
Akhiok;AK;Akhiok Airport (AKK)
Akiachak;AK;Akiachak Airport (KKI)
Akiak;AK;Akiak Airport (AKI)
Akun;AK;Akun Airport (7AK)
Akutan;AK;Akutan Airport (KQA)
Alakanuk;AK;Alakanuk Airport (AUK)
Aleknagik;AK;Aleknagik New (WKK)
Allakaket;AK;Allakaket Airport (AET)
Alpine;AK;Alpine Airstrip (DQH)
Ambler;AK;Ambler River (ABL)
Amook Bay;AK;Amook Bay Airport (AOS)
Anaktuvuk Pass;AK;Anaktuvuk Pass Airport (AKP)
Anchorage;AK;Lake Hood Strip (DQL)
Anchorage;AK;Merrill Field (MRI)
Anchorage;AK;Ted Stevens Anchorage International (ANC)
Angoon;AK;Angoon Seaplane Base (AGN)
Aniak;AK;Aniak Airport (ANI)
Anvik;AK;Anvik Airport (ANV)
Arctic Village;AK;Arctic Village Airport (ARC)
Atka;AK;Atka Airport (AKB)
Atmautluak;AK;Atmautluak Airport (ATT)
Atqasuk;AK;Atqasuk Edward Burnell Sr. Memorial (ATK)
Barrow;AK;Wiley Post/Will Rogers Memorial (BRW)
Barter Island;AK;Barter Island LRRS (BTI)
Bartletts;AK;Bartletts Airport (A2K)
Bear Fish Camp;AK;Bear Fish Camp Airport (AK2)
Beaver;AK;Beaver Airport (WBQ)
Bethel;AK;Bethel Airport (BET)
Bettles;AK;Bettles Airport (BTT)
Birch Creek;AK;Birch Creek Airport (KBC)
Blue Fox Bay;AK;Blue Fox Bay Airport (BFB)
Brevig Mission;AK;Brevig Mission Airport (KTS)
Brooks Lodge;AK;Brooks Lodge Airport (RBH)
Buckland;AK;Buckland Airport (BKC)
Calder Bay;AK;Calder Bay Airport (UXI)
Candle;AK;Candle 2 (CDL)
Cape Lisburne;AK;Cape Lisburne LRRS (LUR)
Cape Newenham;AK;Cape Newenham LRRS (EHM)
Cape Romanzof;AK;Cape Romanzof LRRS (CZF)
Cape Simpson;AK;Cape Simpson Airport (A40)
Central;AK;Central Airport (CEM)
Chalkyitsik;AK;Chalkyitsik Airport (CIK)
Chatham;AK;Chatham Airport (CYM)
Chefornak;AK;Chefornak Airport (CYF)
Chenega;AK;Chenega Bay (NCN)
Chevak;AK;Chevak Airport (VAK)
Chicken;AK;Chicken Airport (CKX)
Chignik Bay;AK;Chignik Bay Airport (KCG)
Chignik Lagoon;AK;Chignik Lagoon Airport (KCL)
Chignik;AK;Chignik Lake (KCQ)
Chisana;AK;Chisana Airport (CZN)
Chomondely Sound;AK;Chomondely Sound Airport (ZXH)
Chuathbaluk;AK;Chuathbaluk Airport (CHU)
Circle;AK;Circle City (IRC)
Clarks Point;AK;Clarks Point Airport (CLP)
Clover Bay;AK;Clover Bay Airport (ZXN)
Coffman Cove;AK;Coffman Cove Seaplane Base (KCC)
Cold Bay;AK;Cold Bay Airport (CDB)
Coldfoot;AK;Coldfoot Airport (CXF)
Cordova;AK;Merle K Mudhole Smith (CDV)
Corner Bay;AK;Corner Bay Airport (CBA)
Craig;AK;Craig Seaplane Base (CGA)
Crooked Creek;AK;Crooked Creek Airport (CKD)
Danger Bay;AK;Danger Bay Airport (DGB)
Deadhorse;AK;Deadhorse Airport (SCC)
Deadhorse;AK;Inigok (FVZ)
Deadhorse1;AK;Point Thomson Airstrip (A1K)
Deadmans Bay;AK;Deadmans Bay Airport (A02)
Deering;AK;Deering Airport (DRG)
Dillingham;AK;Dillingham Airport (DLG)
Dora Bay;AK;Dora Bay Airport (DOF)
Eagle;AK;Eagle Airport (EAA)
Edna Bay;AK;Edna Bay Airport (EDA)
Eek;AK;Eek Airport (EEK)
Egegik;AK;Egegik Airport (EGX)
Ekwok;AK;Ekwok Airport (KEK)
Elfin Cove;AK;Elfin Cove Seaplane Base (ELV)
Elim;AK;Elim Airport (ELI)
Emmonak;AK;Emmonak Airport (EMK)
Excursion Inlet;AK;Excursion Inlet Seaplane Base (EXI)
Fairbanks;AK;Fairbanks International (FAI)
Fairbanks;AK;Ladd AAF (FBK)
False Pass;AK;False Pass Airport (KFP)
Fort Glenn;AK;Fort Glenn Airport (FQW)
Fort Yukon;AK;Fort Yukon Airport (FYU)
Frazer Lake;AK;Frazer Lake Airport (VZE)
Funter Bay Alaska;AK;Funter Bay (FNR)
Galena;AK;Edward G. Pitka Sr. (GAL)
Gambell;AK;Gambell Airport (GAM)
Ganes Creek;AK;Ganes Creek Airport (GEK)
Golovin;AK;Golovin Airport (GLV)
Goodnews Bay;AK;Goodnews (GNU)
Grace Harbor;AK;Grace Harbor Airport (FVX)
Grayling;AK;Grayling Airport (KGX)
Gulkana;AK;Gulkana Airport (GKN)
Gustavus;AK;Gustavus Airport (GST)
Haines;AK;Haines Airport (HNS)
Healy;AK;Healy River (HKB)
Hollis;AK;Hollis Seaplane Base (HYL)
Holy Cross;AK;Holy Cross Airport (HCR)
Copy and paste all of the above into your EXCEL file.
Save this file with the name airports2019.csv
(Click on Yes when EXCEL warns you about format.)
• Now create the empty table in SQL Developer as follows.
create table airports2019 (
city varchar2(30),
state varchar2(2),
airportname varchar2(90));
• Check if the table is really there.
In the upper left window click on the black + sign next to
the disk symbol for prophet2018.
Then click on the + next to Tables.
Make sure that Airports2019 is there.
If it isn't you need to refresh the connection.
Clicking on the two blue arrows should do this.
• Right click on the name of the table.
Click on Import Data.
In the field Import Data File: browse to your fila airports.csv
Change the Delimiter menu to ;
UNCLICK the box next to Header.
Click Next
Leave the Import Method: As insert
It should NOT say: Send Create Script to SQL Worksheet
Click Next
There should be 3 columns. Click on each one of those. The correct table-column name should already be there, taken automatically from the table declaration.
Click Next again
Change the Match By Menu to Position
(Any Red Exclamation Marks should disappear at this point.)
Click Next
Click Finish
It should show you a message ... Tasks successful
If that did not happen then there is either an error
in your data file or you made an error when doing the above
steps.
Click OK (if there is no error). Otherwise go back.
Make sure you have your Worksheet in the Right Window open.
To be sure the table is there, do a
select * from airports2019;