Oracle10g Common commands and common functions

Select view_name from user_views view the User View list

Select view_name from dba_views System View list

 
 

Desc User_views view information structure

Desc User_tables

 
 

DESC user_synonyms

DESC Dba_synonyms // view the synonym Field

 
 

 
 

Select user from dual current Login user Name

Length

CharacterQuantity

Usage:LENGTH (Str)

LengthB

BytesQuantity

Usage:LENGTHB (Str)

Ltrim	Remove spaces on the left
Rtrim	Remove spaces on the right
Trim	Remove leading and trailing Spaces

Eg:

Select Length (Ltrim ('abc') from dual;

4

 
 

SubSTR

Substring

Usage:SUBSTR (str, startCount, Length)

Str: string to be retrieved

StartCount: Start number, starting from 1.

Length of Count

Replace

Replace

Replace (oldStr, newStr)

 
 

 
 

 
 

 
 

 
 

SYSDATE

System time

Select sysdate from dual

 
 

CURRENT_DATE

Current Time

Alert session set NLS_DATE_FORMAT = 'dd-mon-yyyy hh: mi: si ';

// Change the current time format

 
 

NEXT_DAY

SELECT NEXT_DAY (SYSDATE, 'monday') FROM dual

// Select the date of the next Monday.

 
 

TO_CHAR

Convert non-character to character

Usage
TO_CHAR (content to be converted, Format)

Eg:

Select TO_CHAR (sysdate, 'yyyy-mm-dd') FROM dual;

2008-11-8

 
 

SELECT TO_CHAR (SYSDATE, 'yyyy-mm-ddHh24: Mi: ss') FROM dual;

23:15:00

 
 

TO_DATE

Converts a character to a time

SELECT TO_DATE ('12-March-08 ') FROM dual

 
 

TO_NUMBER

Convert to digital

SELECT TO_NUMBER ('123') FROM dual

8

 
 

 
 

 
 

Aggregate functions

 
 

MAX	Maximum Value
MIN	Minimum
SUM	And
AVG	Average
COUNT	Count // note COUNT (*) is not necessarily equal to COUNT (column)