Debugging a Sample Script with IDERA SQL Debugger for Oracle

From RapidSQL
Jump to: navigation, search

Go Up to Tutorial Sessions

The installation includes a sample script intended to walk you through basic debugging functionality. The sample script creates a package that includes functions and procedures that you debug.

Note: To create the sample package, you must have CREATE privileges.

Overview

Debugging a Sample Script is divided into three sections that familiarize you with basic debugging features and functionality:

Note: For the purposes of this walk-though we have created this package under the user name DEMO_SPENCE.

Getting Started with IDERA SQL Debugger for Oracle

The installation includes a sample script that you execute to create a package containing functions and procedures. These functions and procedures demonstrate basic debugging features available in the IDERA SQL Debugger for Oracle.

Note: To create the sample package, you must have CREATE privileges.

On installation the script is placed in the \UsrScrpt subfolder of the main installation directory.

If you create the package included with the installation, you can delete it and its objects from your system when you finish working with them. The objects to delete:

  • The package COUNT_TIME_INTERVAL
  • The package function WEEKEND_DAYS_( )
  • The package function WORKING_DAYS_( )
  • The package function YEARS_ELAPSED_BETWEEN_( )
  • The procedure YEARS_ELAPSED
  • The procedure YEARS_ELAPSED_Y2K

IDERA SQL Debugger for Oracle Overview

The Getting Started section guides you through:

  • Opening the sample debug script.
  • Executing sample debug script.
  • Changing the Navigator tab display.
  • Confirming the creation of the package, including its functions and procedures.

Getting Started

  1. On the File menu, select Open.
    The Open File(s) dialog opens.
  2. In the Open File(s) dialog, go to DBA900\UsrScrpt\DEBUGGER_DEMO.sql, and then click Open.
    Note: During the installation the DEBUGGER_DEMO.sql is placed in the \UsrScrpt subfolder of the main installation folder.
    The What type of file dialog opens.
  3. On the What type of file dialog, click The file includes the DDL to create a database object, and then click OK.
    The target script opens in an SQL Editor.
  4. On the SQL Editor toolbar, click Execute to execute the script and create the package.
    The target script executes and opens the SQL Editor Results tab, displaying the results of the script execution. If you were not able to create the package, check the error messages to determine the problem.
  5. On the Navigator tab list, click Organize by Owner.
    A list of owners is displayed.
  6. On the Navigator, double-click your owner name.
    A list of your schema objects is displayed.
  7. Under your owner node, double-click the Packages node.
    A COUNT_TIME_INTERVAL displayed, confirming the package’s creation.

Debugging Sample Script 1

Sample Script 1 demonstrates IDERA SQL Debugger’s basic features and functionality with the function WORKING_DAYS( ), which counts the number of business days between two dates.

Debugging Sample Script 1 is divided into five parts:

Sample Script 1 - Starting the Debug Session

After you open and execute DEBUGGER_DEMO.sql, you can begin debugging Sample Script 1. To begin debugging the function WORKING_DAYS( ), start a debug session.

Starting the Debug Session

To start the debug session, do the following:

  1. On the Navigator tab, under the Packages node, double-click the COUNT_TIME_INTERVAL node.
    The COUNT_TIME_INTERVAL node opens and displays the following items:
  2. Under the COUNT_TIME_INTERVAL node, double-click Functions.
    The Functions node opens and displays the following items:
  3. Under the Functions node, right-click WORKING_DAYS ( ), and then click Debug to start the debug session.
    The Function Execution dialog opens with the current date in the boxes.

Sample Script 1 - Entering Input Parameters

After you start a debugging session, you can enter input parameters. You cannot debug a script that requires input parameters until you input those parameters in the Function Execution dialog.

Input Parameters

To enter input parameters, do the following:

  1. Click the P_START_DATE DATE box, and then click the drop-down arrow.
    A calendar opens.
  2. On the calendar, click the left arrow to set the month to November 1999.
  3. Click 1.
    11/01/1999 is displayed in the Value column of P_START_DATE.
  4. Click the P_END_DATE DATE box, and then click the drop-down arrow.
    A new calendar opens.
  5. On the calendar, click the left arrow to set the month to November 1999.
  6. Click 8.
    11/08/1999 is displayed in the Value column of P_END_DATE.
  7. Click OK.
    The Function Execution dialog closes and then opens the following five IDERA SQL Debugger for Oracle interface windows:

Sample Script 1- Inserting Breakpoints

After you input parameters in the Input Parameters dialog, you can begin inserting breakpoints. In this example, you insert the breakpoints in the extracted dependent object code. After you extract this code, locate the target breakpoint lines by searching for the text DBMS_OUTPUT.

Breakpoints

To insert breakpoints, do the following:

  1. In the Dependency Tree window, double-click the COUNT_TIME_INTERVAL package body.
    The SQL code for the package body opens in the SQL Editor window.
  2. On the Edit toolbar, click Find.
    The Find dialog opens.
  3. On the Find dialog, in the Find What box, type DBMS_OUTPUT.
  4. Click Find Next.
    In the SQL Editor, the first occurrence of DBMS_OUTPUT, on line 22 is highlighted.
  5. On the SQL Editor toolbar, click Breakpoint.
    A breakpoint is inserted next to the target line number.
  6. On the Find dialog, click Find Next.
    Tthe next occurrence of DBMS_OUTPUT is highlighted.
  7. Click Find Next a third time.
    The next occurrence of DBMS_OUTPUT, on line 35, is highlighted.
  8. On the Find dialog, click Cancel.
    The Find dialog closes.
  9. On the Edit toolbar, click Breakpoint to insert a second breakpoint.
    You should now have breakpoints set at lines 22 and 35.

Sample Script 1- Stepping Into

After you insert breakpoints, you can step into the function code.

Step Into

To use the Step Into facility, do the following:

  1. On the SQL Editor toolbar, click Go.
    IDERA SQL Debugger for Oracle begins debugging and runs to the first breakpoint, placing the yellow arrow on line 22.
  2. On the SQL Editor toolbar, click Step Into.
    IDERA SQL Debugger for Oracle moves the yellow arrow to the next line of the code.
  3. Click Step Into again to enter the LOOP block.
    IDERA SQL Debugger for Oracle displays the value of the variables in the Variables window.
  4. Click Step Into again to start moving through the LOOP block.
    In the Variables window, IDERA SQL Debugger for Oracle updates the value of variable v_currdate from 01-NOV-1999 to 02-NOV-1999.
  5. Click Step Into two more times.
    In the Variables window, IDERA SQL Debugger for Oracle updates the value of v_theday from NULL to Tuesday.
    Note: If you continued stepping through the LOOP block, the IDERA SQL Debugger for Oracle would continue to update v_currdate and v_theday until v_currdate is greater than p_end_date.
  6. On the SQL Editor toolbar, click Go.
    IDERA SQL Debugger runs to the next breakpoint.
  7. On the SQL Editor toolbar, click Go once more.
    IDERA SQL concludes the debug session and displays the Debug Session Results box.

Sample Script 1 - Viewing Debug Session Results

After Stepping Into and running to the end of the code, IDERA SQL Debugger for Oracle displays a Debug Session Results box containing the following information:

  • Variable Output
  • DBMS_OUTPUT Results
Note: In this example, the IDERA SQL Debugger for Oracle displays a Debug Session Results box because the sample program includes DBMS_OUTPUT.

Debug Session Results

To debug session results, do the following:

  1. Click OK.
The Debug Session Results box closes and your debug session terminates.

Debugging Sample Script 2

Sample Script 2 demonstrates IDERA SQL Debugger for Oracle’s functionality when used on a function containing a bug which prevents it from executing successfully. The buggy function, WEEKEND_DAYS( ), requires input parameters and counts the number of weekend days between two dates. In this section, use IDERA SQL Debugger for Oracle to identify the bug, and then correct the script so that it can execute successfully.

Debugging Sample Script 2 is divided into six parts:

Sample Script 2 - Executing the Function

After you open and execute DEBUGGER_DEMO.sql, you can begin debugging Sample Script 2. To begin debugging the function WEEKEND_DAYS ( ), first execute the function to discover the type of error it returns when it fails to execute.

Executing the Function

To execute the function, do the following:

  1. On the Navigator tab, under the Packages node, double-click the COUNT_TIME_INTERVAL node.
    The COUNT_TIME_INTERVAL node opens.
  2. Double-click the Functions node.
    The Functions node opens.
  3. Click WEEKEND_DAYS ( ), then right-click it and click Execute.
    The Function Execution dialog opens.
  4. In the Value column of the P_START_DATE row, type 11/01/1999.
  5. In the Value column of the P_END_DATE row, type 11/30/1999.
  6. Click Execute.
    The attempt to execute the function fails and returns an error indicating that the character string buffer is too small.

Sample Script 2 - Starting the Debug Session

After you unsuccessfully execute the function WEEKEND_DAYS( ) and determine the nature of its execution error, you can start a debugging session to determine the actual cause of the error.

Starting the Debugging Session

To start the debugging session, do the following:

  1. On the Navigator tab, under the COUNT_TIME_INTERVAL node, under the Functions node, right-click WEEKEND_DAYS ( ), and then click Debug to start the debug session.
The Function Execution dialog opens.

Sample Script 2 - Entering Input Parameters

After you start the debug session, you can enter input parameters in the Function Execution dialog.

Entering Input Parameters

To enter input parameters, do the following:

  1. At the end of the P_START_DATE row, click the drop-down arrow.
    The calendar opens.
  2. On the calendar, click Left Arrow to set the month to November 1999.
  3. Click 1.
    11/01/1999 is displayed in the Value column of the P_START_DATE row.
  4. At the end of the P_END_DATE row, click the drop-down arrow.
    A new calendar opens.
  5. On the calendar, click Left Arrow to set the month to November 1999.
  6. Click 30.
    11/08/1999 is displayed in the Value column of the P_END_DATE row.
  7. Click Continue.
    Tthe Function Execution dialog gloses and then opens the following five IDERA SQL Debugger for Oracle interface windows:

Sample Script 2- Inserting Breakpoints

After you enter input parameters, you can begin inserting breakpoints. In this example, you insert the breakpoints in the extracted dependent object code. After you extract this code, locate the target breakpoint lines by searching for a particular line of code.

Breakpoints

To insert breakpoints, do the following:

  1. In the Dependency Tree window, double-click the COUNT_TIME_INTERVAL package body.
    Tthe SQL code for the package body opens in the SQL Editor.
  2. On the Edit toolbar, click Find.
    The Find dialog opens.
  3. On the Find dialog, in the Find What box, type Function weekend_days, and then click Find Next.
    IDERA SQL Debugger for Oracle highlights the first occurrence of Function weekend_days.
  4. On the Find dialog, click Cancel.
    The Find dialog closes.
  5. Click line 60, the first line of executable code:
  6. On the SQL Editor toolbar, click Breakpoint.
    A breakpoint is inserted next to the line number.
  7. Click Go to start debugging and run to the breakpoint.
    IDERA SQL Debugger for Oracle places the yellow arrow on line 60 and populates the Variables window with the first set of variables in the function code.
    IDERA SQL Debugger for Oracle also populates the Call Stack window with everything called before the breakpoint.

Sample Script 2- Stepping Into

After you set and run to the breakpoint, you can step into the function to locate the cause of the error. To locate the cause of the error, monitor the Variables window. As you step through the code, the Variables window updates with the value of the variables.

Step Into

  1. On the SQL Editor toolbar, click Step Into.
    The yellow arrow moves to the next line of the code, line 64.
  2. On the SQL Editor toolbar, click Step Into.
    IDERA SQL Debugger for Oracle’s Variables window updates the value of v_currdate to 02-NOV-1999.
  3. On the SQL Editor toolbar, click Step Into.
    The yellow arrow moves to the next line of the code, line 66.
  4. On the SQL Editor toolbar, click Step Into.
    The yellow arrow moves to the next line of the code, line 67, and, in the Variables window, updates the value of v_theday to Tuesday.
  5. On the SQL Editor toolbar, click Step Into.
    The yellow arrow moves back to line 64 to repeat the loop.
  6. On the SQL Editor toolbar, click Step Into.
    IDERA SQL Debugger for Oracle’s Variables window updates the value of v_currdate to 03-NOV-1999.
  7. On the SQL Editor toolbar, click Step Into.
    The yellow arrow moves to the next line of the code, line 66.
  8. On the SQL Editor toolbar, click Step Into.
    The IDERA SQL Debugger for Oracle locates the error. The application terminates the debug session, returns an error indicating that the numeric or value character string buffer is too small, extracts the COUNT_TIME_INTERVAL package code into an SQL Editor, and returns an error indicating the line on which the code failed.

Sample Script 2 - Correcting the Script

After you step through the SQL code and locate the error, you can correct the bug in Sample Script 2. When IDERA SQL Debugger for Oracle locates an error, it extracts the target package body into an SQL Editor. To correct this script:

  • Scroll to the incorrect line in the script
  • Analyze the code
  • Correct the error
  • Execute the corrected SQL script
  • Execute the WEEKEND_DAYS ( ) function

The code in Sample Script 2 fails on line 66, returning an error when the variable v_theday increments from the value Tuesday to the value Wednesday. The cause of this error is found in the declarations section of the function script, where the width of the VARCHAR2 variable v_theday is set to 8. Because Wednesday includes nine characters, the value of the variable v_theday fails when it attempts to place a nine-character value in an eight-character variable. To correct this error, increase the width of the variable v_theday to accommodate nine characters.

Correcting the Script

To correct the script, do the following:

  1. On the Navigator tab, under the Packages node, under the COUNT_TIME_INTERVAL. node, right-click Package Body, and then click Extract.
    The package body is extracted into an SQL Editor.
  2. In the SQL Editor, scroll to line 57, the line defining the variable v_theday.
  3. On line 57, change the value of the width from 8 to 9.
  4. On the SQL Editor toolbar, click Execute to execute the script.
    The scrip executes successfully.
  5. On the Navigator tab, under the COUNT_TIME_INTERVAL package node, under the Functions node, click WEEKEND_DAYS ( ).
  6. Right-click WEEKEND_DAYS ( ), and then click Execute.
    The Function Execution dialog opens.
  7. In the Value column of the P_START_DATE row, type 11/01/1999.
  8. In the Value column of the P_END_DATE row, type 11/30/1999.
  9. Click Execute.
    The corrected function executes successfully.