logo_michael-thomas.jpg (3143 bytes)

Oracle

This web page contains survival notes on Oracle.  As I get a chance I will place my survival notes here.  I am not an Oracle jock at all!

Resources

bulletTemp Tables
bullethttp://www.indiana.edu/~dss/Services/DataWarehouse/Oracle/TemporaryTables/tmptab.html
bullet 

My Notes

bulletDefault Admin account & password
Username:  system
Password:  manager

Oracle specific SQL Notes

Topic Notes
TAB Table TAB Table
bulletThis table contains a list of all of the tables in a database.
List Tables in a Database List Tables in Database
bulletselect * from TAB; 
List Structure List the Structure of a Table
bulletdesc [table name];  //List the schema (structure) of a table.
bulletex: desc TAB;

For Oracle:  (desc TAB;)

TNAME not null varchar2 (30)
TABTYPE not nul varchar2(7)
CLUSTERID number

 

Operators Operators
bullet% - wildcard for any number of characters
bullet_ - wildcard for one character.
bulletlike "%CAT%"  //like = having matching characters.
Comment
bulletrem - comment
Tables Tables
bulletcreate table mytable1 (myvarchar1 varchar(10) NOT NULL, mydate date NULL)
bulletinsert into mytable1 (myvarchar1, mydate ) values ('Hello Test', '01-Jan-04')
bulletinsert into mytable1 (myvarchar1, mydate ) values ('Hello Test 2', sysdate)
Temporary Tables Temp Tables
bulletcreate global temporary table tmptable (myvarchar1 varchar(10) NOT NULL) - suppose to work in Oracle 8.1 or greater.  Got this from a friend.
Date Dates
bulletsysdate - the current system date.
bulletselect to_char (mydate, 'yyyymmdd') from mytable1
Converts a date to 'yyyymmdd' format
Functions Functions
bulletsubstr