Access ORACLE DBMS

A SIMPLE GUIDE TO ORACLE SQLPLUS

Access ORACLE DBMS

This document provides a simple guide for the access of ORACLE DBMS by using the SQLPLUS software. ORACLE can be accessed from either UNIX or DOS (PC).

UNIX Login Procedures

You are assigned a computer account in this class.

Each computer account can be used to log into both bulldog and ub servers

The assigned user id and password are the same on both servers. However, you need to change the password manually for each account.

You must log into bulldog to work with ORACLE.

From a PC you can telnet to bulldog server and then work with ORACLE.

The first time you log in to the account, you must use the default password. Once you logged in, you can change the password using the command: passwd.

ORACLE Login Procedures

ORACLE can only be accessed in your bulldog account. When you log into the account the first time, you need to set up the ORACLE working environment and change its password following the steps:

After you logged into bullldog, at the UNIX prompt, enter the following information into your .personal file:

setenv ORACLE_BASE /u01/app/oracle

setenv ORACLE_HOME /u01/app/oracle/product/8.1.7

setenv ORACLE_PATH /u01/app/oracle/product/8.1.7/bin

setenv ORACLE_SID oracle

set path =($path $ORACLE_PATH)

Invoke ORACLE SQLPLUS software

Type sqlplus at the prompt

When it asks for password, enter the default password, not the password you changed after you logged in.

Once you logged into ORACLE, you can change the ORACLE password using the command:

grant connect to <user id> identified by <password>;

Example : grant connect to cis6039 identified by fool;

The semi-colon at the end of the command is required.

Exit From SQLPLUS

Type Exit while inside SQLPLUS followed by the Enter key to exit.

Protect Your Work from Mistakes

You should execute the commit command before you change your database. When you are satisfied with your work, you should save it in the database permanently by executing the commit command, or you can discard all changes you made since the last commit command by typing the rollback command.

Save all changes in the database:

SQL>commit

Discard all changes made since the last commit:

SQL>rollback

Other Useful SQL Commands

Check relations you created in the database:

select * from system.tab;

Check the definition of a relation you created:

SQL>describe <relation name>

Example : SQL>describe student

If you forget a specific SQL command you could enter

SQL>help <the SQL command>

You can also find out all commands by entering:

SQL>help menu

Prepare SQL Statements as Text Files

You should create a file of SQL commands, such as creation of relations and queries, and execute the SQL commands in the file when you are in SQLPLUS.

Example:

spool myoutput.lst

create table myrelation
(mykey char(4), name char(30), address char(50));

insert into myrelation values
('1020', 'John Smit', '1 Main Street');

insert into myrelation values
('1030', 'Linda Jade', '1 Main Street');

insert into myrelation values
('1040', 'Tom Dube', '1 Main Street');

select *
from myrelation;

drop table myrelation;

spool out

Save the queries in a file. If you use a word processor, such as WordPerfect or Word for Windows, make sure to save the file in text format. Assume the above example is saved as:

myquery.sql

The next task is to transfer the query file to your UNIX (bulldog) account so that you can execute the queries using a command like ftp. You can also create text file directly on bulldog using an UNIX editor like pico.

Once the file is transferred into UNIX, you can run the SQL statements after you log into SQLPLUS. Assume that you name the SQL file myquery.sql.

SQL>start <your sql file name>

Example :

SQL>start myquery.sql

If there are errors in your SQL command file, ORACLE will display the error and discard that command. It tries to execute the following SQL command if possible. If situations like this happen, you may want to clear the database to discard the changes you made. If you execute commit before running this file, the rollback command can restore the database for you. You then should go back and edit your file to fix the errors. The screen editor of DOS can be invoked by entering:

SQL>edit <your SQL file name>

Example :

SQL>edit myquery.sql

After the errors are fixed, you may repeat the above steps again.

For modularity, you should organize your queries into different files. For example, let one file contain all SQL commands for creating relations, one file for removing all relations, one file for inserting tuples into all relations, one file for retrieving results from the relations, and one file for delete all tuples. In such a way, you could easily test different types of queries.

You can save or append the SQL query you type into a text file. This command is very useful to record SQL statements as you test them one by one.

SQL>save <file name>

Example :

You last query:

SQL>select * from student;

To save it into a file:
SQL> save working.sql;

To append a tested query into the same file:

SQL>select * from class;

SQL>save working.sql app;

After first save operation, file working.sql will be created and will contain the query

"select * from student". After the second save operation the query "select * from class" will get appended to the file working.sql.

As the file, working.sql, is kept in the UNIX account, you should down load it to your PC from the UNIX account and then print it from your printer or incorporate it into your document.

Query Results Output

Record your database activities in a file for output or editing purpose.

SQL>spool <your file name>

Example:

SQL>spool myoutput.out

All database activities after this command are written into this file.

End recording database activities and print them out using the following commands:

SQL>spool off (stop spooling)

Once you have the output file, you can print out the file use the lpr command at the UNIX prompt.

Example (in PC DOS):

SQL>spool myoutput.out

<database queries>

SQL>spool off SQL>exitBulldog5.d.umn.edu1% lpr myoutput.out The difference between a save and a spool operation is that a save operation will save only the query whereas a spool operation will save the query along with the result of the query.

Special Characters

If your data contains special punctuation characters, you may encounter difficulties in ORACLE. These are the solutions:

To obtain a single quote character ('), use two single quotes, i.e., '', to get one single quote character. For example, you would enter 'Smith''s Home' in SQL for the string "Smith's Home".

The '&' character is used by SQLPLUS as substitution character for data input. To use an '&' character as part of your string value, you need to turn on the escape character using the following command:

SQL>set escape \

If you want enter a string containing &, you would use '\' to escape the '&' character. For example, to enter the string, "Johnson & Son Co.", as 'Johnson \& Son Co.'

Simple Formatting Query Results

Attribute headings are by default attribute names. The column command defines new attribute headings.

	SQL> column <column_name>  heading <column_heading>;

Example:

	SQL> column desc heading description ;

To display a column heading on more than one line, use a vertical bar (|) where you want to begin a new line.

Two line heading example:

	SQL> column Emp# heading 'Employee|Number' ;

To change the character used to underline headings, use the set underline command.

Example :

	SQL> set underline =

set the underline character to the equal character.

To format a number attribute to a dollar format, use the

column <attribute> format <$format>

Example :

   SQL> column salary format $999,999

To indicate the displayed width of a character string attribute, use the

column <attribute> format <Aformat>.

Example : set the width of the name attribute to 8 characters.

	
	 SQL> column name format A8

If a name is longer than 8 characters, the remaining is displayed at the second line (or several lines)

To avoid repeating the leading attribute of a sorted output, use the

break on <attribute> skip <n>.

Example :

	SQL> select name, item#, amount
	  2  from inventory
	  3  order by name;

NAME ITE AMOUNT

--------- ----- -------------

Box i4 12.93

Box i8 98.23

Phone i3 54.23

Table i2 23.12

Table i6 54.98

Chair i1 23.84

Chair i5 43.98

Chair i9 23.12

Chair i7 23.45

SQL> break on name skip 1 SQL> select name, item#, amount 2 from inventory 3 order by name;

NAME ITE AMOUNT

--------- ----- --------------

Box i4 12.93

i8 98.23

Phone i3 54.23

Table i2 23.12

i6 54.98

Chair i1 23.84

i5 43.98

i9 23.12

i7 23.45

To add titles to the query result, use the command:

ttittle <position> <title> <position>.

Example: one line title

	SQL> ttitle center 'Company Sale Report' skip 1
	SQL> select name, item#, amount
	  2  from inventory
	  3  order by name;

Company Sale Report

NAME ITE AMOUNT

---------- ----- --------------

Box i4 12.93

i8 98.23

. . .

Example: multiple line title

	SQL> ttitle center 'Company Sale Report' skip 1 -
	> center ================================ skip 1 -
	> left 'Personal Report' right 'Sales Department' skip 2

SQL> select name, item#, amount 2 from inventory 3 order by name;

Company Sale Report

===========================

Personal Report Sales Department

NAME ITE AMOUNT

---------- ----- --------------

Box i4 12.93

i8 98.23

The set command can be used to change the default number of lines per page (14) and the number of characters per line (80). To set the number of lines on a page , use the pagesize variable of the set command.

SQL> set pagesize <number_of_lines>Example: To set the page size to 66 lines enter the following command. SQL> set pagesize 66

All changes are active until they are cleared or reset.

	SQL> clear break
 	SQL> clear column
 	SQL> ttitle off   (may be turn on by using the 'on'switch)