The Princeton University Instructional Oracle Facility permits members of the Princeton University community to create an Oracle account on the Instructional Oracle database server. Under the Oracle license agreement, these Oracle accounts must only be used for educational and instructional purposes.
Features:
Instructional Oracle Account Information
Once you have used
"Create Oracle Account" link
to create your Instructional Oracle account, you have access to various views. You can query these views to obtain useful information about your Oracle account and environment. You can use the
Secure SQL Editor
to perform these queries and also get the table definitions of these views.
Consult the
SQL Editor Tutorial
on how to perform queries and get table definitions on any user view.
The following table provides a list of these views and descriptions of the views.
| View Name | Description |
| DICT | table names and table description |
| DICT_COLUMNS | column names of table names and column description |
| CAT | names of all user's tables, views, synonyms, and sequences |
| OBJ | information on all objects in your account |
| TABS | table information on all user's tables |
| COLS | column information on all user's columns |
| USER_VIEWS | view information on all user's views |
| SYN | synonyms information on all user's synonyms |
| SEQ | sequence information on all user's sequences |
| USER_CONSTRAINTS | constraint information on user's constraints |
| USER_CONS_COLUMNS | column information on user's constraints |
| IND | index information on all user's indices |
| USER_IND_COLUMNS | column information on user's indices |
Secure SQL Editor
The
Secure SQL Editor
is a secure web tool which provides access to your Instructional Oracle account.
Users access their Oracle account by executing Structured Query Language (SQL) commands in their Oracle accounts. The
Oracle 8 SQL Reference
provides detailed information on SQL and Oracle's enhancements to SQL.
The Secure SQL Editor can also execute a subset of of commands associated with Oracle's client tool SQL*Plus.
Consult
SQL*Plus User's Guide and Reference
and
SQL*Plus Quick Reference
documentation to learn more about SQL*Plus.
The Secure SQL Editor is simple to use. Instructional Oracle users submit SQL and SQLPlus commands to the web form and the SQL Editor will execute them against their accounts. The commands and resulting output from those commands are then displayed.
The
SQL Editor Tutorial
provides many examples of useful SQL and SQLPlus commands.
Secure SQL Loader
The
Secure SQL Loader
is a secure web tool which will load data directly into your Instuctional Oracle account.
This tool is built on top of the Oracle database tool SQL*Loader ( see the
SQL*Loader Online Reference
for more detailed information on that Oracle tool).
The Secure SQL Loader requires that you have two world-readable files in your OIT Unix account home directory.
One file is the data file and will contain the data to be loaded. The other file is the control file and the contents of the control file determines the target table and the rules for loading the data.
Consult the
SQL Loader Demo
on how to to use the SQL Loader.
Configuring Oracle Tools To Access Your Instructional Oracle Account
If you are planning to access your Oracle account using Oracle tools such as SQLPlus you will have to
configure either your sqlnet.ora file or the tnsnames.ora file.
If you are planning to use Oracle name servers, you can specify in your sqlnet.ora file the following Princeton University name servers,
In both cases, use "storacle8" as your host string.
Alternative Methods For Accessing Your Instructional Oracle Account
There are two other Princeton Facilities,
Princeton University DBToolBox and
Princeton University Campus CGI Facility
that can access your Instructional Oracle Account.
Important Instructions For Using DBToolBox To Access Instructional Oracle Account
DBToolBox allows authenticated users to query against many types of databases. To access the Instructional Oracle database with DBToolBox you must specify "storacle" as the server in all the query forms.
Important Instructions For Using Campus CGI Facility To Access Instructional Oracle Account
Campus CGI Facility allows authenticated users to run their own CGI programs without having to run their own Web server. Users who use Perl5 to write their CGI scripts can use Perl5/DBI to access their Instructional Oracle account. The
Instructional Oracle Perl/DBI Example Script perldbi.pl
demonstrates how to retrieve and update values for a table in an Instructional Oracle account.
The Example web page provides the actual source code for the perl5 script. You can incorporate the appropriate sections and subroutines into your own perl5 scripts.
The Example web page also provides important information concerning security issues and the testing and debugging environment.
It is important to note that if users update their CGI perl scripts to include Perl/DBI code to access to their Instructional Oracle account, then users can not run these CGI perl scripts directly from the arizonas.
Users must run these CGI perl scripts through the Campus CGI webserver.
See
Instructional Oracle Perl/DBI Example Script perldbi.pl
for more details on this issue.
Using Oracle JDBC Drivers To Access Instructional Oracle Account
Another alternative is to use Oracle JDBC Drivers to connect to your Instructional Oracle Account. Currently the Oracle8i JDBC drivers for use with JDK 1.1.x ( see documentation packages.html) have been installed in the /u/storacle/jdbc8/lib directory. To reference the drivers you must set the CLASSPATH environment variables according:
Here is a test program which accesses the the account "your_storacle_account" with password "your_storacle_password" on Storacle
Using Oracle JDB Drivers and Your Campuscgi Account To Access Instructional Oracle Account
You must copy the classes file /u/storacle/jdbc8/lib directory to your campuscgi directory and set the CLASSPATH according. In the following example, I will use "myaccount" as the campuscgi account and assume the file "StoracleTest.java" is in the that same campuscgi account
Note- to access the oracle documentation, your machine must be on the Princeton network.
names.default_domain = world
name.default_zone = world
NAMES.DIRECTORY_PATH = (ONAMES)
NAMES.PREFERRED_SERVERS =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = oranamesrvr0.princeton.edu)
(Port = 1575)
)
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = oranamesrvr1.princeton.edu)
(Port = 1575)
)
)
NAME.PREFERRED_SERVERS =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = oranamesrvr0.princeton.edu)
(Port = 1575)
)
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = oranamesrvr1.princeton.edu)
(Port = 1575)
)
)
Then you can use either "storacle" or "storacle8" as the host string.
If you are not using Oracle name servers, then use either Oracle Easy Configure Tool
to add an entry for storacle8 with protocol, TCP/IP service name storacle, hostname
storacle.princeton.edu) or manually add this to your tnsnames.ora file.
storacle8.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = storacle.princeton.edu)
(Port = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = storacle)
)
)
setenv CLASSPATH .:/u/storacle/jdbc8/lib/classes111.zip
yuma.Princeton.EDU% more StoracleTest.java
import java.awt.*;
import java.sql.*;
class StoracleTest {
public static void main(String args[]) {
// declare to stringbuffer to hold sql statements
StringBuffer Sql1 = new StringBuffer(124);
System.out.println("Content-type: text/html\n\n");
System.out.println("<html>\n");
try {
System.out.println("<head><title>Test Java</title></head>\n");
System.out.println("<body bgcolor=\"white\">\n");
System.out.println("<center><H4>Test Java</H4></center>\n");
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
// Connect to the Instructional Oacle database
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@storacle:1521:storacle","your_storacle_account","your_storacle_password");
// Get the auto commit status and if true,disable it
// This way I can have control of when the updates to database is to be committed
if(conn.getAutoCommit()) {
System.out.println("<br>Auto commit status is true so disabled it\n");
conn.setAutoCommit(false);
System.out.println("<br>New Auto commit status is "+conn.getAutoCommit()+ "\n");
} else {
System.out.println("<br>Auto commit status is false\n");
}
System.out.println("<center><H4>Results by Specifying Column Index</H4></center>\n");
// create a Statement Object
Statement stmt = conn.createStatement ();
//Specify the SQL statement to query the databaese
String sql1string = new String("select id,text from test_table");
Sql1.append(sql1string);
ResultSet rset = stmt.executeQuery (sql1string);
// Get the results via getString ( specify want results from column 1,2 of SQL statement)
// and print the results
while (rset.next ()) {
System.out.println ("<BR> id["+rset.getString(1)+ "]text["+rset.getString(2)+"]\n");
}
System.out.println("<center><H4>Now Get Execute SQL to Return Row Count</H4></center>\n");
String sql4string = new String("select count(*) from test_table where id < 5");
Sql1.setLength(0);
Sql1.append(sql4string);
ResultSet rset4 = stmt.executeQuery (sql4string);
while (rset4.next ()) {
System.out.println ("<BR> Number of Rows With Id < 5 is "+rset4.getString(1)+"\n");
}
System.out.println("<center><H4>Now Update Rows In Table</H4></center>\n");
String sql3string = new String("update test_table set text=upper(text) where id < 5");
Sql1.setLength(0);
Sql1.append(sql3string);
int count = stmt.executeUpdate (sql3string);
System.out.println ("<BR> Rows Updated is "+count+"\n");
System.out.println("<center><H4>Commit Update and Get Results by Specifying Column Name</H4></center>\n");
conn.commit();
Sql1.setLength(0);
Sql1.append(sql1string);
// Execute same SQL statment sql1string but in getString specify columns "id" and "text" to get results
ResultSet rset1 = stmt.executeQuery (sql1string);
// Print the results
while (rset1.next ()) {
System.out.println ("<br> id["+rset1.getString("id")+ "]text["+rset1.getString("text")+"]\n");
}
System.out.println("<center><H4>Should Now Get SQLException Handling</H4></center>\n");
// create a invalid sql statement by referencing non-existing table "ttttest_table"
String sql2string = new String("select text from ttttest_table");
// clear my sql string so I can re load with new sql command
Sql1.setLength(0);
Sql1.append(sql2string);
ResultSet rset2 = stmt.executeQuery (sql2string);
System.out.println("</body></html>\n");
} catch (SQLException e) {
System.out.println("<BR>**Caught SQLException**\n");
if (Sql1.length() > 0) {
System.out.println("<BR>Bad Sql["+Sql1 +"]\n");
}
System.out.println("<BR>"+ e.getMessage()+ "\n");
System.out.println("</body></html>\n");
}
}
}
yuma.Princeton.EDU% javac -verbose StoracleTest.java
request 1/1 : StoracleTest.java
process 1/1 : StoracleTest.java
done 1/1 : StoracleTest.java
1 unit compiled
1 .class file generated
----------
1. WARNING in StoracleTest.java (at line 1)
import java.awt.*;
^^^^^^^^
The import java.awt is never used
----------
1 problem (1 warning)
Then execute the class file.
yuma.Princeton.EDU% java StoracleTest
Content-type: text/html
<html>
<head><title>Test Java</title></head>
<body bgcolor="white">
<center><H4>Test Java</H4></center>
<br>Auto commit status is true so disabled it
<br>New Auto commit status is false
<center><H4>Results by Specifying Column Index</H4></center>
<BR> id[1]text[value 1]
<BR> id[2]text[value 2]
<BR> id[6]text[value 6]
<center><H4>Now Get Execute SQL to Return Row Count</H4></center>
<BR> Number of Rows With Id < 5 is 2
<center><H4>Now Update Rows In Table</H4></center>
<BR> Rows Updated is 2
<center><H4>Commit Update and Get Results by Specifying Column Name</H4></center>
<br> id[1]text[VALUE 1]
<br> id[2]text[VALUE 2]
<br> id[6]text[value 6]
<center><H4>Should Now Get SQLException Handling</H4></center>
<BR>**Caught SQLException**
<BR>Bad Sql[select text from ttttest_table]
<BR>ORA-00942: table or view does not exist
</body></html>
setenv CLASSPATH .:/usr/campuscgi/myaccount/classes111.zip
phoenix.Princeton.EDU%pwd
/usr/campuscgi/myaccount
Next build the class file in the campuscgi account directory
phoenix.Princeton.EDU% javac -verbose StoracleTest.java
Next verifies that it runs
phoenix.Princeton.EDU% java StoracleTest
Next created a csh file called mytest with the following lines
#!/bin/csh
setenv CLASSPATH .:/usr/campuscgi/myaccount/classes111.zip
/usr/bin/java StoracleTest
Now you can run this script with the following URL
http://campuscgi.princeton.edu/~myaccount/mytest
Oracle Documentation
Please direct comments and questions to
request-WWW.html