PLSql_Optimize_Level or Pragma Inline for Subprogram Inlining

Subprogram Inlining - The compiler puts the actual code of a subprogram where the call is.

Pro: Improves performance of the program.
Con: Compile time is extended. Program size is larger.

---

PLSQL_OPTIMIZE_LEVEL controls the degree to which the compiler can rearrange code to make your PL/SQL code run faster.

Alter Procedure Proc_x 

          Compile Plsql_Optimize_Level = ?; 

Alter Session Set PlSql_Optimize_Level = ?;

Alter System  Set PlSql_Optimize_Level = ?;

0	Forfeits most of performance gains of 10g
1	Moderate optimization: May eliminate unnecessary computations and exceptions.
2	Agressive optimization: (Default) Like 1, but may also move code.
3	The compiler performs automatic subprogram inlining where appropriate. (new in 11g)

---

PRAGMA INLINE ( identifier , { 'YES' | 'NO' } ) ;

Create Or Replace Procedure Inline_Test

     ( Fld Varchar2 ) Is

     Pragma Inline(Some_Function,'YES');

Begin

     For Idx In 1..10 Loop

          Dbms_Output.Put_Line(Some_Function('abc'));

     End Loop;

End;

Besides procedures, and functions, it also impacts every call to CASE, CONTINUE-WHEN, EXECUTE IMMEDIATE, EXIT-WHEN, LOOP, and RETURN statements.

Use Pragma Exception_Init for Error Handling

Format: PRAGMA EXCEPTION_INIT ( Exception_Name , SqlCode# )

This pragma (compiler directive) is necessary in two situations:

• Use this to name any unnamed ORA- error that might be raised by your program. If you don't, you have to catch those errors with 'WHEN OTHERS.'


• For a user-defined exception, assign a code between –20999 and –20000 to SQLCode so it can be raised.

Situation #1:

---

For instance, if ORA-00068 had a possibility of popping up when your program ran, you'd want to code as shown in the example.

ORA-00068: invalid value string for parameter string, must be between string and string

DECLARE
    e_InvalParam Exception;
    Pragma Exception_Init(e_InvalParam, -68);
BEGIN
    ...
EXCEPTION
    when e_InvalParam then ...
END;

The following ORA- errors have already been named. (See the package named Standard in All_Source.)

Pragma Exception_Init( Access_Into_Null ,' -6530 '); Pragma Exception_Init( Case_Not_Found ,' -6592 '); Pragma Exception_Init( Collection_Is_Null ,' -6531 '); Pragma Exception_Init( Cursor_Already_Open ,' -6511 '); Pragma Exception_Init( Dup_Val_On_Index ,' -0001 '); Pragma Exception_Init( Invalid_Cursor ,' -1001 '); Pragma Exception_Init( Invalid_Number ,' -1722 '); Pragma Exception_Init( Login_Denied ,' -1017 '); Pragma Exception_Init( No_Data_Found , 100 ); Pragma Exception_Init( No_Data_Needed ,' -6548 '); Pragma Exception_Init( Not_Logged_On ,' -1012 '); Pragma Exception_Init( Program_Error ,' -6501 '); Pragma Exception_Init( Rowtype_Mismatch ,' -6504 '); Pragma Exception_Init( Self_Is_Null ,' -30625 '); Pragma Exception_Init( Storage_Error ,' -6500 '); Pragma Exception_Init( Subscript_Beyond_Count ,' -6533 '); Pragma Exception_Init( Subscript_Outside_Limit ,' -6532 '); Pragma Exception_Init( Sys_Invalid_Rowid ,' -1410 '); Pragma Exception_Init( Timeout_On_Resource ,' -0051 '); Pragma Exception_Init( Too_Many_Rows ,' -1422 '); Pragma Exception_Init( Userenv_CommitScn_Error ,' -1725 '); Pragma Exception_Init( Value_Error ,' -6502 '); Pragma Exception_Init( Zero_Divide ,' -1476 ');

Situation #2:

---

I want to check for a valid code, and raise an error if the value is wrong.

Procedure p_CkCode
                ( i_Emp# NUMBER
                , i_Code NUMBER) is
    v_Code    NUMBER;
    v_SqlCode NUMBER;
    
    e_InvalCode Exception; 
    Pragma Exception_Init(e_InvalCode,-20456); 
Begin
    ...
Exception
    when e_InvalCode then
        v_SqlCode := SqlCode;
        Insert Into AuditTrack (errors)
                         values(i_emp#||’ ’||v_SqlCode);
        Raise;
End;

By default, all user exceptions have a SqlCode of 1.
The code must be between –20,000 and –20,999. Anything else will raise ORA-21000.

If no code number was assigned to the user-defined unhandled exception, you'll see the following:

ORA-06510: PL/SQL: unhandled user-defined exception

Using Pragma Autonomous_Transaction to Create an Audit Trail

This pragma (compiler directive) is useful because the insert into the audit table -- while happening in the middle of another transaction, stands on its own. That means if a rollback happens on the main transaction, your audit trail will still show that a change was attempted.

For example, connect to the HR schema:

Connect hr/hr

---

This sequence will be used as a transaction number in the audit table.

Create Sequence Trans_Seq;

---

This is a simple table that tracks a transaction number, the user that attempted the update, and the system date.

Drop Table Track_Upd_Sal purge;
Create Table Track_Upd_Sal ( Trans_Num  NUMBER
                           , Trans_User VARCHAR2(10)  
                           , Trans_Date DATE
                           );

---

This procedure will be called by a trigger. Because it is autonomous, the Insert shown here will happen whether or not the update to Salary is committed or rolled back. (The 'Commit' shown in this procedure does not affect the transaction in the Employee table.)

Create or Replace Procedure p_Track_Upd_Sal
    ( User_In   VARCHAR2
    , Date_In   DATE)                   is
    Pragma Autonomous_Transaction; 

Begin
    Insert into Track_Upd_Sal values( Trans_Seq.NextVal
                                    , User_In
                                    , Date_In);
Commit;
End;
/

---

This trigger will call the above procedure if someone tries to update the salary in the Employees table.

Create or Replace Trigger Bef_Upd_Sal_Emp
    Before Update 
        of Salary 
            on Employees
                referencing new as new old as old 
                    for each row
Begin
    p_Track_Upd_Sal ( User
                    , SYSDATE);
End;
/

---

OK, the user 'HR' is now going to try this:

Update Employees
    Set Salary = 10000
    Where Employee_Id = 197;

---

If we look, the salary has been updated, and the audit trail has a new record.

Select Employee_id
     , Salary
  From Employees
 Where Employee_Id = 197;
    
Select *
From Track_Upd_Sal;

---

But notice the salary change can still be rolled back.

Rollback;
Select Employee_id
     , Salary
  From Employees
 Where Employee_Id = 197;

Tiny URL: http://tinyurl.com/autotran