Autonomous Transactions (Oracle8i) - PRAGMAS

Autonomous transactions execute within a block of code as separate transactions from the outer (main) transaction. Changes can be committed or rolled back in an autonomous transaction without committing or rolling back the main transaction. Changes committed in an autonomous transaction are visible to the main transaction, even though they occur after the start of the main transaction. Changes committed in an autonomous transaction are visible to other transactions as well. The RDBMS suspends the main transaction while the autonomous transaction executes:

PROCEDURE main IS BEGIN    UPDATE ...-- Main transaction begins here.    DELETE ...    at_proc;  -- Call the autonomous transaction.    SELECT ...    INSERT ...    COMMIT;   -- Main transaction ends here. END;  PROCEDURE at_proc IS    PRAGMA AUTONOMOUS_TRANSACTION; BEGIN        -- Main transaction suspends here.    SELECT ...    INSERT ...-- Autonomous transaction begins here.    UPDATE ...    DELETE ...    COMMIT;   -- Autonomous transaction ends here. END;         -- Main transaction resumes here.

So, changes made in the main transaction are not visible to the autonomous transaction and if the main transaction holds any locks that the autonomous transaction waits for, a deadlock occurs. Using the NOWAIT option on UPDATE statements in autonomous transactions can help to minimize this kind of deadlock. Functions and procedures (local program, standalone, or packaged), database triggers, top-level anonymous PL/SQL blocks, and object methods can be declared autonomous via the compiler directive PRAGMA AUTONOMOUS_TRANSACTION.

In the example below, the COMMIT does not make permanent pending changes in the calling program. Any rollback in the calling program would also have no effect on the changes committed in this autonomous procedure:

CREATE OR REPLACE PROCEDURE add_company (    name_in   company.name%TYPE    ) IS    PRAGMA AUTONOMOUS_TRANSACTION; BEGIN    determine_credit(name);    create_account(name);    ...    COMMIT;  -- Only commit this procedure's changes. END add_company;

Pragmas
 The PRAGMA keyword is used to give instructions to the compiler. There are four types of pragmas in PL/SQL:
 EXCEPTION_INIT
Tells the compiler to associate the specified error number with an identifier that has been declared an EXCEPTION in your current program or an accessible package. See the Section 1.10, "Exception Handling " section for more information on this pragma.
 
RESTRICT_REFERENCES
Tells the compiler the purity level of a packaged program. The purity level is the degree to which a program does not read/write database tables and/or package variables. See the Section 1.15, "Calling PL/SQL Functions in SQL" section for more information on this pragma.
 
SERIALLY_REUSABLE
Tells the runtime engine that package data should not persist between references. This is used to reduce per-user memory requirements when the package data is only needed for the duration of the call and not for the duration of the session. See the Section 1.14, "Packages" section for more information on this pragma.
 
AUTONOMOUS_TRANSACTION (Oracle8i )
Tells the compiler that the function, procedure, top-level anonymous PL/SQL block, object method, or database trigger executes in its own transaction space. See the Section 1.8, "Database Interaction and Cursors " section for more information on this pragma.
 

What are the type of triggers available in Oracle Reports ?

There are eight report triggers. Global triggers called the Report Triggers : (1) Before Parameter Form (2) After Parameter Form (3) Before Report (4) After Report (5) Between Pages Other Triggers : (1) Validation Triggers (2) Format Triggers (3) Action Triggers /*******************************/ (1) Before Parameter Form Fires before the Runtime Parameter Form is displayed. From  this trigger, you can access and change the values of parameters, PL/SQL global variables,  and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still  fires. Consequently, you can use this trigger for validation of command line parameters). (2) After Parameter Form Fires after the Runtime Parameter Form is displayed.  Fromfires. Consequently, you can use this trigger for  validation of command line parameters or other data). (3) Before Report Fires before the report is executed but after queries are  parsed and data is fetched. (4) Between Pages Fires before each page of the report is formatted, except  the very first page. This trigger can be used for customized page formatting. (Note : In the  Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page,  the trigger does not fire again.) (5) After Report Fires after you exit the Previewer, or after report output  is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This  trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that  this trigger always fires, whether or not your report completed successfully. (6) Validation Triggers Validation Triggers are PL/SQL functions that are executed  when parameter values are specified on the command line and when you accept the  Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you  execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in  the Parameter property sheet. (7) Format Triggers Format Triggers are PL/SQL functions executed before the  object is formatted. The trigger can be used to dynamically change the formatting attributes of  the object. (8) Action Triggers Action Triggers are PL/SQL procedures executed when a  button is selected in the Previewer. The trigger can be used to dynamically call another report  (drill down) or execute any other PL/SQL.