Debugging in SQL Developer

Hello! My name is Alexey Maryakhin, I am an Oracle developer. In this article we will continue to get acquainted with the topic of debugging PL/SQL code.

In the previous article, we explored the debugging capabilities of PL/SQL Developer. Here I propose to consider another tool – SQL Developer (version 21.2.0.187 Build 187.1842). We will also outline the pros and cons of these tools in comparison.

As it turned out, there is not much information in Russian on this topic, and documentation on SQL Developer doesn't answer many questions. In this article I will try to highlight the main points regarding the use SQL Developer for debugging. If the topic is relevant to you, welcome!

Debugging Features and Limitations

Debugging with the tool SQL Developer allows you to perform different types of debugging: step-by-step; with breakpoints on lines of code and for exceptions. You can view the values ​​of variables (including complex types), set the values ​​of variables (except complex types). This is, in general, a standard set of debugging functions.

Additionally SQL Developer allows you to debug sessions on a remote host. This is convenient in cases where a defect is reliably reproduced on some bench – for example, in a test circuit or on a product base, if there is access there – but the developer is unable or is not able to reproduce the case on his bench. Then you can connect to a remote debugger and see in detail what is happening in the “foreign” session.

There are also several restrictions:

  • Debugging code with certain constructs. Debugging some objects does not work: you will not be able to enter the object, even if you have all rights and debugging information is enabled. This limitation is due to the presence of certain constructs in the code – the use of custom types, see. SQL Developer doesn't step into code. In this case, you can make a copy of the package, removing the interfering code and leaving only those procedures or functions that need to be debugged.

  • Executing queries in a debugging session. IN SQL Developer You cannot run queries in a debugging session. The function is very useful, PL/SQL Developer, for example, it is present. I hope it will appear here in the future too.

  • Using bind variables. The tool does not have the ability to use bind variables in debugging scripts. Therefore, in order to transfer, for example, a LOB object to a script, you will have to manually add code to fill in the input data.

Debugging permissions

In the previous article we already talked about setting up permissions for debugging, we’ll give a brief summary here.

-- Права на отладку объектов своей схемы:
GRANT DEBUG CONNECT SESSION TO <user>;
-- Права на отладку объекта чужой схемы (под пользователем схемы-владельца объекта):
GRANT DEBUG ON <object_name> TO <user>;
-- Права на отладку любого объекта:
GRANT DEBUG ANY PROCEDURE TO <user>;

If we want to use the protocol for debugging Java Debug Wire Protocol (V SQL Developer it is used by default), you need to grant ACL rights (for more details, see Testing and Debugging Procedures with SQL Developer):

-- Под SYS:
BEGIN
    DBMS_NETWORK_ACL_ADMIN.append_host_ace(
        -- * = для любых адресов. Можно задать разрешение только для
        -- определённого ip-адреса или имени хоста, или задать в виде маски
        host => '*',
        ace  => xs$ace_type( privilege_list => xs$name_list('jdwp')
                           , principal_name => 'TEST_DEBUG_USR'
                           , principal_type => xs_acl.PTYPE_DB));
END;

Additionally:

Resetting ACL
-- Под SYS:
BEGIN
    DBMS_NETWORK_ACL_ADMIN.remove_host_ace(
        host => '*',
        ace  => xs$ace_type( privilege_list => xs$name_list('jdwp')
                           , principal_name => 'TEST_DEBUG_USR'
                           , principal_type => xs_acl.PTYPE_DB));
END;
Viewing the ACL
SELECT dnap.principal, dna.host, dna.lower_port
     , dna.upper_port, dna.acl, dnap.privilege
  FROM dba_network_acls dna
  JOIN dba_network_acl_privileges dnap ON dnap.acl = dna.acl
                                      AND dnap.aclid = dna.aclid;

Debug Settings in SQL Developer

For debugging to work correctly, you need to make a number of settings.

Select the debugging protocol. There are two options:

  1. DBMS_DEBUG_JDWP – use for debugging Java Debug Wire Protocol. It works via a network connection, which allows for remote debugging. And debugging of stored Java‑source, but we do not consider this functionality in this article.

  2. DBMS_DEBUG (deprecated) – direct connection to the database. In this case, a separate session will be created for debugging, which is also used in PL/SQL Developer. For SQL Developer option can be used if you have DBMS_DEBUG_JDWP there are some problems with access during the network connection (firewall, etc.).

The differences between protocol options are briefly discussed in the section “Under the hood”.

Behavior when running debug

If breakpoints are not set in advance in the objects being debugged, then when debugging is started, the script is immediately executed to completion and the transaction is committed. To avoid this, it is recommended to select the option in the global debugging settings Debugger > Start Debugging Option > Step Into (default is Run Until a Breakpoint Occurs):

Considering that the transaction is automatically committed during debugging, despite the parameter Autocommit = False, this can lead to unexpected consequences. Especially when debugging in production.

Preparing the script

By analogy with PL/SQL Developer There are two ways to create a debug script:

  1. Write a regular anonymous block manually.

  2. Open stored PL/SQL-code, press Debug…, select the desired method, adjust the script proposed in the template. Using an example package:

Setting Script Variables

Script variables are configured depending on how the debugging script is created.

Option 1: the script was created manually. In this case, the input parameters are specified directly in the script, as usual. As a recommendation, I can note that it is better to describe input variables in a block DECLARE so that after running the script under debugging you can change its values.

Option 2: the script was created through the menu Debug…. Here the input parameters are specified in the script creation dialog: in the table Parameters or directly in the text of the script template. An example of the dialogue can be seen in the previous section.

This option has limitations: only variables of simple types can be specified in the table. Types LOB, BOOLEAN, etc. will have to be written manually directly in the script. For LOB types this is somewhat inconvenient: CLOB does not always fit into one line block – it needs to be divided into several and joined; You won’t be able to define a BLOB explicitly at all—you’ll have to encode it in Base64 or invent something similar.

Launch

For start debugging an anonymous block two steps need to be completed:

  1. Call the context menu on any line of the block.

  2. Select item Debug…:

After this, the anonymous block code will be opened in a new tab, debugging will start: debugging controls will become available.

To start it debugging stored PL/SQL codein the script preparation dialog box you just need to click OKcm. Preparing the script. After this, debugging will start directly in the code of the stored object (package): debugging controls will become available.

Step by step debugging

Step by Step Debugging Commands

Team

Description

Terminate

Abort execution

Find Execution Point

Go to the current execution point. In case this point is lost

Step Over

Go through a debugging step without entering a procedure or function. Let's go inside only if there is a breakpoint in the nested method

Step Into

Go inside the procedure or function that is at the current debugging step. For DML statements, the button will lead to entry into the corresponding trigger, if there is one for the table

Step Out

Exit the current procedure or function to a higher level: into the code from which you entered this procedure or function in step-by-step debugging mode

Step to End of Method

Execute to the last instruction of the current method

Resume

Continue execution to the end or to the next breakpoint

Pause

Suspend execution without finishing (you can continue)

Suspend All Breakpoints

Disable or enable all breakpoints

Breakpoints

To interrupt code execution, you can set breakpoints on a specified line. Their influence extends only to lines that contain executable instructions. That is, breakpoints will not work in comments or in the middle of a statement.

Breakpoints only have an effect on objects compiled with debugging information added. Unlike PL/SQL Developer they can also be placed in anonymous blocks, but only after the debugging session has started, when the anonymous block opens in a separate tab.

The dot is applied to the object line number according to the number from dba_source. If you set a point in an irrelevant object, for example, in a version of the object that has not yet been compiled, then the point will be taken into account during debugging if dba_source This line contains the instruction to be executed. However, the behavior will be different than expected because the point is actually set on a different line.

For the specimen in question SQL Developer breakpoints are set locally. That is, other users will not see the breakpoints you set.

Installation

The breakpoint is set in the standard way: by clicking on the line number in the object. See screenshot:

You can set a point either directly in the desired object by opening it for viewing or editing, or from a debugging script when debugging is already in progress.

You can also set a point through the context menu called for the object line number:

Another option is through the tab Breakpointswhich has a button to add a point (item Source Breakpoint). Please note that in this option, all point parameters – type, object name and line number – will have to be entered manually. For example, for a package you need to enter the object type as the string $Oracle.PackageBody., and the package name as .pls, which is not very convenient:

Settings

To configure the breakpoint parameters, you need to open the settings dialog. There are three ways to do this.

Context menu. Using the context menu on the dot icon in the corresponding line, select:

Pointer. Hold the pointer over the breakpoint icon for a few seconds. A Quick Setup window will appear, through which you can open an advanced setup dialog:

Edit button. Via the menu View open the tab Breakpointspress the button Edit:

These settings can be made both before and during debugging.

The settings window contains three tabs: Definition Conditions And Actions. Let's see what is worth paying attention to.

1. Definition — basic parameters of the point:

The set of settings depends on the type of point. The setting common to all types is Breakpoint Group Name. It allows you to group points by category and manage (disable, enable, delete) a whole group of points at once, which is very convenient. To include a point in a group, you can specify it directly in the settings of a specific point or drag the point into the group in the tab Breakpoints. Tab view for grouped points:

You can also customize the behavior of points for the entire group, for example, set conditions or actions:

2.Conditions — conditions when the point should be triggered:

This tab also has its own parameters:

  • Condition conditions for stopping. The syntax is the same as in regular SQL. For example, l_res IS NOT NULLWhere l_res is a variable available in the context of a given breakpoint.

    There is a nuance here. For example, for numeric values, conditions of the form a_id_client_order = 2839397788 When passing through a point, a warning appears that the expression could not be calculated. In this case, the option with a smaller number a_id_client_order = 2 works correctly. It turned out that such large values ​​​​need to be specified as a real number. So this is an option a_id_client_order = 2839397788.0 will work as expected. There is not a word in the documentation about such features.

  • Thread Options conditions for flows. It is not clear how to use this for debugging PL/SQL, if anyone has information, please share it in the comments.

  • Pass Count, condition on the number of passes. Let us dwell only on the number of passes through the point indicated here.

3.Actions — actions performed when passing through a point:

Options for this tab:

  • Halt Execution, interrupts execution at the point.

  • Beep, does it give a beep? In my experiments, I couldn’t hear the sound, but the function looks interesting: when debugging heavy code, it happens that you need to wait until execution reaches the desired breakpoint. During this wait, you can be distracted, and the sound signal will not let you miss the moment when you can return to debugging.

  • Log Breakpoint Occurrence, Controlling the output of a message to the log when a point is reached:

    Tag: custom label.

    Expression: the meaning of the expression. For example, you can display the value of a variable.

    Stack: log the complete call stack.

    Example configuration and result:

  • Enable — Disable a Group of Breakpoints: enable/disable a group of breakpoints when a given point is reached. Also an interesting feature. It can be used, for example, when debugging cycles, when you need to “catch” and debug in detail only a certain step of the cycle. If we immediately set breakpoints in the loop, we will stop at every step, which is completely inconvenient. Solution: set the necessary breakpoints in the loop, include them in the group, and make them inactive; we put another conditional point inside the loop without interruption, when it is reached, we turn on the group of points – we debug it, when we exit also a conditional point – we turn off the group of points, if necessary.

Exceptions

IN SQL Developer you can also set breakpoints for exceptions. The difference between these points is that they are not tied to a specific line of code, but are triggered when exceptions occur.

By default, when you start debugging, a catch-all point for all exceptions is automatically created, which catches exceptions of any type:

In the event of an exception, the stop will occur at the corresponding instruction even before the exception is raised. That is, you can see the values ​​of the variables that led to the error:

Such a breakpoint cannot be removed, but it can be disabled (Disable). Then there will be no stopping at the exception line.

To stop at an exception with a specific code, you can create a new point. For this, the algorithm is as follows:

1. On the panel Breakpoints press “+“, select point type Exception Breakpoint:

2. In the point parameters in Exception Class:

  1. Specify the exception code in the format $Oracle.EXCEPTION_ORA_, where is the error number. For example, for ORA-01 476 you would specify $Oracle.EXCEPTION_ORA_1476.

  2. Set the required flags:

    Break for Caught Exceptions — dwell on the exceptions being processed. That is, for those who have processing in the block EXCEPTION.

    Break for Uncaught Exceptions — stop at unhandled exceptions for which there is no provision for interception in the block EXCEPTION.

The remaining options on the other tabs are similar to other breakpoint types.

3. Important point: if you need to stop only on exceptions with certain codes, then it is logical to disable the common point Oracle exception, Persistent – otherwise we will dwell on all the exceptions. But if you disable this point, the interruption will not work on all others Exception Breakpoint. To achieve the desired result – stop only at exceptions of certain codes – a common point Oracle exception, Persistent you don’t need to disable it, but just remove the flag for it Halt Execution (abort execution):

Confirm that the exception class specified is correct. After this, in case of exceptions, execution will stop only on user-specified exceptions:

Variable values

View

When debugging in SQL Developer It is possible to view and set variable values. There are several ways to view variable values ​​when debugging:

  1. Hover over a variable directly in the code.

  2. Tab Smart Data — show variables that are used in the context of the currently executing line of code.

  3. Tab Data – show all variables available in the context of all executable code, including internal and external package variables.

  4. Tab Watches — show user-selected variables.

  5. Window Inspect (from the context menu on a variable) – view the specific selected variable.

For variables of complex types, viewing works completely in all details:

But in Watches or Inspect the value of a specific field of the structure cannot be viewed: you need to add the entire variable and drill down to the desired field in the tree or table.

Change

You can change the value of a variable from the tabs Smart Data Data or Watches: by double clicking on a variable in the list, or through the context menu (item Modify Value…):

It is important to note that changing the value only works for simple types. For objects you can only reset the value to NULL.

Remote debugging

Rights

To start remote debugging of single user sessions, you will need regular rights DEBUG CONNECT SESSION. To remotely debug other people's sessions, you must grant permissions DEBUG CONNECT ON USER:

GRANT DEBUG CONNECT ON USER <target_user> TO <debug_user>;
-- <target_user> - кого отлаживаем
-- <debug_user> - кто отлаживает

Rights can be issued under SYSor from the owner user, that is .

Example: Running a Test Procedure

Let's take an example where the target session is connected under TEST_DEBUG_USR and the debugging session is connected under it:

In this example PL/SQL Developer acts as a client application. The client runs it, connects to the database and performs some action on it. This is the client action we want to debug using SQL Developer.

The sequence of steps will be like this:

1. IN SQL Developer start a remote debugging session:

Enter the parameters:

As a result, a process will start waiting for the remote session to connect:

2. In the session being debugged (from PL/SQL Developer) run the script for connecting to the debugger:

BEGIN
    DBMS_DEBUG_JDWP.connect_tcp(host => '10.200.9.149', port => '4000');
END;

Here host is the address of the client machine whose session we want to debug. As a result, in SQL Developer A message about connecting a remote session will be displayed and the corresponding process will appear:

3. In the session being debugged, run the corresponding code (in SQL Developer in the same code we first set breakpoints):

BEGIN
    PK_DEBUG_DEMO.test_debug_vars();
END;

As a result, the session being debugged will “hang” waiting for a response, and in the debugging session we find ourselves inside the corresponding object at the first stopping point and see that the debugging control buttons have become available:

4. After passing the debugged code to the end in the debugged remote session, the procedure will end, and in SQL Developer The debugging session will still be active: you can run the code again and repeat the debugging. To end the debugging session, click the button Terminate, we will see a message in the log Debugger disconnected from database.

Debugging Features in SQL Developer

  1. Output is printed only after the script has completed. If we interrupt execution, we won’t see anything.

  2. Transaction management – on a manual drive! If you forget to add processing to the script (ROLLBACK), when debugging is complete, the transaction will be automatically committed. To such behavior does not affect setting Autocommit = False. Therefore, when debugging on a food database, you need to be more careful and do not forget to add ROLLBACK.

conclusions

About the debugging functionality in SQL Developer it cannot be said to be intuitive. The documentation is difficult to organize and often does not answer questions. I think it's all because SQL Developer is a comprehensive product that contains many different features and tools for working with the Oracle database.

However, the tool uses the actual debugging interface J.D.W.P. therefore more reliable. There are also several convenient functions, such as viewing the contents of variables of complex types, managing groups of breakpoints, and remote debugging, which can greatly simplify the life of an Oracle developer.

“Under the hood”

As a debugging mechanism, Oracle provides an interface in the form of DBMS packages: DBMS_DEBUG And DBMS_DEBUG_JDWP. It is worth considering that DBMS_DEBUG starting from version 19 of Oracle it is marked as deprecated. Current debugging mechanism – Java Debug Wire Protocol (plastic bag DBMS_DEBUG_JDWP).

Let's see what's inside each package.

DBMS_DEBUG

Allows you to debug the following PL/SQL-objects:

  • procedure

  • function

  • package,

  • package body

  • trigger

  • anonymous block,

  • object type,

  • object type body.

Feature of the debugging option through this package: the entire debugging progress is controlled through the functions or procedures of this package. PL/SQL Developer, at least until version 15.0.3.2059, it just uses this package for debugging, while in SQL Developer current package is used DBMS_DEBUG_JDWP.

Debugging in this version implies the presence of two sessions:

  • Target Session (target session) – the code being debugged;

  • Debug Session (debug session) – the session from which debugging is controlled.

The following code is executed in the target session:

-- Включение отладки
BEGIN
    --sys.dbms_session.reset_package;
    sys.dbms_debug.probe_version(major => :major, minor => :minor);
    :debug_session_id := sys.dbms_debug.initialize; 
    sys.dbms_debu.set_timeout_behaviour(sys.dbms_debug.retry_on_timeout);
    sys.dbms_debug.debug_on;
END;

...Отладка (управляется из отладочной сессии)...

-- Выключение отладки
BEGIN
    sys.dbms_debug.default_timeout := 3600;
    sys.dbms_debug.debug_off; 
END;

All other commands are executed from the debug session:

sys.dbms_debug.attach_session  -- Подключиться к целевой сессии
sys.dbms_debug.synchronize     -- Получить событие из целевой сессии
sys.dbms_debug.set_breakpoint  -- Установить точку остановки
sys.dbms_debug.get_value       -- Получить значение переменной
sys.dbms_debug.print_backtrace -- Получить стек вызовов
sys.dbms_debug.continue        -- Продолжить выполнение
sys.dbms_debug.detach_session  -- Отключиться от целевой сессии (закончить отладку)
-- ... и т. п.

Diagram of the debugging process:

Nuances:

  1. The debugging session may freeze periodically.

  2. The values ​​of variables of complex types are not shown; We are talking about multi-level structures.

DBMS_DEBUG_JDWP

The package implements a debugging mechanism via Java Debug Wire Protocol. Allows debugging as stored PL/SQL‑code, and stored Java-procedures.

Unlike the previous option, there is no debugging session. To enable debugging in the target session, connect to the debugger DBMS_DEBUG_JDWP.CONNECT_TCP(). Next, debugging is controlled through the mechanisms J.D.W.P.

In addition to the rights to debug the session, the user of the database schema must be given the rights ACL (Access Control List), since debugging is done through an external Java-interface (JDWP).

Tool comparison and conclusions

In the article we examined in detail the capabilities and features SQL Developer in the article. In short, the tool is useful for working with variables of complex types, supports remote debugging, and is also free.

It's time to compare the tool with PL/SQL Developer, which I reviewed last time. For convenience, I compiled a table with characteristics:

Action

PL/SQL Developer

SQL Developer

Using bind variables

+

−

Running queries in a debugging session

+

−

Conditional breakpoints

+

+

Breakpoints for exceptions

+

+

Grouping breakpoints

−

+

Viewing Variables of Simple Types

+

+

Viewing Variables of Complex Types

−

+

Viewing Variables in Large Objects

−

−

Setting simple variable values

+

+

Setting Scalar Array Values

+

−

Remote debugging

−

+

Debugging Java source

−

−

Price issue

−
(paid)

+
(free)

Let's summarize the comparison.

PL/SQL Developer — a fairly simple and intuitive application. The use of bind variables and the ability to execute queries in a debugging session in some cases greatly simplifies the debugging process. On the other hand, the tool is paid and not very reliable due to the use of an outdated package DBMS_DEBUG, which is marked as deprecatedbecause of this, it cannot display the values ​​of variables of complex types and may freeze.

SQL Developer raises many questions regarding ergonomics. Its behavior is not always obvious and intuitive, and not everything can be learned from the documentation. But, firstly, debugging here works correctly with complex types of variables, which greatly simplifies the debugging process. Secondly, there is the possibility of remote debugging. Thirdly, the tool is developed and supported by the company itself Oracle, which means support for the latest and most relevant technologies Oracle. Moreover, the application is free.

As you can see, each debugging tool has its pros and cons. I hope that thanks to my reviews of them and comparison, you will be able to choose the best option for yourself. Personally, I use both depending on the situation and the task at hand.

Below I will leave useful materials on the topic: source codes for the objects used in the examples, as well as links to documentation and useful materials.

Thank you for your attention!

Useful materials

Example objects

Creating a test user
-- SYS
-- Create the user 
CREATE USER TEST_DEBUG_USR
  IDENTIFIED BY test_debug_usr
  TEMPORARY TABLESPACE TEMP;

-- Grant/Revoke role privileges 
GRANT CONNECT TO TEST_DEBUG_USR;
GRANT RESOURCE TO TEST_DEBUG_USR;
GRANT UNLIMITED TABLESPACE TO TEST_DEBUG_USR;
GRANT DEBUG CONNECT SESSION TO TEST_DEBUG_USR;

BEGIN
    DBMS_NETWORK_ACL_ADMIN.append_host_ace( host => '*' -- Для любых адресов. Можно задать разрешение только для определённого ip-адреса или имени хоста, или задать в виде маски
                                          , ace  => xs$ace_type( privilege_list => xs$name_list('jdwp')
                                                               , principal_name => 'TEST_DEBUG_USR'
                                                               , principal_type => xs_acl.PTYPE_DB));
END;
/
Test table
-- Create table
CREATE TABLE T_CLIENT_ORDER
(
  id_client_order INTEGER NOT NULL,
  NUM             VARCHAR2(256) NOT NULL,
  COST            NUMBER(14,2),
  notes           VARCHAR2(2000)
);
-- Add comments to the table 
COMMENT ON TABLE T_CLIENT_ORDER IS 'Заказ клиента';
-- Create/Recreate primary, unique and foreign key constraints 
ALTER TABLE T_CLIENT_ORDER
  ADD CONSTRAINT PK_CLIENT_ORDER PRIMARY KEY (ID_CLIENT_ORDER)
  USING INDEX 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255;

-- Вставка данных
INSERT INTO T_CLIENT_ORDER (ID_CLIENT_ORDER, NUM, COST)
WITH w_json AS
     ( SELECT '{ "orders": [
        { "id": 19010, "num": "N-8812", "cost": 633.50 },
        { "id": 19012, "num": "N-8821", "cost": 133 },
        { "id": 19014, "num": "N-8855", "cost": 560 },
        { "id": 19016, "num": "N-8889", "cost": 1090 }
    ]}' AS text
         FROM dual
     )
SELECT o.*
  FROM w_json j
     , JSON_TABLE( j.text, '$."orders"[*]' COLUMNS ( ID INTEGER PATH '$."id"'
                                                   , NUM VARCHAR2(256) PATH '$."num"'
                                                   , COST NUMBER PATH '$."cost"'
                                                   )) o;
Package PK_DEBUG_DEMO
CREATE OR REPLACE PACKAGE PK_DEBUG_DEMO AS
CREATE OR REPLACE PACKAGE PK_DEBUG_DEMO AS

    PROCEDURE test_debug_vars;


    PROCEDURE put_dbms_output
        ( a_clob CLOB
        );


    FUNCTION test_clob
        ( a_clob    CLOB
        ) RETURN    CLOB;


    FUNCTION f1
        ( p1        VARCHAR2
        , p2        VARCHAR2
        ) RETURN    VARCHAR2;


    FUNCTION f0
        RETURN BOOLEAN;


    FUNCTION div
        ( a         NUMBER
        , b         NUMBER
        ) RETURN    NUMBER;


    FUNCTION get_order_rec
        ( a_id_client_order     INTEGER
        ) RETURN                T_CLIENT_ORDER%ROWTYPE;


    FUNCTION get_order
        ( a_id_client_order     INTEGER
        ) RETURN                VARCHAR2;


    PROCEDURE update_order
        ( a_id_client_order     INTEGER
        , a_cost                NUMBER := NULL
        , a_text                VARCHAR2 := NULL
        );

END PK_DEBUG_DEMO;
/
CREATE OR REPLACE PACKAGE BODY PK_DEBUG_DEMO AS
    TYPE TStringArray IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
    TYPE TStringHash IS TABLE OF VARCHAR2(1024) INDEX BY VARCHAR2(256);
    TYPE TStringArrayHash IS TABLE OF TStringArray INDEX BY VARCHAR2(256);
    TYPE TStringHashHash IS TABLE OF TStringHash INDEX BY VARCHAR2(256);
    --
    TYPE TSimpleRec IS RECORD
        ( text_attr     VARCHAR2(256)
        , num_attr      NUMBER
        );
    TYPE TSimpleRecArray IS TABLE OF TSimpleRec INDEX BY BINARY_INTEGER;
    TYPE TSimpleRecHash IS TABLE OF TSimpleRec INDEX BY VARCHAR2(256);
    --
    TYPE TSubSubRec IS RECORD
        ( sub_sub_text_attr     VARCHAR2(256)
        , sub_sub_num_attr      NUMBER
        );
    TYPE TSubSubRecArray IS TABLE OF TSubSubRec INDEX BY BINARY_INTEGER;
    TYPE TSubSubRecHash IS TABLE OF TSubSubRec INDEX BY VARCHAR2(256);
    TYPE TSubRec IS RECORD
        ( sub_text_attr     VARCHAR2(256)
        , sub_num_attr      NUMBER
        , sub_sub_array     TSubSubRecArray
        , sub_sub_hash      TSubSubRecHash
        );
    TYPE TSubRecArray IS TABLE OF TSubRec INDEX BY BINARY_INTEGER;
    TYPE TSubRecHash IS TABLE OF TSubRec INDEX BY VARCHAR2(256);
    TYPE TComplexRec IS RECORD
        ( text_attr     VARCHAR2(256)
        , num_attr      NUMBER
        , sub_array     TSubRecArray
        , sub_hash      TSubRecHash
        );


    PROCEDURE test_debug_vars
    AS
        la_str_arr          TStringArray;
        lh_str_hash         TStringHash;
        lh_str_arr_hash     TStringArrayHash;
        lh_str_hash_hash    TStringHashHash;
        lr_simple_rec       TSimpleRec;
        lt_simple_rec_arr   TSimpleRecArray;
        lh_simple_rec_hash  TSimpleRecHash;
        lr_complex_rec      TComplexRec;
        l_key               VARCHAR2(256);
        l_str               VARCHAR2(256);
        l_str_2             VARCHAR2(256);
    BEGIN
        -- 1. TStringArray
        la_str_arr(la_str_arr.COUNT+1) := 'la_str_arr-1';
        la_str_arr(la_str_arr.COUNT+1) := 'la_str_arr-2';
        la_str_arr(la_str_arr.COUNT+1) := 'la_str_arr-3';
        -- output
        DBMS_OUTPUT.put_line('TStringArray:');
        FOR i IN la_str_arr.FIRST..la_str_arr.LAST LOOP
            DBMS_OUTPUT.put_line('    la_str_arr('||i||') = '''||la_str_arr(i)||'''');
        END LOOP;
        DBMS_OUTPUT.new_line();

        -- 2. TStringHash
        FOR i IN 1..5 LOOP
            l_key := 'map_'||TRIM(TO_CHAR(i, '000'));
            lh_str_hash(l_key) := 'val_'||i;
        END LOOP;
        -- output
        DBMS_OUTPUT.put_line('TStringHash:');
        l_str := lh_str_hash.FIRST;
        WHILE l_str IS NOT NULL LOOP
            DBMS_OUTPUT.put_line('    lh_str_hash('''||l_str||''') = '''||lh_str_hash(l_str)||'''');
            l_str := lh_str_hash.NEXT(l_str);
        END LOOP;
        DBMS_OUTPUT.new_line();

        -- 3. TStringArrayHash
        lh_str_arr_hash('arr_1') := la_str_arr;
        la_str_arr(la_str_arr.COUNT+1) := 'la_str_arr-4';
        lh_str_arr_hash('arr_2') := la_str_arr;
        -- output
        DBMS_OUTPUT.put_line('TStringArrayHash:');
        l_str := lh_str_arr_hash.FIRST;
        WHILE l_str IS NOT NULL LOOP
            DBMS_OUTPUT.put_line('    lh_str_arr_hash('''||l_str||'''):');
            FOR i IN lh_str_arr_hash(l_str).FIRST..lh_str_arr_hash(l_str).LAST LOOP
                DBMS_OUTPUT.put_line('        lh_str_arr_hash('''||l_str||''')('||i||') = '''||lh_str_arr_hash(l_str)(i)||'''');
            END LOOP;
            l_str := lh_str_arr_hash.NEXT(l_str);
        END LOOP;
        DBMS_OUTPUT.new_line();

        -- 4. TStringHashHash
        lh_str_hash_hash('map_1')('sub_1') := 'val_1_1';
        lh_str_hash_hash('map_1')('sub_2') := 'val_1_2';
        lh_str_hash_hash('map_2')('sub_1') := 'val_2_1';
        lh_str_hash_hash('map_2')('sub_2') := 'val_2_2';
        lh_str_hash_hash('map_2')('sub_3') := 'val_2_3';
        -- output
        DBMS_OUTPUT.put_line('TStringHashHash:');
        l_str := lh_str_hash_hash.FIRST;
        WHILE l_str IS NOT NULL LOOP
            DBMS_OUTPUT.put_line('    lh_str_hash_hash('''||l_str||'''):');
            l_str_2 := lh_str_hash_hash(l_str).FIRST;
            WHILE l_str_2 IS NOT NULL LOOP
                DBMS_OUTPUT.put_line('        lh_str_hash_hash('''||l_str||''')('''||l_str_2||''') = '''||lh_str_hash_hash(l_str)(l_str_2)||'''');
                l_str_2 := lh_str_hash_hash(l_str).NEXT(l_str_2);
            END LOOP;
            l_str := lh_str_hash_hash.NEXT(l_str);
        END LOOP;
        DBMS_OUTPUT.new_line();

        -- 5. TSimpleRec
        lr_simple_rec.text_attr := 'text_1';
        lr_simple_rec.num_attr := 1.1;
        -- output
        DBMS_OUTPUT.put_line('TSimpleRec:');
        DBMS_OUTPUT.put_line('    lr_simple_rec.text_attr=""'||lr_simple_rec.text_attr||'''');
        DBMS_OUTPUT.put_line('    lr_simple_rec.num_attr="||lr_simple_rec.num_attr);
        DBMS_OUTPUT.new_line();

        -- 6. TSimpleRecArray
        FOR i IN 1..10 LOOP
            lt_simple_rec_arr(i).text_attr := "text_'||i;
            lt_simple_rec_arr(i).num_attr := 1+i/100;
        END LOOP;
        -- output
        DBMS_OUTPUT.put_line('TSimpleRecArray:');
        FOR i IN lt_simple_rec_arr.FIRST..lt_simple_rec_arr.LAST LOOP
            DBMS_OUTPUT.put_line('    lt_simple_rec_arr('||i||'):');
            DBMS_OUTPUT.put_line('        .text_attr=""'||lt_simple_rec_arr(i).text_attr||'''');
            DBMS_OUTPUT.put_line('        .num_attr="||lt_simple_rec_arr(i).num_attr);
        END LOOP;
        DBMS_OUTPUT.new_line();

        -- 7. TSimpleRecHash
        FOR i IN 1..10 LOOP
            l_key := "map_'||TRIM(TO_CHAR(i, '000'));
            lh_simple_rec_hash(l_key).text_attr := 'text_'||i;
            lh_simple_rec_hash(l_key).num_attr := 1+i/100;
        END LOOP;
        -- output
        DBMS_OUTPUT.put_line('TSimpleRecHash:');
        l_str := lh_simple_rec_hash.FIRST;
        WHILE l_str IS NOT NULL LOOP
            DBMS_OUTPUT.put_line('    lh_simple_rec_hash('''||l_str||'''):');
            DBMS_OUTPUT.put_line('        .text_attr=""'||lh_simple_rec_hash(l_str).text_attr||'''');
            DBMS_OUTPUT.put_line('        .num_attr=""'||lh_simple_rec_hash(l_str).num_attr||'''');
            l_str := lh_simple_rec_hash.NEXT(l_str);
        END LOOP;
        DBMS_OUTPUT.new_line();

        -- 8. TComplexRec
        lr_complex_rec.text_attr := 'lvl_1_text';
        lr_complex_rec.num_attr := 0.1;
        lr_complex_rec.sub_array(1).sub_text_attr := 'lvl_2_text_1';
        lr_complex_rec.sub_array(1).sub_num_attr := 1.1;
        lr_complex_rec.sub_array(1).sub_sub_array(1).sub_sub_text_attr := 'lvl_3_text_1_1';
        lr_complex_rec.sub_array(1).sub_sub_array(1).sub_sub_num_attr := 1.001;
        lr_complex_rec.sub_array(1).sub_sub_array(2).sub_sub_text_attr := 'lvl_3_text_1_2';
        lr_complex_rec.sub_array(1).sub_sub_array(2).sub_sub_num_attr := 1.002;
        lr_complex_rec.sub_array(1).sub_sub_array(3).sub_sub_text_attr := 'lvl_3_text_1_3';
        lr_complex_rec.sub_array(1).sub_sub_array(3).sub_sub_num_attr := 1.003;
        lr_complex_rec.sub_array(1).sub_sub_hash('map_1_1').sub_sub_text_attr := 'lvl_3_text_1_m1';
        lr_complex_rec.sub_array(1).sub_sub_hash('map_1_1').sub_sub_num_attr := 1.001;
        lr_complex_rec.sub_array(1).sub_sub_hash('map_1_2').sub_sub_text_attr := 'lvl_3_text_1_m2';
        lr_complex_rec.sub_array(1).sub_sub_hash('map_1_2').sub_sub_num_attr := 1.002;
        lr_complex_rec.sub_array(2).sub_text_attr := 'lvl_2_text_2';
        lr_complex_rec.sub_array(2).sub_num_attr := 2.1;
        lr_complex_rec.sub_array(2).sub_sub_array(1).sub_sub_text_attr := 'lvl_3_text_2_1';
        lr_complex_rec.sub_array(2).sub_sub_array(1).sub_sub_num_attr := 2.001;
        lr_complex_rec.sub_array(2).sub_sub_array(2).sub_sub_text_attr := 'lvl_3_text_2_2';
        lr_complex_rec.sub_array(2).sub_sub_array(2).sub_sub_num_attr := 2.002;
        lr_complex_rec.sub_array(2).sub_sub_array(3).sub_sub_text_attr := 'lvl_3_text_2_3';
        lr_complex_rec.sub_array(2).sub_sub_array(3).sub_sub_num_attr := 2.003;
        lr_complex_rec.sub_array(2).sub_sub_hash('map_2_1').sub_sub_text_attr := 'lvl_3_text_2_m1';
        lr_complex_rec.sub_array(2).sub_sub_hash('map_2_1').sub_sub_num_attr := 2.001;
        lr_complex_rec.sub_array(2).sub_sub_hash('map_2_2').sub_sub_text_attr := 'lvl_3_text_2_m2';
        lr_complex_rec.sub_array(2).sub_sub_hash('map_2_2').sub_sub_num_attr := 2.002;
        lr_complex_rec.sub_hash('map_1').sub_text_attr := 'lvl_2_text_1';
        lr_complex_rec.sub_hash('map_1').sub_num_attr := 1.1;
        lr_complex_rec.sub_hash('map_1').sub_sub_array(1).sub_sub_text_attr := 'lvl_3_text_1_1';
        lr_complex_rec.sub_hash('map_1').sub_sub_array(1).sub_sub_num_attr := 1.001;
        lr_complex_rec.sub_hash('map_1').sub_sub_array(2).sub_sub_text_attr := 'lvl_3_text_1_2';
        lr_complex_rec.sub_hash('map_1').sub_sub_array(2).sub_sub_num_attr := 1.002;
        lr_complex_rec.sub_hash('map_1').sub_sub_hash('map_1_1').sub_sub_text_attr := 'lvl_3_text_1_m1';
        lr_complex_rec.sub_hash('map_1').sub_sub_hash('map_1_1').sub_sub_num_attr := 1.001;
        lr_complex_rec.sub_hash('map_1').sub_sub_hash('map_1_2').sub_sub_text_attr := 'lvl_3_text_1_m2';
        lr_complex_rec.sub_hash('map_1').sub_sub_hash('map_1_2').sub_sub_num_attr := 1.002;
        lr_complex_rec.sub_hash('map_2').sub_text_attr := 'lvl_2_text_1';
        lr_complex_rec.sub_hash('map_2').sub_num_attr := 1.1;
        lr_complex_rec.sub_hash('map_2').sub_sub_array(1).sub_sub_text_attr := 'lvl_3_text_2_1';
        lr_complex_rec.sub_hash('map_2').sub_sub_array(1).sub_sub_num_attr := 2.001;
        lr_complex_rec.sub_hash('map_2').sub_sub_array(2).sub_sub_text_attr := 'lvl_3_text_2_2';
        lr_complex_rec.sub_hash('map_2').sub_sub_array(2).sub_sub_num_attr := 2.002;
        lr_complex_rec.sub_hash('map_2').sub_sub_hash('map_2_1').sub_sub_text_attr := 'lvl_3_text_2_m1';
        lr_complex_rec.sub_hash('map_2').sub_sub_hash('map_2_1').sub_sub_num_attr := 2.001;
        lr_complex_rec.sub_hash('map_2').sub_sub_hash('map_2_2').sub_sub_text_attr := 'lvl_3_text_2_m2';
        lr_complex_rec.sub_hash('map_2').sub_sub_hash('map_2_2').sub_sub_num_attr := 2.002;
        -- output
        DBMS_OUTPUT.put_line('TComplexRec:');
        DBMS_OUTPUT.put_line('    lr_complex_rec:');
        DBMS_OUTPUT.put_line('        .text_attr=""'||lr_complex_rec.text_attr||'''');
        DBMS_OUTPUT.put_line('        .num_attr="||lr_complex_rec.num_attr);
        FOR i IN lr_complex_rec.sub_array.FIRST..lr_complex_rec.sub_array.LAST LOOP
            DBMS_OUTPUT.put_line("        .sub_array('||i||'):');
            DBMS_OUTPUT.put_line('            .sub_text_attr=""'||lr_complex_rec.sub_array(i).sub_text_attr||'''');
            DBMS_OUTPUT.put_line('            .sub_num_attr="||lr_complex_rec.sub_array(i).sub_num_attr);
            FOR j IN lr_complex_rec.sub_array(i).sub_sub_array.FIRST..lr_complex_rec.sub_array(i).sub_sub_array.LAST LOOP
                DBMS_OUTPUT.put_line("            .sub_sub_array('||j||'):');
                DBMS_OUTPUT.put_line('                .sub_sub_text_attr=""'||lr_complex_rec.sub_array(i).sub_sub_array(j).sub_sub_text_attr||'''');
                DBMS_OUTPUT.put_line('                .sub_sub_num_attr="||lr_complex_rec.sub_array(i).sub_sub_array(j).sub_sub_num_attr);
            END LOOP;
            l_str := lr_complex_rec.sub_array(i).sub_sub_hash.FIRST;
            WHILE l_str IS NOT NULL LOOP
                DBMS_OUTPUT.put_line("            .sub_sub_hash('''||l_str||'''):');
                DBMS_OUTPUT.put_line('                .sub_sub_text_attr=""'||lr_complex_rec.sub_array(i).sub_sub_hash(l_str).sub_sub_text_attr||'''');
                DBMS_OUTPUT.put_line('                .sub_sub_num_attr="||lr_complex_rec.sub_array(i).sub_sub_hash(l_str).sub_sub_num_attr);
                l_str := lr_complex_rec.sub_array(i).sub_sub_hash.NEXT(l_str);
            END LOOP;
        END LOOP;
        l_str := lr_complex_rec.sub_hash.FIRST;
        WHILE l_str IS NOT NULL LOOP
            DBMS_OUTPUT.put_line("        .sub_hash('''||l_str||'''):');
            DBMS_OUTPUT.put_line('            .sub_text_attr=""'||lr_complex_rec.sub_hash(l_str).sub_text_attr||'''');
            DBMS_OUTPUT.put_line('            .sub_num_attr="||lr_complex_rec.sub_hash(l_str).sub_num_attr);
            FOR j IN lr_complex_rec.sub_hash(l_str).sub_sub_array.FIRST..lr_complex_rec.sub_hash(l_str).sub_sub_array.LAST LOOP
                DBMS_OUTPUT.put_line("            .sub_sub_array('||j||'):');
                DBMS_OUTPUT.put_line('                .sub_sub_text_attr=""'||lr_complex_rec.sub_hash(l_str).sub_sub_array(j).sub_sub_text_attr||'''');
                DBMS_OUTPUT.put_line('                .sub_sub_num_attr="||lr_complex_rec.sub_hash(l_str).sub_sub_array(j).sub_sub_num_attr);
            END LOOP;
            l_str_2 := lr_complex_rec.sub_hash(l_str).sub_sub_hash.FIRST;
            WHILE l_str_2 IS NOT NULL LOOP
                DBMS_OUTPUT.put_line("            .sub_sub_hash('''||l_str_2||'''):');
                DBMS_OUTPUT.put_line('                .sub_sub_text_attr=""'||lr_complex_rec.sub_hash(l_str).sub_sub_hash(l_str_2).sub_sub_text_attr||'''');
                DBMS_OUTPUT.put_line('                .sub_sub_num_attr="||lr_complex_rec.sub_hash(l_str).sub_sub_hash(l_str_2).sub_sub_num_attr);
                l_str_2 := lr_complex_rec.sub_hash(l_str).sub_sub_hash.NEXT(l_str_2);
            END LOOP;
            l_str := lr_complex_rec.sub_hash.NEXT(l_str);
        END LOOP;
        DBMS_OUTPUT.new_line();
    END test_debug_vars;


    PROCEDURE put_dbms_output(a_clob CLOB)
    AS
        l_length INTEGER := 30000; --Кол-во символов в строке
        l_offset NUMBER := 1;
    BEGIN
        IF a_clob IS NOT NULL THEN
            LOOP EXIT WHEN l_offset > dbms_lob.getlength(a_clob);
                dbms_output.put_line(dbms_lob.substr( a_clob, l_length, l_offset));
                l_offset := l_offset + l_length;
            END LOOP;
        END IF;
    END put_dbms_output;


    FUNCTION test_clob
        ( a_clob    CLOB
        ) RETURN    CLOB
    AS
    BEGIN
        put_dbms_output(a_clob);
        RETURN a_clob||CHR(10)||"New Line';
    END test_clob;


    FUNCTION f2
        RETURN VARCHAR2
    AS
    BEGIN
        RETURN 'F_2';
    END f2;


    FUNCTION f3
        RETURN VARCHAR2
    AS
    BEGIN
        RETURN 'F_3';
    END f3;


    FUNCTION f1
        ( p1        VARCHAR2
        , p2        VARCHAR2
        ) RETURN    VARCHAR2
    AS
    BEGIN
        DBMS_OUTPUT.put_line('p1 = '||p1);
        DBMS_OUTPUT.put_line('p2 = '||p2);
        RETURN 'F_1';
    END f1;


    FUNCTION f0
        RETURN BOOLEAN
    AS
    BEGIN
        IF f1(p1 => f2(), p2 => f3()) = 'F_1' THEN
            RETURN TRUE;
        END IF;
        RETURN FALSE;
    END f0;


    FUNCTION div
        ( a         NUMBER
        , b         NUMBER
        ) RETURN    NUMBER
    AS
        l_res   NUMBER;
    BEGIN
        l_res := a / b;
        RETURN l_res;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line('div error: a="||a||" b='||b||'. Error: '||SQLERRM);
            RAISE;
    END div;


    FUNCTION get_order_rec
        ( a_id_client_order     INTEGER
        ) RETURN                T_CLIENT_ORDER%ROWTYPE
    AS
        lr_order    T_CLIENT_ORDER%ROWTYPE;
    BEGIN
        SELECT *
          INTO lr_order
          FROM t_client_order o
         WHERE o.id_client_order = a_id_client_order;

        RETURN lr_order;
    END get_order_rec;


    FUNCTION get_order
        ( a_id_client_order     INTEGER
        ) RETURN                VARCHAR2
    AS
        lr_order    T_CLIENT_ORDER%ROWTYPE;
        l_res       VARCHAR2(4000);
    BEGIN
        lr_order := get_order_rec(a_id_client_order => a_id_client_order);
        l_res := 'ID='||lr_order.id_client_order||'; NUM="'||lr_order.num||'"; COST='||TRIM(TO_CHAR(lr_order.cost, '9G999G990D00'))||'';

        RETURN l_res;
    END get_order;


    PROCEDURE update_order
        ( a_id_client_order     INTEGER
        , a_cost                NUMBER := NULL
        , a_text                VARCHAR2 := NULL
        )
    AS
    BEGIN
        UPDATE t_client_order o
           SET o.cost = NVL(a_cost, o.cost)
             , o.notes = a_text
         WHERE o.id_client_order = a_id_client_order;
        DBMS_OUTPUT.put_line(get_order(a_id_client_order => a_id_client_order));
    END update_order;


END PK_DEBUG_DEMO;
/

Links

Testing and Debugging Procedures with SQL Developer (www.oracle.com)

Plastic bag DBMS_DEBUG

Plastic bag DBMS_DEBUG_JDWP

Protocol Java Debug Wire Protocol

Parameter PLSQL_DEBUG

Debugging in TOAD Debugging PL/SQL Code with Toad for Oracle

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *