onlydarksets

Just another WordPress.com weblog

Oracle syntax reference

Posted by onlydarksets on August 11, 2008

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: