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.

  1. Each computer account can be used to log into both bulldog and ub servers
  2. The assigned user id and password are the same on both servers. However, you need to change the password manually for each account.
  3. You must log into bulldog to work with ORACLE.
  4. From a PC you can telnet to bulldog server and then work with ORACLE.
  5. 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:

  1. After you logged into bullldog, at the UNIX prompt, enter the following information into your .personal file:
  2. 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)

  3. Invoke ORACLE SQLPLUS software
  4. Type sqlplus at the prompt

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

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

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

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

select *
from system.tab;

SQL>describe <relation name>

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.

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

myquery.sql

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>

SQL>start myquery.sql

SQL>edit <your SQL file name>

 

 

 

SQL>edit myquery.sql

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

 

SQL>save <file name>

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

SQL>spool <your file name>

SQL>spool myoutput.out

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

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.

SQL>spool myoutput.out

<database queries>

SQL>spool off
SQL>exit

Bulldog5.d.umn.edu1% lpr myoutput.out



Special Characters

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

 

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

	SQL> column <column_name>  heading <column_heading>;
	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.

	SQL> column Emp# heading 'Employee|Number' ;
	SQL> set underline =

set the underline character to the equal character.

column <attribute> format <$format>

   SQL> column salary format $999,999

column <attribute> format <Aformat>.

	
	 SQL> column name format A8

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

break on <attribute> skip <n>.

 

 

 

 

 

	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

ttittle <position> <title> <position>.

	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

. . .

	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

SQL> set pagesize <number_of_lines>

    • Example:
To set the page size to 66 lines enter the following command.

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