Help on Oracle

Oracle 8 documentation

A Simple Guide to ORACLE 7 SQLPLUS

Topics

• Access ORACLE DBMS on GL
• Login Procedure for UNIX account on GL
• Access ORACLE DBMS on CS
• Login Procedure for UNIX account on CS
• Login Procedure for ORACLE DBMS
• Invoking SQLPLUS
• Exit From SQLPLUS
• Documentation and help
• Protect Your Work from Mistakes
• Other Useful SQL Commands
• Prepare SQL Statements as Text Files
• Query Results Output
• Simple Formatting Query Results

Access ORACLE DBMS on GL

ORACLE Login Procedures

To run the interactive sql client, sqlplus, you will first have to execute the commands in /opt/bin/oracle_cshrc. So, you could do:

   source /opt/bin/oracle_cshrc
   sqlplus

Access ORACLE DBMS on CS

ORACLE Login Procedures

To run the interactive sql client, sqlplus, you will first have to execute the commands in /opt/bin/oracle_cshrc. So, you could do:

   source /opt/bin/oracle_cshrc
   sqlplus

Invoking SQLPLUS

  dbs1[2]%   sqlplus

  SQL*Plus: Release 3.3.2.0.0 - Production on Thu Nov  7 15:23:29 1996
  Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
  
  Enter user-name: finin
  Enter password: 
  
  Connected to:  Oracle7 Server Release 7.3.2.1.0 - Production Release
  With IRIX Enhancements, PL/SQL Release 2.3.2.0.0 - Production
  
  SQL> help;
                                      HELP
  
   HELP [topic] | help
  
   HELP displays information on the commands and conventions of SQL*Plus,
   SQL, and PL/SQL.  Type "help", a space, all or part of any topic, and
   then press Enter.
  
   Typing only part of a topic (for example, HELP SE) will cause all help
   topics that match the string (SE) to be displayed on your screen in
   alphabetical order (for example, HELP SE will retrieve help on the
   SELECT statement, the SET command, and the Set Operators--in that order).
  
       ENTER THIS...     TO DISPLAY THIS...
       --------------------------------------------------------------
       help commands     a list of SQL*Plus, SQL, and PL/SQL commands
       help comm         information on commands, comments, and commit
       help help         this screen
  
  SQL> exit;
  Disconnected from Oracle7 Server Release 7.3.2.1.0 - Production Release
  With IRIX Enhancements
  PL/SQL Release 2.3.2.0.0 - Production
  dbs1[3]% 

You can change your ORACLE password using the SQL grant command:

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

For example, grant connect to finin identified by ragnarok;

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

Exit From SQLPLUS

Documentation and help

Protect Your Work from Mistakes

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>
  SQL> describe student

• If you forget a specific SQL command you could enter
  SQL> help <the SQL command>
  You could also find out all commands by entering:
  SQL> help menu

Prepare SQL Statements as Text Files

• Use your favorite editor to type in your SQL queries.

  EXAMPLE:

  cat 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 into 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

  Once the file is transformed into UNIX, you can run the SQL statements after you log into SQLPLUSi. Assume that you name the SQL file as 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 to edit your file and fix all errors. The screen editor of DOS can be invoked by enter:
  SQL> edit <your SQL file name>

  EXAMPLE:
  SQL> edit myquery.sql

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

• To be useful, 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 queries. You should avoid to putting all SQL commands into one "monster" file. It would be very difficult to work with.

• You can save or append the SQL query you type into a text file. This command is very useful for you to record SQL statements after you test them one by one.
  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;

Query Results Output

• Record your database activities into 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 out them by using the following commands:
  SQL> spool off (stop spooling)
  SQL> spool out (stop spooling & send the file directly to a printer)
  

  Once you have the output file, you could print out the file use the lpr command of the computer system after you exist from SQLPLUS.

Simple Formatting Query Results

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

   SQL> column  heading ;
  

  Example:

   SQL> column desc heading description
  

  Two line heading example:

   SQL> column Emp# heading 'Employee|Number'
  

• To change the character used to underline headings, use the set underline command. For 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 <9'format>:

    	SQL> column salary format $999,999
  

• To indicate the displayed width of a character string attribute, use the column <attribute> format <A'format>. For 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 repeat the leading attribute of a sorted output, use the break on <attribute> skip <n>. For example, the following query is:

   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).

   SQL> ttitle center 'Company Sale Report' skip 1 -
   	SQL> set pagesize 60
  

• All formatting remain 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)