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

My Notes

Oracle specific SQL Notes

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

For Oracle:  (desc TAB;)

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

 
Operators Operators
  • % - wildcard for any number of characters
  • _ - wildcard for one character.
  • like "%CAT%"  //like = having matching characters.
Comment
  • rem - comment
Tables Tables
  • create table mytable1 (myvarchar1 varchar(10) NOT NULL, mydate date NULL)
  • insert into mytable1 (myvarchar1, mydate ) values ('Hello Test', '01-Jan-04')
  • insert into mytable1 (myvarchar1, mydate ) values ('Hello Test 2', sysdate)
Temporary Tables Temp Tables
  • create 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
  • sysdate - the current system date.
  • select to_char (mydate, 'yyyymmdd') from mytable1
    Converts a date to 'yyyymmdd' format
Functions Functions
  • substr