Oracle syntax reference

After using SQL Server forever, I’m finally on a project that uses Oracle.  This is just a dumping ground for various syntax.  Note this is for 9i (yes, it’s a bit behind the current release).

Switch schemas:

ALTER SESSION SET CURRENT_SCHEMA = SchemaName

Format dates (including convert string to date):

TO_DATE(<string>, ‘<format>’)

to_date(‘1998/05/31:12:00:00AM’, ‘yyyy/mm/dd:hh:mi:ssam’));

Format strings (including convert date to string):

TO_CHAR(<date>, ‘<format>’)

TO_CHAR(SYSDATE, ‘YYYY’)

CASE in select:

CASE
WHEN owner=’SYS’ THEN ‘The owner is SYS’
WHEN owner=’SYSTEM’ THEN ‘The owner is SYSTEM’
ELSE ‘The owner is another value’
END

DECODE

List all tables in current schema:

SELECT table_name FROM user_tables;

List all tables current user has access to:

SELECT table_name FROM all_tables;