Skip to main content

PRAGMA

What is PRAGMA?


In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below:
  1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.

  2. PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.

  3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.

  4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.

  5. PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inline. In-lining replaces a subprogram call with a copy of the called subprogram.
Syntax:
CREATE OR REPLACE [FUNCTION | PROCEDURE] [NAME] IS
IS
[PRAGMA];
BEGIN
 ...
 ...
END;

The procedure P_ERR_LOG uses the PRAGMA AUTONOMOUS_TRANSACTION directive to capture the error occurring in a program unit.
CREATE OR REPLACE PROCEDURE P_ERR_LOG (P_UNIT VARCHAR2, P_SQLCODE NUMBER, P_SQLERRM VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO ERR_TABLE VALUES (P_UNIT, P_SQLCODE, P_SQLERRM);
   COMMIT;
END;
Labels:

Comments

Post a Comment

Popular posts from this blog

How to setup and use AME - Approval Management Engine

Approval Management Engine - AME For Purchase Requisition Approvals Purchase Requisitions can be routed for approval using the AME Approval Management Engine. This whitepaper describes how to setup AME for use with requisition approvals, and shows how a requisition approval list is built based on the AME setup. Approvers in the AME based approver list are assigned to the requisition based on the AME rules setup for the Purchase Requisition Approval transaction. Similar setup can be done for Requester Change Order Approval and for Internal Requisition Approval, although those are not specifically covered in this whitepaper. The screenshots provided are based on 11i.AME.B, and some of the navigation details are specific to 11i.AME.B. However, most of the details provided are applicable to 11i.AME.A and higher including R12. Assign AME Roles and Responsibilities AME responsibilities in 11i.AME.A are assigned directly to the users. However, In R12 or 11i.AME.B and higher, AME respons...
Post a Comment
Read more

Workflow Important Debug Queries

deq_time is not always populated in WF_DEFERRED. The best way to monitor is to check if there are any READY events select msg_state,count(*) from applsys.aq$wf_deferred  group by msg_state; For getting Item_Type and Display name for Runnable processes. SELECT WFA_ACT.ITEM_TYPE ITEM_TYPE ,   WFA_ACT.NAME PROCESS_NAME ,   WFA_ACT.DISPLAY_NAME DISPLAY_NAME FROM wf_activities_vl wfa_act WHERE wfa_act.runnable_flag = 'Y' AND wfa_act. type            = 'PROCESS' AND sysdate BETWEEN wfa_act.begin_date AND NVL(wfa_act.end_date, sysdate); Query to find records that are pending in each of the workflow agent listener queues SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||        ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || .queue_table ||        ' where deq_time is null and nvl(delay,enq_time)<sy...
Post a Comment
Read more

Approver Does Not Have Entry In WF_ROLES Error

AME Issue - Incorrect entry in WF_ROLES Symptom: A.  User received following error for Purchase Requisition AME workflow.   ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions. This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=3783).   B.  Error occurs attempting to approve a Quote: ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions. This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=1)     C.  In 11.5.10, the following erro...
Post a Comment
Read more