Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Oracle

Synonyms in Oracle SQL PLSQL

by

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:

  1. Table
  2. View
  3. Sequence
  4. Stored procedure
  5. Function
  6. Package
  7. Materialized view
  8. Java class schema object
  9. User-defined object
  10. Synonym

Example to create a synonym for ‘employee’ table 

CREATE PUBLIC SYNONYM employee_syn
FOR employee;

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

SELECT *
FROM employee_syn;

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

CREATE OR REPLACE PUBLIC SYNONYM employee_syn
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: 

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

by

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: 

CREATE OR REPLACE PROCEDURE add_employee_salary
   (employee_id_in IN NUMBER, salary_in IN NUMBER)
IS
   no_salary EXCEPTION;

BEGIN
   IF salary_in = 0 THEN
      RAISE no_salary;

   ELSE
      INSERT INTO employee (employee_id, salary )
      VALUES ( employee_id_in, salary_in );
   END IF;

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'Duplicate Employee_Id.');

  WHEN no_salary THEN
raise_application_error (-20001,'Salary can’t be 0.');

  WHEN OTHERS THEN
raise_application_error (-20002,'An error occurred.');

END;

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

by

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: 

CREATE OR REPLACE PROCEDURE add_employee_salary
   (employee_id_in IN NUMBER, salary_in IN NUMBER)
IS
   no_salary EXCEPTION;

BEGIN
   IF salary_in = 0 THEN
      RAISE no_salary;

   ELSE
      INSERT INTO employee (employee_id, salary )
      VALUES ( employee_id_in, salary_in );
   END IF;

EXCEPTION
  WHEN no_salary THEN
    raise_application_error (-20001,'Salary can’t be 0.');

  WHEN OTHERS THEN
    raise_application_error (-20002,'An error occurred.');
END;

Named System Exceptions in Oracle PLSQL

by

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:

  1. executed a SELECT INTO statement and no rows were returned.
  2. referenced an uninitialized row in a table.
  3. 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: 

CREATE OR REPLACE PROCEDURE add_new_employee
  (employee_id_in IN NUMBER, employee_name_in IN VARCHAR2)
IS

BEGIN
  INSERT INTO employee (employee_id, employee_name )
  VALUES (  employee_id_in, employee_name_in );

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'Duplicate employee_id');

  WHEN OTHERS THEN
raise_application_error (-20002,'An error occurred.');

END;

Exception Handling in Oracle PLSQL

by

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:

  1. Type of PLSQL Exception
  2. Error Code
  3. 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.