How to remove repeated character from string

By using  regular experssion it is possible to remove repeated characters from string

Example

select regexp_replace('Tesstt', '(.)\1+','\1') 
  from dual;

Result: Test

How to find last character in the string

By using SUBSTR function it is possible to find last character in string.

Select SUBSTR('last character', -1) from dual

result: r

Ceil Function

In Oracle/PLSQL, the ceil function returns the smallest integer value that is greater than or equal to a number.
The syntax for the ceil function is: ceil( number ) number is the value used to find the smallest integer value.

 For Example
ceil(32.65) would return 33
ceil(32) would return 32
ceil(-32.65) would return -32
ceil(-32) would return -32

Views, Materialized Views and Synonyms

Views

A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used. All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Using views encourages the use of Shared SQL with the benefit of reduced memory usage.
Read-Only vs Updatable Views
The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table.
In order to be updatable, a view cannot contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list or finally (with some exceptions) a JOIN . See the Oracle Database Administrator's Guide for full details. Views that are not updatable can be modified using an INSTEAD OF trigger.

Materialized Views

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

Synonyms

A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package. A public synonym is owned by the user group PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.
Synonyms are used to:
- Mask the real name and owner of a schema object
- Provide global (public) access to a schema object
- Provide location transparency for tables, views, or program units of a remote database.
- Simplify SQL statements for database users
e.g. to query the table PATIENT_REFERRALS with SQL:
SELECT * FROM MySchema.PATIENT_REFERRALS;
CREATE PUBLIC SYNONYM referrals FOR MySchema.PATIENT_REFERRALS;
After the public synonym is created, you can query with a simple SQL statement:
SELECT * FROM referrals;

How to bring sentence till the second occurrence of mentioned word

Bu using regular expression it is possible to find sentence till the second occurrence of mentioned word.

select 

regexp_replace('Payables A 2204216 22490587 Payables A 15 2648191', '^(.*)Payables.*$', '\1') p_result

from dual

select 
substr('Payables A 2204216 22490587 Payables A 15 22648191',1
,instr('Payables A 2204216 22490587 Payables A 15 22648191', 'Payables',1,2)-1)  NAMES
from dual;

How to find number of words in the string

By using length function it is possible to find number of words in the sentence.  First, we find the length of the string and length of the same string without spaces. The difference between them plus 1 will be the number of words in the sentence.

SELECT LENGTH('Oracle PL SQL') - LENGTH(REPLACE('Oracle PL SQL', ' ', '')) + 1 WordCount

from DUAL

PROCEDURES

This Oracle tutorial explains how to create and drop procedures in Oracle/PLSQL with syntax and examples.

CREATE PROCEDURE

Just as you can in other languages, you can create your own procedures in Oracle.

Syntax

The syntax to create a procedure in Oracle is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Example

Let's look at an example of how to create a procedure in Oracle.

The following is a simple example of a procedure:

CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )

IS
   cnumber number;

   cursor c1 is
   SELECT course_number
    FROM courses_tbl
    WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   INSERT INTO student_courses
   ( course_name,
     course_number )
   VALUES
   ( name_in,
     cnumber );

   commit;

   close c1;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.

DROP PROCEDURE

Once you have created your procedure in Oracle, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in Oracle is:

DROP PROCEDURE procedure_name;

procedure_name

The name of the procedure that you wish to drop.

Example

Let's look at an example of how to drop a procedure in Oracle.

For example:

DROP PROCEDURE UpdateCourse;

This example would drop the procedure called UpdateCourse