Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without...
Complex Queries in SQL ----------------------------------------- Hai All, These questions are the most frequently asked in interviews. 1) To fetch ALTERNATE records from a table. (EVEN NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,rowid,...
PL/SQL Implicit Cursor Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement or DML statement Oracle reserves a private SQL area in memory called cursor. Implicit cursor scope you can get information from cursor...
PL/SQL Parameterized Cursor PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query. PL/SQL Parameterized cursor define only datatype of parameter and not need to define it's length. Default values is assigned to the Cursor...
PL/SQL Functions PL/SQL functions block create using CREATE FUNCTION statement. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value. When you create a function or procedure,...
PL/SQL Explicit Cursor Explicit Cursor which are construct/manage by user itself call explicit cursor. User itself to declare the cursor, open cursor to reserve the memory and populate data, fetch the records from the active data set one at a time, apply...
PL/SQL Cursors For Loop ----------------------------------- PL/SQL cursor FOR loop has one great advantage of loop continued until row not found. In sometime you require to use explicit cursor with FOR loop instead of use OPEN, FETCH, and CLOSE statement....
Trigger Triggering SQL statement : SQL DML (INSERT, UPDATE and DELETE) statement that execute and implicitly called trigger to execute. Trigger Action : When the triggering SQL statement is execute, trigger automatically call and PL/SQL trigger block...
PL/SQL RAISE_APPLICATION_ERROR In PL/SQL RAISE_APPLICATION_ERROR function use to assign exception name and exception error code. Define RAISE_APPLICATION_ERROR function syntax, raise_application_error(error_number, error_message); Example Code raise_app_error.sql...
PL/SQL pragma EXCEPTION_INIT pragma EXCEPTION_INIT : Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument, exception_name error_number You can define pragrma EXCEPTION_INIT in DECLARE...
