Category Archives: Oracle Pl/SQL Interview Questions

What are the return values of functions SQLCODE and SQLERRM? | Oracle Pl/SQL Questions

SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE.

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

What is Raise_application_error? | Oracle Pl/SQL Questions

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

What is Pragma EXECPTION_INIT? Explain the usage? | Oracle Pl/SQL Questions

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

What is an Exception? What are types of Exception? | Oracle Pl/SQL Questions

Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are: CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR INVALID_NUMBER LOGON_DENIED NOT_LOGGED_ON PROGRAM-ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE VALUE_ERROR ZERO_DIVIDE OTHERS. exception is an identifier and … Continue reading

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

Write the order of precedence for validation of a column in a table? | Oracle Pl/SQL Questions

I. done using Database triggers. ii. done using Integrity Constraints.

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

What happens if a procedure that updates a column of table X is called in a database trigger of the same table? | Oracle Pl/SQL Questions

Mutation of table occurs.

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

What are two virtual tables available during database trigger execution? | Oracle Pl/SQL Questions

The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why? | Oracle Pl/SQL Questions

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing. yes WE can use COMMIT and ROLLBACK triggers, but by using PRAGAMA AUTONAMOUS_TRANSATIONS. Now the … Continue reading

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

How many types of database triggers can be specified on a table? What are they? | Oracle Pl/SQL Questions

Insert Update Delete Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. … Continue reading

Posted in Oracle Pl/SQL Interview Questions | Leave a comment

What is a database trigger? Name some usages of database trigger?

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

Posted in Oracle Pl/SQL Interview Questions | Leave a comment