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. 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

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.

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-)

Category	Msg #s	Example
Severe	5000-5999	Aliasing problems with parameters
Informational	6000-6249	Code that can never run
Performance	7000-7249	Passing 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;
/

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