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)