Saturday, 2 January 2016

Difference between view and materialized view.............

  VIEW :

  1. The view will not store any data.
  2. The view is mainly use for the security purpose.
  3. In view whenever the base table is dropped the view will not be accessible. 
  4. In view DML operation can perform.(on simple views)
MATERIALIZED VIEW :

  1. The materialized view will store the data.
  2. The materialized view is mainly use for the performance purpose.
  3. In materialized view whenever the base table is dropped the view will accessible.
  4. In materialized view DML operation can not perform. 












Please share your in case of any further clarification on above post.

Wednesday, 23 December 2015

What is oracle Functions ?

Functions

These are oracle predefined functions ,which are used in sql statements.

Functions are two types:
 - Single Row Functions
 - Group Functions

Single Row Functions :

Single Row Functions different type some type are described below:

1. String Functions
2. Date Functions
3. Numeric Functions
4. Conversion Functions
5. Miscellaneous Functions
 
1. String Functions :

a. INITCAP : This is use to Capitalized first latter of a string.
Ex. Sql > select initcap('hello world') from dual;
output>
                 

b. UPPER : This is use to convert  a string into upper case.
Ex. Sql > select upper('hello world') from dual;
output>
           
c. LOWER : This is use to convert  a string into lower case.
Ex. Sql > select lower('HELLO world') from dual;
output>
            
d. LENGTH : This function is use to find length of a string.
Ex. Sql >select length('HELLO world') from dual;
output>
           

e. RPAD: This function is use to pads the right-side of a string  with a specific character or a set of characters (Default padding character is blank space)
Ex. Sql> select rpad('hello',9,'$'), rpad('hello',9,'$#') from dual;
output>
          


f. LPAD :This function is use to lads the left-side of a string  with a specific character or a set of characters  (Default padding character is blank space)
Ex. Sql> select lpad('hello',9,'$'), lpad('hello',9,'$#') from dual;
output>
          

g. LTRIM :This function is use to remove string's character from left side of the srting.
Ex. Sql> select ltrim('12heloo123','12345'), ltrim('heloo123','12345') from dual;
output>
          


h. RTRIM :This function is use to remove string's character from right side of the srting.
Ex. Sql> select rtrim('12heloo123','12345'),rtrim('heloo123','12345') from dual;
output>
         
       

i. TRIM : This function is use to remove unwanted character from both side of the string.
    LEADING,TRAILING and BOTH are three parameter use in TRIM function.
    BOTH is default parameter.
Ex. Sql>  select trim(leading'1'from'12reo123'), trim(trailing'3'from'33reo123'), trim(both'3'from'33reo123'), trim('3'from'33reoo123')  from dual;
output>
           


NOTE: TRIM set have only one character can't use more than one character.


Please share your in case of any further clarification on above post.