plsql

Synonyms in Oracle SQL PLSQL

December 21, 2012 by techhoneyadmin

Oracle SQL / PLSQL uses synonym as an alias for any database object such as tables, views, sequences, stored procedures, and other database object.

In other words we can say that in Oracle SQL / PLSQL a synonym is an alternative name for database objects.

Oracle SQL / PLSQL syntax to create or replace a synonym is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [SCHEMA.] synonym_name
FOR [SCHEMA.] object_name [@ dblink];

The OR REPLACE phrase in the plsql create synonym syntax above allows us to replace or recreate any plsql synonym (if already existing) for the same database object and without using the drop command.

The PUBLIC phrase in the above plsql create synonym syntax means that the synonym will be accessible for all the valid users, but the user must have the sufficient privileges for the object to use its synonym.

The SCHEMA phrase in the plsql create synonym is the name of the schema where the synonym will reside. It is an optional phrase, if omitted; oracle plsql creates the synonym in the current schema.

The SYNONYM_NAME in the above plsql create synonym syntax is the name of the synonym.

The OBJECT_NAME in the above plsql create synonym syntax is the name of the database object for which the synonym is to be created.

The object can be any of the following:

Table
View
Sequence
Stored procedure
Function
Package
Materialized view
Java class schema object
User-defined object
Synonym

Example to create a synonym for ‘employee’ table

`1`	`CREATE` `PUBLIC` `SYNONYM employee_syn`

`2`	`FOR` `employee;`

Once we run the above PLSQL CREATE SYNONYM command we can fetch the records of the employee table as:

`1`	`SELECT` `*`

`2`	`FROM` `employee_syn;`

If the ‘employee_syn’ synonym already existed we can replace the synonym using the CREATE OR REPLACE SYNONYM as:

`1`	`CREATE` `OR` `REPLACE` `PUBLIC` `SYNONYM employee_syn`

`2`	`FOR` `employee;`

Example to drop a plsql synonym:

Oracle SQL / PLSQL allow us to drop a synonym that we have created earlier.

The syntax to drop a plsql synonym is:

DROP [PUBLIC] SYNONYM [SCHEMA.] synonym_name [FORCE];

The PUBLIC phrase in the above oracle plsql drop synonym syntax allows us to drop a public synonym, if we have specified public then we don’t have to specify a schema for the plsql synonym.

The FORCE phrase in the above oracle plsql drop synonym syntax will drop the synonym even if there are dependencies on it.

Example to drop a plsql synonym is:

`1`	`DROP` `PUBLIC` `SYNONYM employee_syn;`

The above plsql drop synonym command will drop the synonym ‘employee_syn’ from the database.

WHEN OTHERS CLAUSE in Exceptions in Oracle PLSQL

December 21, 2012 by techhoneyadmin

WHEN OTHERS CLAUSE in PLSQL Exceptions:-

WHEN OTHERS CLAUSE in PLSQL Exceptions is used to capture an exception that’s not handled by our Named System Exception or Named Programmer Defined Exception.

The plsql syntax for using WHEN OTHERS Clause in an Exception within a procedure is:

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

BEGIN
–executable_statements

EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]

WHEN exception_name2 THEN
[exception2_handling_statements]

WHEN exception_name_n THEN
[exceptionN_handling_statements]

WHEN OTHERS THEN
[generic_handling_statements]

END [procedure_name];

The plsql syntax for using WHEN OTHERS Clause in an Exception within a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]

BEGIN
–executable_statements

EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]

WHEN exception_name2 THEN
[exception2_handling_statements]

WHEN exception_name_n THEN
[exceptionN_handling_statements]

WHEN OTHERS THEN
[generic_handling_statements]

END [function_name];

Example of WHEN OTHERS Clause in a procedure is:

1CREATE OR REPLACE PROCEDURE add_employee_salary
2   (employee_id_in IN NUMBER, salary_in IN NUMBER)
3IS
4   no_salary EXCEPTION;
5 
6BEGIN
7   IF salary_in = 0 THEN
8      RAISE no_salary;
9 
10   ELSE
11      INSERT INTO employee (employee_id, salary )
12      VALUES ( employee_id_in, salary_in );
13   END IF;
14 
15EXCEPTION
16  WHEN DUP_VAL_ON_INDEX THEN
17raise_application_error (-20001,'Duplicate Employee_Id.');
18 
19  WHEN no_salary THEN
20raise_application_error (-20001,'Salary can’t be 0.');
21 
22  WHEN OTHERS THEN
23raise_application_error (-20002,'An error occurred.');
24 
25END;

In the example of plsql exception handling above, if an exception is encountered that is not DUP_VAL_ON_INDEX or a no_salary exception, then it will be trapped by the PLSQL WHEN OTHERS Clause.

Named Programmer Defined Exceptions in Oracle PLSQL

December 21, 2012 by techhoneyadmin

Oracle PLSQL exception is the condition that terminates the execution of plsql block.

Named Programmer Defined Exception:-

The named programmer defined exceptions are the plsql exceptions that are defined by a programmer to trap specific exceptions hence have been named.

The named programmer defined plsql exceptions are not named in the standard package in plsql and hence the developer needs to define these types of plsql exception.

The plsql syntax for the Named Programmer Defined Exception within a procedure is:

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

BEGIN
–executable_statements

EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]

WHEN exception_name2 THEN
[exception2_handling_statements]

WHEN exception_name_n THEN
[exceptionN_handling_statements]

WHEN OTHERS THEN
[generic_handling_statements]

END [procedure_name];

The plsql syntax for the Named Programmer Defined Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]

BEGIN
–executable_statements

EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]

WHEN exception_name2 THEN
[exception2_handling_statements]

WHEN exception_name_n THEN
[exceptionN_handling_statements]

WHEN OTHERS THEN
[generic_handling_statements]

END [function_name];

Example of plsql exception (named programmer defined exception) handling in a procedure is:

1CREATE OR REPLACE PROCEDURE add_employee_salary
2   (employee_id_in IN NUMBER, salary_in IN NUMBER)
3IS
4   no_salary EXCEPTION;
5 
6BEGIN
7   IF salary_in = 0 THEN
8      RAISE no_salary;
9 
10   ELSE
11      INSERT INTO employee (employee_id, salary )
12      VALUES ( employee_id_in, salary_in );
13   END IF;
14 
15EXCEPTION
16  WHEN no_salary THEN
17    raise_application_error (-20001,'Salary can’t be 0.');
18 
19  WHEN OTHERS THEN
20    raise_application_error (-20002,'An error occurred.');
21END;

Named System Exceptions in Oracle PLSQL

December 21, 2012 by techhoneyadmin

Oracle PLSQL exception is the condition that terminates the execution of plsql block.

Named System PLSQL Exceptions:-

The named system exceptions are the plsql exception that are encountered frequently and hence have been named for ease of use and that’s why they are called as named system exception.

The named plsql exceptions are named in the standard package in plsql and hence the developer does not need to define the plsql exception.

Below is the list of named system plsql exception:

Oracle PLSQL Exception Name	Oracle Error #	Oralce PLSQL Exception Explanation
DUP_VAL_ON_INDEX	ORA-00001	We tried to execute INSERT or UPDATE statement which tried to create a duplicate value in a field restricted by unique index.
TIMEOUT_ON_RESOURCE	ORA-00051	We were waiting for a resource and we timed out.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	We tried to reference a cursor that does not exist. This may have happened because we’ve executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON	ORA-01012	We tried to execute a call to Oracle before logging in.
LOGIN_DENIED	ORA-01017	We tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND	ORA-01403	We tried one of the following: executed a SELECT INTO statement and no rows were returned. referenced an uninitialized row in a table. read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS	ORA-01422	We tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE	ORA-01476	We tried to divide a number by zero.
INVALID_NUMBER	ORA-01722	We tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR	ORA-06500	We ran out of memory or memory was corrupted.
PROGRAM_ERROR	ORA-06501	This is a generic “Contact Oracle support” message because an internal problem was encountered.
VALUE_ERROR	ORA-06502	We tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN	ORA-06511	We tried to open a cursor that is already open.

The plsql syntax for the Named System Exception within a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
–executable_statements
EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]
WHEN exception_name2 THEN
[exception2_handling_statements]
WHEN exception_name_n THEN
[exceptionN_handling_statements]
WHEN OTHERS THEN
[generic_handling_statements]
END [procedure_name];

The plsql syntax for the Named System Exception in a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
–executable_statements
EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]
WHEN exception_name2 THEN
[exception2_handling_statements]
WHEN exception_name_n THEN
[exceptionN_handling_statements]
WHEN OTHERS THEN
[generic_handling_statements]
END [function_name];

Example of plsql exception (named system exception) handling in a procedure is:

1CREATE OR REPLACE PROCEDURE add_new_employee
2  (employee_id_in IN NUMBER, employee_name_in IN VARCHAR2)
3IS
4 
5BEGIN
6  INSERT INTO employee (employee_id, employee_name )
7  VALUES (  employee_id_in, employee_name_in );
8 
9EXCEPTION
10  WHEN DUP_VAL_ON_INDEX THEN
11raise_application_error (-20001,'Duplicate employee_id');
12 
13  WHEN OTHERS THEN
14raise_application_error (-20002,'An error occurred.');
15 
16END;

Exception Handling in Oracle PLSQL

December 21, 2012 by techhoneyadmin

Oracle PLSQL exception can be termed as a condition that terminates the execution of plsql block.

Oracle PLSQL gives us the functionality to handle the exception that can crop up in any Oracle PLSQL block and this functionality is called as Exception Handling.

Using plsql exception handling we can avoid the code to end abruptly.

Whenever an exception occurs, a message that details the cause of the plsql exception can be received.

A PLSQL Exception Message typically consists of 3 parts:

Type of PLSQL Exception
Error Code
Error Message

By handling plsql exceptions in a block we can make sure that the execution of that plsql block is not ended abruptly.

Oracle syntax for plsql exception handling in a plsql block is:

DECLARE
—Declaration Section
BEGIN
–Executable Statements
EXCEPTION
WHEN exception_1 THEN
–Exception_1 handling statements
WHEN exception_2 THEN
–Exception_2 handling statements
.
.
WHEN exception_N THEN
—-Exception_N handling statements
WHEN OTHERS THEN
–Generic exception handling statements
END;

Whenever any exception is raised or encountered, Oracle PLSQL searches for the appropriate plsql exception handler in the exception handling section.

For instance, in the above syntax of plsql exception, if exception_1 is encountered then oracle plsql will execute the exception_1 handling statements to handle the exception and if exception_2 is encountered then oracle plsql will execute the exception_2 handling statements to handle the exception.

As it’s not possible to determine all the exceptions that may happen at runtime, the “WHEN OTHERS” part of the plsql exception handling will be used to manage or handle any plsql exception that has not been handled explicitly.

Only one plsql exception can be raised in any plsql block which means that once an exception is encountered or raised then the execution of that plsql block will be terminated after the plsql exception is handled.

If we have a nested PLSQL block as shown below:

DECLARE
–Declaration Section
BEGIN
DECLARE
–Declaration Section
BEGIN
–Execution code
EXCEPTION
–Exception section
END;
EXCEPTION
–Exception Section
END;

Now, if the plsql exception is encountered or raised in the inner plsql block then the exception should be handled in the plsql exception handling section of the inner plsql block else the control moves to the exception handling section of the next upper plsql block.

If none of the plsql block handles the plsql exception then the execution of the plsql block will be terminated unexpectedly.

Oracle PLSQL exception can be of the following types:

Please click on the links above to know each type of exception is detail.