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