This Guide will introduce you to using Oracle for several basic tasks, as well as how to configure the Oracle environment and use special features of its user interface. This guide is divided into the following sections:
In order to interact with Oracle you should use the SQL*Plus Application. This application is available on both the windows and Unix platforms.
You will be prompted for your username, connect string, and password. If you are using Oracle on windows with the CD accompanying the text, then you may leave the connect string blank. On Unix the connect string will follow your username and the @ symbol in the format username@connectstring.
Note: If you have installed Oracle on Windows, you may use it to connect to your Unix based server. In order to do this copy the entries from the tnsnames.ora in the $ORACLE_HOME/network/admin directory on the Unix system to the tnsnames.ora file on the Windows machine. Then, use the connect string as you would in Unix. There is also a version of SQL*Plus on windows that works in command line mode, which is identical to the Unix version.
Oracle data is stored in tables which represent your fundamental schema design. You create them with the create table command:
create table tablename ( columnname type, columnname type ...);
where tablename is the name of your table, columnname is the name of your column and type is the type as specified in the table below. There are additional types available, however these are the most common. Give some thought as to the order of columns as it can effect the default order they are displayed and data is entered.
The easiest way to get information about a table is with the DESCRIBE command:
To alter a table use the alter table command:
alter table tablename add( columnname datatype, ...); alter table tablename modify ( column newdatatype); alter table drop column columnname;
To delete a table use the following:
drop table tablename;
To Drop all your tables you can use the following in a script:
SET NEWPAGE 0
SQL> create table sign_sales(Color varchar2(30),date_sold date, 2 price_each number); Table created.
SQL> alter table students add( sign_shape number); Table altered. SQL> alter table students modify (sign_shape varchar2(10)); Table altered. SQL> alter table students drop column sign_shape ; Table altered.
SQL> DESCRIBE sign_shape
Name Null? Type ----------------------------------------- -------- ---------------------------- COLOR VARCHAR2(30) DATE_SOLD DATE PRICE_EACH NUMBER
SQL> drop table sign_sale; Table dropped.
Working with data is fundamental to database systems. It is possible to load data with SQL. It can also be done using the Oracle bulk loader. To load values in SQL*Plus use the following syntax:
Insert into tablename values (somevalue, somevalue, ...);
where somevalue is a value to insert and tablename is the name of the table. Values are inserted in the order of the columns of the table. The first value is inserted into the first column, etc.
Querying data in SQL is one of a database systems major tasks, and can not be fully described here. In its most basic form a query consists of the SELECT statement:
select columnname, columnname... from tablename;
Where columnname is the name of a column or * for all columns and tablename is the name of the table.
You can alter data with the update expression. The optional where clause limits the rows effected. The set clause describes the change as follows:
update tablename set columnname=expression where whereclause;
You can delete data with the delete expression. The optional where clause limits the rows effected.
delete from tablename where whereclause;
SQL> insert into signs values(19.95,'White','Rectangle','Park Somewhere Else'); 1 row created. SQL> SQL> select * from signs; PRICE_EACH COLOR SHAPE DESCRIPTION ---------- --------- ------------ ------------------- 19.95 White Rectangle Park Somewhere Else SQL> SQL> update signs set price_each = 4.00 where price_each < 20; 1 row updated. SQL> SQL> delete from signs ; 1 row deleted. SQL>
In order to finish realizing a schema in Oracle, it is necessary to add the required constraints. Oracle supports several types of constraints, to enforce complex rules may require the use of active database features such as triggers. In this section the examples are limited to key constraints.
Constraints can be added to new tables or after table creation. To add primary key constraints to a new table specify the primary key after the columns similar to the following:
create table tablename ( columnname type, columnname type ..., primary key(keycolumn,keycolumn,...);
where keycolumn is the name of a column that is part of the key.
Foreign keys should refer to unique tuples. To create a new table with foreign keys in addition use:
create table tablename ( columnname type, columnname type ..., primary key(keycolumn,keycolumn,...), foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable, foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable,...);
where foreignkeycolumn is the column of the foreign key in table foreigntable. It must match a column in the current table. You can also add constraints with the alter table command where tablename is the name of the table and tableconstraint is the constraint definition:
Alter table tablename add tableconstraint;
Often you may wish to see what constraints exist on a table. The view USER_CONSTRAINTS is maintained by Oracle and has information about constraints. Some interesting columns include, the CONSTRAINT_NAME, CONSTRAINT_TYPE where P refers to primary key, and R is a foreign key constraints, the TABLE_NAME on which it is defined, and the R_CONSTRAINT_NAME for foreign key constraints. Type DESCRIBE USER_CONSTRAINTS for more informaion.
Once you know the CONSTRAINT_NAME, you can use the USER_CONS_COLUMNS view to find the COLUMN_NAME and POSITION for that CONSTRAINT_NAME. You can query these views as you would any other table:
select column_name, position, constraint_name from User_cons_columns;
At times you may need to create constraints that are interdependent, such as two tables that refer to each others primary key in a foreign key constraint. One way to deal with this problem is to defer constraints. They constraint must be made deferrable and the constraints mode set to deferred. You can substitute the names of specific constraints or use the all keyword. They will be checked at the end of the transaction instead of after each insert. To enable them make the mode immediate:
create table tablename ( columnname type, columnname type ..., foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable deferrable);
set constraints all deferred;
set constraints all immediate;
Constraints can be deleted with the drop constraint command. where tablename is the table name and someconstraint is the constraint name:
alter table tablename drop constraint someconstraint;
SQL> create table bids( bid_id varchar2(10), bidder_id varchar2(10), item_id varchar2(10), 2 bid_amount number, primary key(bid_id), foreign key ( item_id ) references 3 auction_items, foreign key (bidder_id) references members(member_id) deferrable); Table created. SQL> SQL> select constraint_name, constraint_type from user_constraints where 2 table_name='BIDS'; CONSTRAINT_NAME C ------------------------------ - SYS_C001400 P SYS_C001401 R SYS_C001401 R SQL> SQL> alter table students drop constraint SYS_C001400; Table altered. SQL> alter table students add primary key( bid_id ); Table altered. SQL> set constraints all deferred; Constraint set.
SQL*Plus provides an interactive command line interface to Oracle. In it you
type various commands to manipulate your data. It has many built in features.
To end your session type:
In the windows version you can select Exit from the file menu.
Type passw to change your password. Try to avoid certain characters like @ and / in your password, as these will confuse oracle.
Commands in Oracle are stored in a buffer until ended with a semicolon ; and are also accessible immediately after execution. They may use several lines. If you make a mistake it is easier to edit your command then type it all over. You can use the edit command to edit a file, or select Edit >Editor > Invoke Editor in Windows.
This will edit the command in the buffer. You may also specify a file as a parameter. By default the editors are vi in Unix and notepad on Windows. You may change the editor where editorname is you editor by using the Define Editor menu in Windows or:
define _editor = editorname
You may execute the command in the buffer by either the command RUN or a single slash (/). You can use GET and SAV to retrieve or save the buffer to a file. You can also change a single line with C to replace the old text with the new.
Whenever you have finished a series of changes and you want to insure they are part of the database you should commit the transaction. This will make the changes visible to other database sessions. type:
If you make a mistake you can undo the change with:
It will restore data to the last commit. Some commands automatically result in commit, such as creating a table.
SQL*Plus can record your commands and output to a file. This is useful for class assignments that ask for a log of your session, or in automated self generating scripts. To do this type:
where filename is the name of the file to save the output. In windows you can use the File > Spool >Spool File Command. To stop recording to a file select File >Spool> Spool Off or type
Sometimes you may need to use a special character such as & or ; in a query that is used by SQL*Plus. You can define an escape character. You may then use it in queries before a special character. It is often useful in automated scripting. To escape the quote character use multiple quotes
set escape \
select 'It''s mine\; I like it' from dual;
You may also turn escape on and off.
set escape off
If you prepared commands in advance, for example by developing them at home to load onto another system type:
where filename.sql contains the commands to run. The File > Open menu can also be used.
There is a login file that you may create in the directory from which you call sqlplus. Put the commands in the file called login.sql This is especially useful for common tasks such as setting the editor.
Oracle provides several options in how both input and output are formatted. One consistent formatting parameter that you may need to change is the date formatting. The ALTER SESSION command allows you to change the date format. use the SET NLS_DATE_FORMAT command followed by the date format string. The default format is DD-MON-YY. Some common Code include:
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI'
You can also change the output formatting in SQL *Plus. This can disable any of the headers normally included with output or change there size and location. This is also helpful when exporting output. For example, the code below disables all heading informaion.
SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF
SQL*Plus has an on line help system. To use it type:
where command is the command you need help with. To obtain a list of commands with help available type:
SQL> spool answer.txt SQL> DEFINE _EDITOR= pico SQL> spool off SQL> commit; Commit complete. SQL> select username from junk; select username from junk * ERROR at line 1: ORA-00942: table or view does not exist
SQL> c /junk/user_users 1* select username from user_users SQL> / USERNAME ------------------------------ STUDENTNAME SQL> quit Disconnected from Personal Oracle9i Release 184.108.40.206.1 - Production With the Partitioning option JServer Release 220.127.116.11.1 - Production student|computer-one|~|%
Version 0.1.0.1 Last Updated: 2002/06/08 by