MassBot1: Created page with "=Getting Started With Oracle ----= * Overview * yurttas/PL/DBL/oracle/F/R/#logging in to oracle|Logging..."

2013-11-05T05:35:25Z

Created page with "=Getting Started With Oracle ----= * Overview * yurttas/PL/DBL/oracle/F/R/#logging in to oracle|Logging..."

New page

=Getting Started With Oracle ----=

* [[yurttas/PL/DBL/oracle/F/R/#overview|Overview]]
* [[yurttas/PL/DBL/oracle/F/R/#logging in to oracle|Logging In to Oracle]]
* [[yurttas/PL/DBL/oracle/F/R/#changing your password|Changing Your Password]]
* [[yurttas/PL/DBL/oracle/F/R/#creating a table|Creating a Table]]
* [[yurttas/PL/DBL/oracle/F/R/#inserting tuples|Inserting Tuples]]
* [[yurttas/PL/DBL/oracle/F/R/#getting the value of a relation|Getting the Value of a Relation]]
* [[yurttas/PL/DBL/oracle/F/R/#getting rid of your tables|Getting Rid of Your Tables]]
* [[yurttas/PL/DBL/oracle/F/R/#info about db|Getting Information About Your Database]]
* [[yurttas/PL/DBL/oracle/F/R/#quitting sqlplus|Quitting sqlplus]]
* [[yurttas/PL/DBL/oracle/F/R/#executing sql from a file|Executing SQL From a File]]
* [[yurttas/PL/DBL/oracle/F/R/#editing commands in the buffer|Editing Commands in the Buffer]]
* [[yurttas/PL/DBL/oracle/F/R/#recording your session|Recording Your Session]]
* [[yurttas/PL/DBL/oracle/F/R/#help facilities and other hints|Help Facilities and Other Hints]]

===---- Overview===

You will be using the Oracle database system to implement your PDA (Personal Database Application) this quarter. '''Important: '''''As soon as your Oracle account is set up, you should log in to change the initial password.''

===---- Logging In to Oracle===

You should be logged onto one of the Sun Solaris machines at L&IR. These machines include elaine, epic, saga, adelbert, tree, and cardinal.

Before using Oracle, execute the following line in your shell to set up the correct environment variables:

 source /usr/class/cs145/cora.env

You may wish to put this line in your shell initialization file instead (for example, <tt>.cshrc</tt>).

Now, you can log in to Oracle by typing:

 sqlplus <yourName>

Here, <tt>sqlplus</tt> is Oracle's generic SQL interface. <tt><yourName></tt> refers to your leland login.

You will be prompted for your password. This password is initially <tt>dbpasswd</tt> and ''must'' be changed as soon as possible. For security reasons, we suggest that you ''not'' use your regular leland password, because as we shall see there are opportunities for this password to become visible under certain circumstances. After you enter the correct password, you should receive the prompt

 SQL>

===---- Changing Your Password===

In response to the <tt>SQL></tt> prompt, type

 alter user <yourName> identified by <newPassword>;

where <tt><yourName></tt> is again your leland login, and <tt><newPassword></tt> is the password you would like to use in the future. This command, like all other SQL commands, should be terminated with a semicolon.

===---- Creating a Table===

In <tt>sqlplus</tt> we can execute any SQL command. One simple type of command creates a table (relation). The form is

 CREATE TABLE <tableName> (
 <list of attributes and their types>
 );

You may enter text on one line or on several lines. If your command runs over several lines, you will be prompted with line numbers until you type the semicolon that ends any command. ('''Warning''': An empty line terminates the command but does not execute it; see [[yurttas/PL/DBL/oracle/F/R/#editing commands in the buffer|Editing Commands in the Buffer]].) An example table-creation command is:

 create table test (
 i int,
 s char(10)
 );

Note that SQL is ''case insensitive'', so <tt>CREATE TABLE TEST</tt> and <tt>create table test</tt> are the same. This command creates a table named <tt>test</tt> with two attributes. The first, named <tt>i</tt>, is an integer, and the second, named <tt>s</tt>, is a character string of length (up to) 10.

===---- Inserting Tuples===

Having created a table, we can insert tuples into it. The simplest way to insert is with the <tt>INSERT</tt> command:

 INSERT INTO <tableName>
 VALUES( <list of values for attributes, in order> );

For instance, we can insert the tuple <tt>(10, 'foobar')</tt> into relation <tt>test</tt> by

 INSERT INTO test VALUES(10, 'foobar');

===---- Getting the Value of a Relation===

We can see the tuples in a relation with the command:

 SELECT *
 FROM <tableName>;

For instance, after the above create and insert statements, the command

 SELECT * FROM test;

produces the result

 I S
 ---------- ----------
 10 foobar

===---- Getting Rid of Your Tables===

To remove a table from your database, execute

 DROP TABLE <tableName>;

We suggest you execute

 DROP TABLE test;

after trying out this sequence of commands to avoid leaving a lot of garbage around that will be still there the next time you use the Oracle system.

===---- Getting Information About Your Database===

The system keeps information about your own database in certain system tables. The most important for now is <tt>USER_TABLES</tt>. You can recall the names of your tables by issuing the query:

 SELECT TABLE_NAME
 FROM USER_TABLES;

More information about your tables is available from <tt>USER_TABLES</tt>. To see all the attributes of <tt>USER_TABLES</tt>, try:

 SELECT *
 FROM USER_TABLES;

It is also possible to recall the attributes of a table once you know its name. Issue the command:

 DESCRIBE <tableName>;

to learn about the attributes of relation <tt><tableName></tt>.

===---- Quitting <tt>sqlplus</tt>===

To leave <tt>sqlplus</tt>, type

 quit;

in response to the <tt>SQL></tt> prompt.

===---- Executing SQL From a File===

Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed. There are two ways to do so.

The first is to provide your password and the name of the file in the command line with which you open <tt>sqlplus</tt>. The form of the command is:

 sqlplus <yourName>/<yourPassword> @<fileName>

For instance, if user <tt>sally</tt>, whose password is <tt>etaoinshrdlu</tt>, wishes to execute the file <tt>foo.sql</tt>, then she would say:

 sqlplus sally/etaoinshrdlu @foo

Notice that this mode presents a risk that <tt>sally</tt>'s password will be discovered, so it should be used carefully. Also note that since <tt>sally</tt> did not specify an extension for the file name "<tt>foo</tt>", <tt>sqlplus</tt> implicitly assumes the default extension "<tt>.sql</tt>". Therefore, it is a good idea to give every command file an extension, and specify the complete file name with the extension after the "<tt>@</tt>".

A second, safer way, is for <tt>sally</tt> to log in to <tt>sqlplus</tt> as usual. She then types, in response to the <tt>SQL></tt> prompt:

 @foo.sql

and the file <tt>foo.sql</tt>'s contents will be executed.

===---- Editing Commands in the Buffer===

If you end a command without a semicolon, but with an empty new line, the command goes into a buffer. You may execute the command in the buffer by either the command <tt>RUN</tt> or a single slash (/).

You may also edit the command in the buffer before you execute it. Here are some useful editing commands. They are shown in upper case but may be either upper or lower.

{| width="100%" border="BORDER"
| <tt>L</tt>
| lists the command buffer, and makes the last line in the buffer the "current" line
|-
| <tt>L</tt> ''n''
| prints line ''n'' of the command buffer, and makes line ''n ''the current line
|-
| <tt>L</tt> ''m n''
| prints lines ''m'' through ''n'', and makes line ''n'' the current line
|-
| <tt>I</tt>
| enters a mode that allows you to input text following the current line; you must terminate the sequence of new lines with a pair of "returns"
|-
| <tt>C /old/new</tt>
| replaces the text "<tt>old</tt>" by "<tt>new</tt>" in the current line
|-
| <tt>A text</tt>
| appends "<tt>text</tt>" to the end of the current line
|-
| <tt>DEL</tt>
| deletes the current line
|}===---- Recording Your Session===

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command <tt>script</tt> to record the terminal interaction. The <tt>script</tt> command records everything printed on your screen. The syntax for the command is

 script [ -a ] [ filename ]

The record is written to <tt>filename</tt>. If no file name is given, the record is saved in the file <tt>typescript</tt>. The <tt>-a </tt>option allows you to append the session record to <tt>filename</tt>, rather than overwrite it. To end the recording, type

 exit

For more information about <tt>script</tt>, check out its man page.

Alternatively, you can use the <tt>spool</tt> command within <tt>sqlplus</tt>. At the <tt>SQL></tt> prompt, you say:

 spool foo;

and a file called <tt>foo.lst</tt> will appear in your current directory and will hold everything typed, until you exit <tt>sqlplus</tt> or type:

 spool off;

Finally, if you use Emacs, you can simply run <tt>sqlplus</tt> in a shell buffer and save the buffer to a file. To prevent your Oracle password from being echoed in the Emacs buffer, add the following lines to your <tt>.emacs</tt> file:

(setq-default
comint-output-filter-functions
'(comint-watch-for-password-prompt))
(setq
comint-password-prompt-regexp
"\\(\\([Oo]ld \\|[Nn]ew \\|^\\)[Pp]assword\\|Enter password\\):\\s *\\'")

===---- Help Facilities and Other Hints===

In response to the <tt>SQL></tt> prompt, type <tt>help</tt> followed by a keyword. If you are lucky, the keyword will be one of those for which help exists, and you will get a (somewhat) helpful message, usually ending in an example or two. To see all the possible commands, for each of which help is available, type

 help commands;

The output from <tt>help</tt>, and in general, the results of many SQL commands, can be too long to display on a screen. You can use

 set pause on;

to activate the paging feature. When this feature is activated, output will pause at the end of each screen until you hit the "return" key. To turn this feature off, use

 set pause off;

----

This document was written originally for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998.

Yurttas/PL/DBL/oracle/F/R/or-intro.html - Revision history

MassBot1: Created page with "=Getting Started With Oracle ----= * Overview * yurttas/PL/DBL/oracle/F/R/#logging in to oracle|Logging..."