Unformatted text preview:

Using ORACLE in the CSLabDr. Weining ZhangDepartment of Computer ScienceUniversity of Texas at San Antonio19th September 20021 IntroductionORACLE is a Relational Database Management System (DBMS) popularly used by many enter-prises, and is available on the SUN workstations in the CSLab, under Solaris 2.7 (UNIX) operatingsystem, for students taking a database course. ORACLE supports the standard SQL language andhas an interactive as well as a programming interface to support operations such as database cre-ation, data manipulation (including querying, insertion, deletion, and updating), data integrity andsystem security maintenance, transaction processing, data entry, report writing, etc. The currentinstallation is ORACLE8i (version 8.1.7). This article discusses issues regarding the use of Oracledatabases and is intended as a starting point for students to use the ORACLE database system.2 ORACLE User AccountsTo access to an ORACLE database, each user must have a UNIX account on the computers in theCSLab and an ORACLE database account. Technically speaking, each user would have two setsof user name and password, one set for the CSLab Unix account, and another set for the ORACLEdatabase account. The ORACLE user name and password are created by a Database Administrator(DBA) who is also the instructor of the database course. The user name of an ORACLE accountstarts with the first letter of the user’s first name, followed by up to first 4 letters of the user’slast name, and followed by the first 3 digits of the course number. The initial Oracle password is“utsannnn”, where “nnnn” is the last 4 digits of the user’s UTSA student number. For example,the account user name and password for Joe Smith with SSN @00456789 in CS3743 will be“jsmit374” and “utsa6789”, respectively. The password can be changed subsequently using theSQL ALTER USER or GRANT statements as described later in this article.In order to use Oracle, an appropriate UNIX environment must be set up. This involves correctsettings of several environment variables in UNIX configuration file .cshrc or .kshrc in auser’s UNIX home directory, depending on the default shell in use.For csh, add the following lines to the .cshrc file.setenv ORACLE_SID "acdb"1Zhang Using ORACLE in the CSLab 19th September 2002setenv ORACLE_BASE /oracle/u01/app/oraclesetenv ORACLE_HOME $ORACLE_BASE/product/8.1.7setenv PATH ${PATH}:$ORACLE_HOME/binsetenv LD_LIBRARY_PATH $ORACLE_HOME/lib:$LD_LIBRARY_PATHsetenv ORACLE_DOC $ORACLE_BASE/docFor ksh, add the following lines to the .kshrc file.export ORACLE_SID=acdbexport ORACLE_BASE=/oracle/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/8.1.7export PATH=${PATH}:$ORACLE_HOME/binexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport ORACLE_DOC=$ORACLE_BASE/docIn addition, to be able to use Java/JDBC and/or SQLJ, one needs further to check and set environ-ment variables PATH, CLASSPATH, and LD_LIBRARY_PATH.The PATH variable must include /usr/local/java/jdk1.1.6/usr/bin,which shouldcome before /usr/local/java/bin. This is because the current version of SQLJ is based onJDK1.1.The CLASSPATH variable must include$ORACLE_HOME/jdbc/lib/classes111.zip$ORACLE_HOME/jdbc/lib/nls_charset10.zip$ORACLE_HOME/sqlj/lib/translator.zipThe LD_LIBRARY_PATH variable must include $ORACLE_HOME/jdbc/lib.3 Oracle8i ManualsA set of manuals for ORACLE8i is available on–line at Oracle Technology Network Web site(http://otn.oracle.com/docs/products/oracle8i/content.html). To access to these manuals, you needto register with Oracle Technology Network first, and sign on with your OTN user name andpassword.The followingmanualsare especially helpful with respect to various aspects of the assignments.All these manuals are reachable through the “Oracle8i Documentation Library” and then “Oracle8iServer and SQL*PLUS” link or “Oracle8i Server Application Development” link.If you encounter SQL errors with error message starting ORA ######, you may want to findthe description of the error and its correction in “Oracle8i Error Messages”.If you have difficulties with PL/SQL, check out the manuals “PL/SQL User’s Guide andReference” and “Oracle8i Supplied Packages reference”.If you have problems with Oracle SQL syntax or want to verify if a certain SQL featureis implemented in Oracle8i, you definitely want to see the syntax charts in “Oracle8i SQLReference”.2Zhang Using ORACLE in the CSLab 19th September 2002With respect to using triggers, read “Oracle8i Application Developer’s Guide - Fundamen-tals”.The discussion of SQL in most database textbooks is based on the SQL standard. The syntax ofmany sample SQL queries in these textbooks may differ from that of ORACLE SQL. It is importantto consult Oracle SQL Reference manual for class assignments.4 Managing the ORACLE User AccountAs a user of the ORACLE database, one should keep the number of tables in one’s account as smallas possible. Temporary tables should be removed using SQL statement DROP TABLE as soon asit is no longer needed. Running many programs that access the database at the same time will slowdown the system significantly. So be sure to leave the ORACLE system in an ordinary way suchas by entering EXIT command.5 The Sample DatabaseA sample database has been created for the course. It contains the six relations of a companydatabase with the following tables: employee, department, dept_locations, de-pendent, works_on, project. These tables can be accessed using SQL statements. Thetable names must have the prefix sample.. For example to list all employees, enter the SQLstatementSELECT *FROM sample.employee;6 Using SQL*PLUSSQL*PLUS is an interactive user interface to ORACLE DBMS. It is a command interpreter aswell as a command line editor. You can enter SQL queries directly to SQL*PLUS and get answersfrom ORACLE databases.6.1 Starting SQL*PLUSTo start SQL*PLUS, enter the command$ sqlpluswhere $ is the UNIX system prompt. You will be asked to enter your ORACLE user’s name andpassword. Enter your user’s name in the following form: userName@cs. For example, for userScott, the user name is scott@cs. Notice that, your ORACLE user name must be followed by@cs in order to use sqlplus. After the correct information is entered, you will see a new promptSQL>on your screen indicating that you have successfully signed on to the database. This may take awhile depending on how many users are concurrently using the ORACLE


View Full Document

UTSA CS 3743 - Using Oracle in the CS Lab

Download Using Oracle in the CS Lab
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Using Oracle in the CS Lab and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Using Oracle in the CS Lab 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?