Oracle FAQ

For more information on using Oracle, see this guide from the authors of the textbook.


How do I set up Oracle on my gradient or eniac account?

Bourne-Style Shells

If you are using bash, open a shell on eniac and add this line to your .profile:

export ORACLE_HOME=/usr/local/oracle
export PATH=${PATH}:${ORACLE_HOME}/bin
alias sql='sqlplus yourusername@cisora'

(Substitute your username for yourusername above.)

C-Style Shells

If you are using a variant of the C shell (csh or tcsh), then open a shell on eniac and add these lines to your ~/.cshrc file:

setenv ORACLE_HOME /usr/local/oracle
set path=$path:$ORACLE_HOME/bin
alias sql 'sqlplus yourusername@cisora'

(Substitute your username for yourusername above.)

Executing Oracle

Now source ~/.cshrc (or .profile) and run Oracle using the command sql. Note that you must have created an Oracle account already.

Next time you log in, you can run Oracle and access the database by just typing "sql".

> sql
SQL*Plus: Release 3.1.3.5.1 - Production on Fri Sep  5 17:22:38 1997
Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
 
Connected to:
Oracle7 Server Release 7.1.4.1.0 - Production Release
PL/SQL Release 2.1.4.0.0 - Production
SQL> select * from icu.unit where psn=1205;
       PSN     APACHE U_ADM     DIAGNOSIS                      U_DSG     U_DECEASE
---------- ---------- --------- ------------------------------ --------- ---------
      1205         26 24-OCT-94                                25-OCT-94
 

What is SQL*Plus?

SQL*Plus is the Oracle command line utility. You can access it on gradient or eniac by typing "sql" (provided you have set it up correctly)

My query does not work, SQL*Plus is saying something like "2  ".
You are missing the final semicolon.

How do I get help in SQL*Plus?
SQL*Plus has a nice help system. Just type "help" or "help <command>" for help on a specific command.

How do I run a script in SQL*Plus?
A script with SQL or SQL*Plus commands in it should have the extension ".sql". E.g. your script is called "foo.sql" to run it type "start foo" or "start foo.sql".

How do I get a transcript of my Oracle session?
In the beginning say "spool <your filename>". To stop spooling type "spool off". For more see "help spool".

What is all this about the Oracle user name and the password?
Oracle lets you have separate password. When run on gradient or eniac, Oracle takes a look at your user ID to identify you. Nevertheless you can have different password for log ins from a remote computer.

What is my oracle user name?
Your Oracle user name is your eniac or gradient user name preceded by "ops$". E.g. your eniac user name is "bar". This makes your Oracle user name "ops$bar".

How do I set my Oracle password?
To set your oracle password you need to know your user name. See question above. For this we assume an Oracle user name of  "ops$bar" and you want to set your password to "xyz12". To do this call up SQL*Plus on gradient or eniac:

SQL> alter user ops$bar identified by password_of_your_choice;

Does the Oracle password have to be different from my eniac/gradient password?
It doesn't but it SHOULD be since the Oracle password is easily exposed in PHP pages etc.

Where can I find more Oracle documentation?
Go to http://technet.oracle.com/.

I just created this new table/row/view ... Why doesn't it show up in my other Oracle SQL*Plus/program/ ... ?
You need to say "Commit;".

Why is my JDBC or SQL*Plus hanging?
You need to add "setenv EPC_DISABLED TRUE" to your Oracle startup script or profile. This is a problem in the server.

How can I see all my tables?

SQL> select table_name from tabs;
or
SQL> select table_name from user_tables;
(tabs is actually a public synonym for user_tables).

What is a synonym?
A synonym is very similar to a shell alias. It lets you access a table under a different name e.g. tabs instead of user_tables.

How do I create a synonym?
The tabs synonym was created like this:

SQL> create synonym tabs for user_tables;

Why doesn't flight, leg, authors ... appear in my select table_name from tabs result?
The names flight, leg, authors ... are public synonyms created by a user with the privileges to do so! Students cannot create public synonyms.

How can I list all my/public synonyms in the system?
To list your synonyms use:

SQL> select synonym_name, table_owner, table_name from user_synonyms;


To list all (careful, long list!) synonyms use:

SQL> select synonym_name, table_owner, table_name from all_synonyms;

How can I share my tables with other members of my group?
You need to give them privileges to access and possible modify your tables. Also, they want to create synonyms for your tables so that they don't have to address your tables as ops$your_username.your_tablename. In this example user foo grants bar full access to his table foobar and bar creates a synonym for foobar:
User foo:

SQL> grant all on foobar to ops$bar;


User bar:

SQL> create synonym foobar for ops$foo.foobar;

How can I  load  my data to  my table?
you should use something call SQL Loader to do it. here is the example

Some useful character functions
you could find some useful charactor functions here

How can I create an auto increment field in Oracle?
You have to use something called a sequence in conjunction with a trigger. Here is an example.

How should I create and populate tables for my project?
It is recommended that you use scripts for this purpose since it can be very annoying to do this manually in SQL*Plus. You should also drop the tables in the beginning of the script. This way you can easily recreate and repopulate your tables when you modify them. Here is an example.

How do I use Oracle and PHP on fling?
Check the following handout. Note that it hasn't been updated recently so some instrucitons may be out of date.


Marcus Haebler 10/03/2000  updated by Jihua Zhong 9/26/2001   and Zack Ives 9/8/2004, 9/12/2007
(Version 0.41)