Saturday, July 3, 2010

Nested Cursors: Reading from Multiple Tables


The following is an example of how to loop through a cursor within a cursor to generate a report as shown above. This uses the HR schema supplied by Oracle.
Declare
    -------------------------------------------------------
    -- This cursor pulls 'Clerks' from the Jobs table. 
    -------------------------------------------------------
    Cursor cur_Job Is
        Select Job_Id
             , Job_Title
          From Jobs
         Where Job_Title like '%Clerk%';
         
    rec_Job cur_Job%RowType; -- record layout matches cursor
    -------------------------------------------------------
    -- This cursor pulls employees with the parameter for
    -- the Job_Id being supplied on the open of the employee
    -- cursor. 
    -------------------------------------------------------
    Cursor cur_Emp ( inp_Job Varchar2 ) Is 
        Select RPad(Last_Name,10) As Last_Name
             , Salary 
          From Employees
         Where Job_Id = inp_Job
           And Salary <= 2500
         Order By Salary;
         
    rec_Emp cur_Emp%RowType; -- record layout matches cursor
Begin
    Open cur_Job;
    -------------------------------------------------------
    -- In the outer loop, fetch one record from the Jobs
    -- cursor, and print the info.
    -------------------------------------------------------
    Loop
        Fetch cur_Job Into rec_Job;
        Exit When cur_Job%NotFound;
        Dbms_Output.Put_Line( Chr(10) -- line break
                            ||'Job: '
                            ||rec_Job.Job_Title
                            ||Chr(10)
                            ||'---------------------'
                            );
        -----------------------------------------------------
        -- in the inner loop, open the employee cursor using
        -- the Job_Id from the Jobs cursor. Print the employee
        -- name, and salary, before closing to get another
        -- jobs record.
        -----------------------------------------------------
        Open cur_Emp (rec_Job.Job_Id);
        Loop
            Fetch cur_Emp into rec_Emp;
            Exit When cur_Emp%NotFound;
            Dbms_Output.Put_line( rec_Emp.Last_Name
                                ||' $'
                                ||rec_Emp.Salary    
                                );
        End Loop;
        Close cur_Emp;
        -----------------------------------------------------
    End Loop;
    Close cur_Job;
End;
 
Tiny URL: http://tinyurl.com/nest-cur

Thursday, June 24, 2010

New in 11g: Continue and Continue When

CONTINUE immediately moves to the next iteration of the loop.
CONTINUE WHEN conditionally moves to the next iteration of the loop.
Begin
  For Idx IN 1..3
  Loop
    Dbms_Output.Put_line('-----------------------------------');
    Dbms_Output.Put_line('Idx '
                        ||Idx
                        ||': BeFore Continue. Mod(Idx,2) = '
                        ||Mod(Idx,2));
---------------------------------------------------
--    If Mod(i,2) = 0 THEN --{ This is the
--        Continue;        -- same as the
--    End If;              -- Continue When below.}
---------------------------------------------------
    Continue When Mod(Idx,2) = 0;
    Dbms_Output.Put_line
      ('After Continue. Print If Mod(Idx,2) is not 0.');
  End Loop;
End;
/

Notes:
  • When using CONTINUE in a simple loop, make sure you increment your loop before the CONTINUE so you don't create an infinite loop.
  • When using CONTINUE in a While loop, try using a GOTO and a label.
  • Inlining impacts every call to CONTINUE and CONTINUE WHEN.

Wednesday, June 23, 2010

New in 10: Compile-Time Warnings

This will allow you to improve code before it runs live.

"ALTER SESSION" is recommended, as "ALTER SYSTEM" takes too much overhead, or use "ALTER PROCEDURE ... COMPILE."

Find message descriptions by SHOW ERRORS or query User_Errors (PLW-)
CategoryMsg #sExample
Severe5000-5999Aliasing problems with parameters
Informational6000-6249Code that can never run
Performance7000-7249Passing a VARCHAR2 value to a NUMBER column

value_clause::=
{ ENABLE | DISABLE | ERROR* }:
{ All| Severe| Informational| Performance|
{ Integer| (Integer [, Integer ] ...)}}
* 'Error' treats a particular message as an error instead of a warning.

Examples:
Alter Session Set Plsql_Warnings = 
               'Enable:Severe', 'Disable:Informational';
Alter Session Set Plsql_Warnings = 
               'Disable:All';
Alter Session Set Plsql_Warnings = 
               'Disable:5000', 'Enable:5001','Error:5002';
Alter Session Set Plsql_Warnings = 
               'Enable:(5000,5001,5002)','Disable:6000,6001)';
Alter Procedure prog_x Compile Plsql_Warnings=
               'Enable:Performance';
Call Dbms_Warning.Set_Warning_Setting_String
               ('Enable:All' ,'Session');
To see how it's currently set:
BEGIN
DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = ' || $$PLSQL_WARNINGS);
END;
/

Tuesday, June 22, 2010

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 = ?;
0Forfeits most of performance gains of 10g
1Moderate optimization: May eliminate unnecessary computations and exceptions.
2Agressive optimization: (Default) Like 1, but may also move code.
3The 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.

Monday, June 21, 2010

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