Table of Contents
- 1 What is Pragma EXCEPTION_INIT?
- 2 What is the first parameter of the Pragma EXCEPTION_INIT function?
- 3 Does Raise_application_error rollback?
- 4 What is pragma and types in Oracle?
- 5 Which of the following types is pragma in Oracle?
- 6 What is the difference between RAISE_APPLICATION_ERROR and exception_init?
- 7 How to define exception_init in declare block?
What is Pragma EXCEPTION_INIT?
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler. A user-defined exception declared within the current scope. PRAGMA. Signifies that the statement is a compiler directive.
What is the first parameter of the Pragma EXCEPTION_INIT function?
The pragma exception_init is declared with its first parameter as the local variable deadlock_error and the second variable as the dead lock’s error code -60. Start of the execution section of the block.
What is pragma in Oracle & What are the types of pragma?
In Oracle PL/SQL, PRAGMA refers to the compiler directive and it is used to provide an instruction to the compiler. PRAGMA are processes at compile time, not run time. They do not affect the meaning of program, they simply convey information to the compiler. This is the generally asked interview question.
What is Pragma AUTONOMOUS_TRANSACTION in Oracle with example?
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. Pragmas are processed at compile time, not at run time.
Does Raise_application_error rollback?
Inside a trigger a RAISE_APPLICATION_ERROR does not perform a ROLLBACK, it aborts the current operation, i.e. a single UPDATE/INSERT/DELETE.
What is pragma and types in Oracle?
In Oracle PL/SQL, PRAGMA refers to the compiler directive and it is used to provide an instruction to the compiler. PRAGMA are processes at compile time, not run time. They do not affect the meaning of program, they simply convey information to the compiler.
What are the types in pragma?
Types of pragma directives in C
| Instruction | Description |
|---|---|
| OPTIMIZE | To turn the optimization feature on or off |
| LOCALITY | To name a coded subspace |
| OPT_LEVEL | To set the level of optimization |
| HP_SHLIB_VERSION | To create a version of a shared library routine |
What is Pragma Serially_reusable and why is it used?
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server (for example, a PL/SQL anonymous block, an OCI call to the database or a stored procedure call through a database link).
Which of the following types is pragma in Oracle?
PL/SQL offers several pragmas as listed below:
- PRAGMA AUTONOMOUS_TRANSACTION.
- PRAGMA EXCEPTION_INIT.
- PRAGMA SERIALLY_REUSABLE.
- PRAGMA RESTRICT_REFERENCES.
- PRAGMA INLINE. PRAGMA AUTONOMOUS_TRANSACTION: Transaction flow in autonomous transaction explained in the below picture:
What is the difference between RAISE_APPLICATION_ERROR and exception_init?
pragma exception init turns an Oracle Error into a named exception. If a database operation raises an ORA-00054 “resource busy”, you would have to code: . Raise_application_error is used to RAISE an error – exception_init is used to deal with errors (i guess you could say they are opposites in a way).
What is the difference between a pragma and exception_init?
A pragma is a compiler directive which indicates that the Statements followed by keyword PRAGMA is a compiler directive statement this means that the statement will be processed at the compile time & not at the runtime. Exception_init helps you in associating an exception name with an Oracle error number.
What is exception_init in Oracle?
Exception_init helps you in associating an exception name with an Oracle error number. In other words we can say that using Exception_Init you can name the exception. Why name the exception? Yes, there is a way of declaring user-define exception without the name and that is by using Raise_Exception_Error procedure.
How to define exception_init in declare block?
You can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program. exception_name and error_number define on yourself, where exception_name is character string up to 2048 bytes suppot and error_number is a negative integer range from -20000 to -20999. PL/SQL procedure successfully operation.