The following topics are covered:
Introduction
Oracle User Accounts - How to Log In - Very Important!!!
SQL Plus Basics

• Running SQL*Plus
• Getting Help in SQL*Plus
• Typing Commands and SQL Statements SQL*Plus
• Displaying Table Metadata
• Saving SQL*Plus Output using the SPOOL Command

Introduction

The Oracle 9i Developer Suite (9iDS) tool set is available (or will be shortly) in BCTC lab in the 25th street Library and Technology building (Ask for Section "M" computers to use Oracle9iDS) and in the CIS Department labs (11-115 and 11-125 in the Vertical Campus).

The following tools are currently supported:

The Oracle database is version 9.2.0 (Oracle 9i Release 2). This combination represents the current commercial state of the art in Client/Server applications development and RDBMS.

For more information on the Oracle product line, please visit the Oracle Corporation Web Site

SQL*Plus is a command line tool that facilitates direct manipulation of a database schema (tables, views, etc.). Any valid SQL statement such as SELECT, CREATE TABLE and others can be typed in and executed at the SQL*Plus prompt. Students in the database classes will require SQL*Plus to do some homework assignments and for projects. Details on how to save output from SQL*Plus and other hints can be found here.

A more detailed Tutorial on SQL*Plus is available (Warning, 70+ Pages long).

Developer 6i/9i is a suite of application development tools that includes a forms/menu painter, report generator and graphics designer. All versions of these tools run under Windows 98, NT, 2000 and XP Professional (NOT Windows ME or XP Home Edition). The client must have TCP/IP protocol support installed and be connected to a local area network at Baruch College.

A Tutorial on Developer/2000 and Developer 6i/9i (Oracle Forms, Reports and Graphics) is available. (Warning, lots of graphics).

Designer/2000 is an integrated Computer Aided Software Engineering (CASE) tool that includes support for Functional, Data Flow and Entity Relationship modeling, physical database modeling, and applications generation. The CASE model repositories reside on the database server allowing for multi-user development.

Designer/2000 is not generally available to students at this time.

Programmer/2000 is a collection of code libraries and interfaces used in conjunction with third party development tools (such as Microsoft Visual Basic or Visual C++) to create applications that can access Oracle databases.

Back to Table of Contents

Oracle User Accounts and Changing your Password

Every student will require an Oracle account in order to work. In Oracle, an account is equivalent to a schema. that is, each user is assigned some space in the Oracle database in which they can create a schema.

Oracle accounts also have passwords associated with them. New users must change their default Oracle passwords.

Running SQL*Plus

To run the SQL*Plus command line program from Windows 95, click on the button, Programs, Oracle @ Baruch and then SQL*Plus. The SQL*Plus login screen will appear after roughly 15 seconds.

In the User Name: field, type in your Oracle username.
Press the TAB key to move to the next field.
In the Password: field, type your Oracle password.
Press the TAB key to move to the next field.
In the Host String: field, type in the name of the Host String given to you by your instructor.
Finally, click on the OK button to log in to Oracle.

You will then be logged into Oracle and you will see the SQL*Plus SQL> prompt.

If you mistype your username, password or Host string and are not logged in to Oracle, please exit the SQL*Plus program and run it again.

Changing Your Oracle Password

To change an Oracle password, users should run the SQL*Plus program (as above) and the issue the password command. Below is an example of the password command in action. Note that the actual passwords will appear as asterisks:

SQL> password
Changing password for HOLOWCZAK
Old password: ***********
New password: **************
Retype new password: *************
Password changed

Your Oracle password should begin with some letters but may include numbers. Punctuation is not allowed in the Oracle password. Use all lower case letters - do not mix upper and lower case letters.

If the password has been changed, you will receive the message:

Password changed

Please note: You May Not share your Oracle account with anyone. All logins are monitored and audited. If an Oracle account is being shared, the account will be shut down with no opportunity for appeal.

Back to Table of Contents

Help for SQL*Plus

Typing Commands and SQL Statements SQL*Plus

To type an SQL statement such as a SELECT, CREATE TABLE, INSERT, etc. simply type the statement and end it with a semicolon. SQL statements can be longer than one line. Just press enter or carriage return at a convenient place and continue the statement. SQL*Plus will not submit the statement until you type the semicolon and press Enter or carriage return. The statement:

SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE WHERE LAST_NAME = 'Smith' ;

SELECT LAST_NAME,
FIRST_NAME
FROM EMPLOYEE 
WHERE LAST_NAME =
'Smith' ;

In Oracle, character and date data are enclosed in single quotes (not double quotes as with Sybase or in the "C" programming language). Also, table names (e.g. EMPLOYEE) and reserved words such as the various SQL statements (e.g. SELECT, INSERT, CREATE, etc.) are not case sensitive so the following SELECT statement does the same thing as the two previous examples:

SELECT Last_Name, First_name
from EmPloyEE
WHeRE LAST_NAME = 'Smith'
;

Given the choice, I prefer to type all reserved words (SELECT FROM CREATE DROP, etc.) in all capital letters while typing any of my table names, columns, etc. in all lowercase. Some people find this irritating...

SELECT last_name, first_name
FROM   employee
WHERE  last_name = 'Smith'
;

• ALTER - Change an existing table definition
• CREATE - Create new database objects such as tables or views
• DELETE - Delete rows from a database table
• DROP - Drop a database object such as a table or a view
• INSERT - Insert new data into a database table
• SELECT - Retrieve data from a database table
• UPDATE - Change the values of some data items in a database table

Note that when creating tables, column names must begin with a letter and may not contain spaces. Use the underscore character _ to separate words in your column names as in the above examples.

Displaying Table Metadata (Data about the data)

The column definitions for a table can be displayed using the DESCRIBE command in SQL*Plus:

Running SQL Statements Using the START Command

Typing SQL statements can get very tedious, especially when trying to INSERT data into a table. SQL*Plus has a feature that enables SQL statements saved in a file to be executed. This Command File or Script as it is sometimes called, should be a simple text file created with a Text editor such as Windows NotePad or the DOS EDIT program. (An alternative is to use a word processor like MS Word and then use the "Save As" feature to save the file as type "MS DOS Text with line Breaks").

When saving a script file from Notepad, be aware that Notepad liks to add a .TXT extension to the file name. Thus your file "create.sql" will actually be saved as "create.sql.txt". You should rename your file to only include the .sql extension.

Since most students will come to a computer lab with their files on a floppy disk, these examples assume the files are stored on the A: drive (By the way, if you are stymied by things like the A: drive or using a text editor, please take one of the introductory courses such as CIS 8000 or CIS 1000).

Assume the following file has been created using the Windows NotePad editor (found inthe Accesories group) and saved under the file name A:\create_emp.sql:

CREATE TABLE EMPLOYEE (
       EMPLOYEE_ID    NUMBER(10,0),
       LAST_NAME      VARCHAR(30),
       FIRST_NAME     VARCHAR(30),
       SALARY         NUMBER(10,2) );

INSERT INTO EMPLOYEE VALUES (1, 'Smith', 'Harry', 37000.00);
INSERT INTO EMPLOYEE VALUES (2, 'Jones', 'Mary', 39000.00);
INSERT INTO EMPLOYEE VALUES (3, 'Green', 'Bill', 22000.00);
INSERT INTO EMPLOYEE VALUES (4, 'Brown', 'Sally', 62000.00);

Note that each SQL statement ends with a semicolon. To have these commands executed under SQL*Plus, run the SQL*Plus program and then type START A:\create_emp.sql as follows:

For most projects, it is convenient to make several files for each type of SQL statement. For example, if my project is on maps and geographic data, I might maintain the following SQL files:

• create_maps.sql - To create all of the tables
• insert_maps.sql - To insert some sample data into each table
• drop_maps.sql - To drop all of the tables

Since each file is a simple text file, it is then very easy to move around via e-mail or FTP, etc. for backup purposes or to collaborate with others.

The EDIT command saves the current SQL command in the buffer to a file and then opens up the Notepad editor on that file. In order to make use of the EDIT command, you must change the name of the default edit file to work off of your floppy disk. Before using the EDIT command, type the following:

Saving SQL*Plus Output using the SPOOL Command

SQL*Plus has a command called SPOOL that can send the output from any SQL statement to a file. Indeed, anything that is displayed in SQL*Plus can be echoed to this spool file. You will need to SPOOL your output to a file in order to save your homework solutions.

Here's how to do it. First, run SQL*Plus then use the SPOOL command as follows:

The SPOOL OFF command turns the output off. Everything between SPOOL a:\myfile.out and SPOOL OFF will be in the file myfile.out. This is a simple ASCII text file that can be read by Windows Notepad, MS Word, or just about any word processor, e-mail package, etc. To print, load this file into MS Word, set the font to Courier and print as you would with any other document.

Note that some SQL*Plus commands will not show up in the SPOOL file. To have them echo to the SPOOL file, use the SET ECHO ON option.

Also, when SPOOLing to a file, SQL*Plus makes each line 80 characters long by padding with spaces. You can shorten this to fewer characters using the SET LINESIZE option. For example, SET LINESIZE 70 will pad each line of output to 70 characters.

Look at the SET TRIMOUT and SET TABS options for more ways around this.

An SQL*Plus Tutorial is available.

Back to Table of Contents

Common Error Messages

Error message	Cause	Recommendation
ORA-01017: invalid username/password; logon denied	You supplied an invalid username and/or password	Exit SQL*Plus and log in again with the correct username and password
ORA-12154: TNS:could not resolve service name	You mistyped the Host String or Connect String. Or, you have not yet configured SQL*Net or Net8.	If you are working in a lab at Baruch, exit SQL*Plus and log in again supplying the correct Host String. If this error still occurs in the BCTC lab or other Baruch computer lab: 1) Write down the machine number and lab you are in and the date and time - Report this to BCTC and Prof. Holowczak 2) Try moving to another machine. If you are in the BCTC lab, try moving to the other side of the lab. If you are working from home or office, make sure you have configured the appropriate SQL*Net or Net8 middleware.
ORA-03121: no interface driver connected - function not performed	The Host String was left blank. Or, if you did not exit SQL*Plus completely, you were not prompted for a Host String.	Exit SQL*Plus completely and supply the appropriate host string when you re-run SQL*Plus.
ORA-12203: TNS:unable to connect to destination	A network error is preventing the connection	Make sure you have a valid connection to the Internet.
ORA-12705: Invalid or Unknown NLS parameter value specified	Likely the Windows registry settings for Oracle have been erased.	If this happens on your computer at home or office, re-install Oracle. If this error occurs in the BCTC lab or other Baruch computer lab: 1) Write down the machine number and lab you are in and the date and time - Report this to BCTC and Prof. Holowczak 2) Try moving to another machine. If you are in the BCTC lab, try moving to the other side of the lab.

Other Related Links

The following links point to sites related to Oracle and to client / server computing. If you have any additions you think would be suitable, please let me know.

Oracle Related WWW Sites

Client / Server Related WWW Sites

Other Interesting Sites

Back to Table of Contents

All material Copyright 1997-2004 R. Holowczak