ORACLE - String Functions : rtrim

the rtrim function removes all specified characters from the right-hand side of a string.

The syntax is:

rtrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.

For example:

rtrim('hola '); would return 'hola'
rtrim('hola ', ' '); would return 'hola'
rtrim('paris000', '0'); would return 'paris'
rtrim('332312paris1233213', '123'); would return '332312paris' (removes the individual occurrences of '1', '2', and '3')

ORACLE - String Functions : length

The length function returns the length of the specified string.

The syntax is:

length( string )

string is the string to return the length for.
If string1 is NULL, then the function returns NULL.

For example:

length('Hola Mundo') would return 10.
length('') would return NULL.

ORACLE - String Functions : Instr

The instr function returns the location of a substring in a string.
The syntax is:
instr( stringA, stringB [, start_position [, nth_appearance ] ] )
stringA is the string to search.
stringB is the substring to search for in stringA .
start_position is the position in stringA where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of stringA and then searches towards the beginning of stringA .
nth_appearance is the nth appearance of stringB. This is optional. If omitted, it defaults to 1.

For example:
instr('hello word hello', 'e') would return 2; the first occurrence of 'e'
instr('hello word hello', 'e', 1, 1) would return 2; the first occurrence of 'e'
instr('hello word hello', 'e', 1, 2) would return 13; the second occurrence of 'e'
instr('hello word hello', 'e', -3, 2) would return 2.
instr('hello word hello', 'X', 1, 1) would return 0.

ORACLE - Conversion Functions : To_char

the to_char function converts a number or date to a string.

The syntax :
to_char( value, [ format_mask ], [ nls_language ] )
value can either be a number or date that will be converted to a string.
format_mask is optional. This is the format that will be used to convert value to a string.
nls_language is optional. This is the nls language used to convert value to a string.

Examples - Numbers
The following are number examples for the to_char function.
to_char(1113214.23, '9999.999.9') would return '1113,214.7'
to_char(3214.23, '9,999.99') would return '3,214.23'
to_char(3214.23, '$9,999.00') would return '$3,214.23'
to_char(78, '000099') would return '000078'

Examples - Dates
The following are date examples for the to_char function.
to_char(sysdate, 'yyyy/mm/dd'); would return '2008/07/19'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 19, 2003'

ORACLE - Conversion Functions : To_Number

The to_number function converts a string to a number.

The syntax is:
to_number( string1, [ format_mask ], [ nls_language ] )
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.
nls_language is optional. This is the nls language used to convert string1 to a number.

For example:
to_number('981.53', 9999.99') would return the number 981.53
to_number('225', '999') would return the number 225
to_number('23', '99') would return the number 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows:
to_number('1210.73'') would return the number 1210.73

ORACLE - Date Functions : Months_Between

The months_between function returns the number of months between date1 and date2.

The syntax is:
months_between( dateA, dateB )
dateA and dateB are the dates used to calculate the number of months.
If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Example :
months_between (to_date ('2008/01/01', 'yyyy/mm/dd'), to_date ('2008/03/14', 'yyyy/mm/dd') )
would return -2.41935483870968
months_between (to_date ('2008/10/18', 'yyyy/mm/dd'), to_date ('2008/8/18', 'yyyy/mm/dd') )
would return 2

Applies To:
*Oracle 10g
*Oracle 11g

ORACLE - Date Functions : Add_Months

The add_months function returns a date plus number months.
The syntax is:
add_months( date, number )

date : is the starting date (before the n months have been added).
number : is the number of months to add to date.

Example:
add_months('10-Feb-08', 1) would return '10-Mar-08'
add_months('01-Aug-03', 3) would return '01-Nov-03'
add_months('01-Sep-08', -3) would return '01-Jun-08'

Applies To:
* Oracle 8i
* Oracle 9i
* Oracle 10g
* Oracle 11g

Oracle - Tuning

Tuning Goals

1. Response time
2. Database availability
3. Database hit percentages
4. Memory utilization

ORACLE : List of the Oracle system tables

Below is an listing of the Oracle system tables that are commonly used.

System Table	Description
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE _LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT _VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

 

 

More Oracle
Mas Oracle

ORACLE - NLS_DATE_LANGUAGE

The NLS_DATE_LANGUAGE parameter explicitly changes the language for abbreviations and names of days and months, and for spelled-out values of other date format elements.

NLS_DATE_LANGUAGE = language

Example :
select to_char(to_date('01-' 'JAN' '/' '2005','dd-MONTH/yyyy',
'NLS_DATE_LANGUAGE = ''ENGLISH'' ') ,
'MONTH DD, YYYY')
from dual

More Oracle
Mas Oracle

ORACLE - NLS_NUMERIC_CHARACTERS, Decimal character and Group Separator

The NLS_NUMERIC_CHARACTERS parameter explicitly specifies a new decimal character and group separator.

The text value must have this form: 'dg'
- d is the new decimal character.
- g is the new group separator.

The decimal character and the group separator must be two different single-byte characters, and cannot be a numeric value or any of the following characters: plus sign ("+"), less-than sign ("<"), minus sign or hyphen ("-" ), or greater-than sign (">").
If the decimal character is not a period (.), you must use single quotation marks to enclose all number values that appear in expressions in your SQL statements.
When not using a period for the decimal point, use the TO_NUMBER function to ensure that a valid number is retrieved.


EXAMPLE 1 :
to_number( products.price, '999G999G999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')

EXAMPLE 2 :
to_number(products.price, '999G999G999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')

EXAMPLE 3 :
SELECT to_number('10.123456', '999G999G999D999999',
'NLS_NUMERIC_CHARACTERS = ''.,''') FROM DUAL

More Oracle
Mas Oracle