Debugging the Backend through the Frontend Using Oracle PL/SQL

Debugging backend processes via a frontend application can be challenging, especially when we want to ensure connectivity and communication between the two parts. Utilizing Oracle PL/SQL for debugging offers a practical way to connect the functionality of the backend system with frontend applications, providing the ability to monitor the code and execute SQL commands in real time more effectively. Below, we outline the steps, tools and advantages of this approach—from system setup to the debugging process itself.

Why Debug the Backend through the Frontend Using PL/SQL?

Connecting backend debugging to the frontend can significantly accelerate the identification of errors and ensure data accuracy between client applications and the database. Some key advantages include:

Real-Time Interaction with the Database: Access to the database in real time enables direct query execution, data updates, and checks.

Improved Problem Detection: Debugging with PL/SQL through the frontend allows for quicker identification of errors.

Speed: This technique eliminates the need to fill in input fields received from the frontend in the backend, thereby speeding up the entire process.

Setting Up the System with Oracle Database

To start using Oracle Database for debugging, follow these steps:

Ensure Necessary Permissions: Check the ACL permissions for network settings. If your system is maintained by a DBA team, request the opening of debugging ports and the configuration of ACL packages.

Creating the Database Structure

In this example, we will use a simple table called “Directory” to illustrate the concept.

);

This table represents the basic structure for debugging and managing data through the frontend interface.

What Does a PL Package Look Like?

create or replace NONEDITIONABLE PACKAGE BODY phonebook_pkg IS

    phonebook phonebook_typ;

    procedure write_log(i_sqlerr in varchar2, i_sqlnmb in number) as

    begin

      insert into logiraj values (null,i_sqlerr,i_sqlnmb);

    end write_log ;

    PROCEDURE add_contact(

        p_name IN VARCHAR2,

        p_phone_number IN VARCHAR2

    ) IS

        contact phonebook_rec;

    BEGIN

        INSERT INTO phonebook (name, phone_number)

        VALUES (p_name, p_phone_number)

        RETURNING id INTO contact.id;

        contact.name := p_name;

        contact.phone_number := p_phone_number;

        phonebook(contact.id) := contact;

    END;

    function populate_grid return phonebook_pkg.phonebook_ref as

    c_ref phonebook_pkg.phonebook_ref;

    begin

    --------------------------

              begin

               DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',63946); -- the IP address of your computer    

              EXCEPTION

               WHEN OTHERS THEN

                 write_log(SQLERRM(-20000),sqlcode);

              end;

    ---------------------------

        open c_ref for

        select * from phonebook;

    ---------------------------

                begin

                   DBMS_DEBUG_JDWP.DISCONNECT(); -- the IP address of your computer     

                EXCEPTION

                   WHEN OTHERS THEN

                     write_log(SQLERRM(-20001),sqlcode);

                end;

    ---------------------------

        return c_ref;   

    end populate_grid ;

    FUNCTION get_contact(

        p_name IN VARCHAR2

    ) RETURN phonebook_pkg.phonebook_ref IS

        c_ref phonebook_pkg.phonebook_ref;

    BEGIN

    begin

       DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',63946); -- the IP address of your computer     

    EXCEPTION

       WHEN OTHERS THEN

         write_log(SQLERRM(-20000),sqlcode);

    end;

    open c_ref for

        SELECT *

        FROM phonebook

        WHERE lower(name) like '%'||lower(p_name)||'%';

        DBMS_DEBUG_JDWP.DISCONNECT();

        RETURN c_ref;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            RETURN null;

    END;

    FUNCTION get_contact1(

        p_name IN VARCHAR2

    ) RETURN NUMBER AS

    BEGIN

        RETURN 1;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            RETURN 0;

    END;

    PROCEDURE delete_contact(

        p_name IN VARCHAR2

    ) IS

        contact_id system.phonebook.id%type;

    BEGIN

        SELECT id INTO contact_id

        FROM system.phonebook

        WHERE name = p_name;

        DELETE FROM phonebook

        WHERE id = contact_id;

        phonebook.DELETE(contact_id);

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            NULL; -- Contact not found

    END;

END phonebook_pkg;

Setting Up the Frontend Application

For the frontend application, you can use C# with the Oracle Managed Data Access library, but any other language that supports Oracle data tools will also suffice.

Library: Use Oracle.ManagedDataAccess.Client from the .NET environment.
Interface: Create a simple interface for interacting with the database to display results obtained during debugging.

In the video file, we showcase the functionality of a simple phone book application that communicates with a PL/SQL database:
Video Link

Debugging in Visual Studio

A key part of this debugging involves using Visual Studio and SQL Developer as IDEs for setting breakpoints and monitoring the behavior of backend code:

1. Open the project in Visual Studio and set breakpoints at the points where calls to the backend are made.
2. Run the application in Debug mode.
3. The program will pause at each breakpoint, allowing you to inspect the state of variables and executed SQL queries.

For additional tracking, logging through the log4net or Serilog frameworks enables detailed monitoring of SQL calls and responses from the backend.

Debugging through Oracle PL/SQL

Let’s open the Log and input parameters into our backend application. We will compile for debugging, set breakpoints, and run the code in debug mode to analyze, troubleshoot, and review queries.

For a practical demonstration of what debugging looks like, you can watch this video: Debugging in Practice

How to Speed Up the Debugging Process on the Backend

To expedite the debugging process, it is essential to grant the necessary permissions on the schema we will be connecting to, if they have not already been provided. Since we are working in the system schema for this example, some permissions may be superfluous, but we will present them nonetheless.

GRANT DEBUG CONNECT SESSION TO "schema_name";

GRANT DEBUG ANY PROCEDURE TO "schema_name";

GRANT EXECUTE ON DBMS_DEBUG_JDWP TO "schema_name";

Configuring ACLs: Set permissions for hosts using DBMS_NETWORK_ACL_ADMIN, which allows access to local and remote computers.

BEGIN

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

        host => '127.0.0.1',

        ace => sys.xs$ace_type(

            privilege_list => sys.XS$NAME_LIST('JDWP'),

            principal_name => 'SYSTEM',

            principal_type => sys.XS_ACL.PTYPE_DB

        )

    );

END;

This package and function are used to manage host (computer) access to databases over the network.

1. (host=>'127.0.0.1', ...)
   Here, the host (computer) to which the ACL entry applies is specified. In this case, it is "127.0.0.1," which is the IP address of the local computer (localhost).
2. privilege_list=>sys.XS$NAME_LIST('JDWP')
   This defines the list of privileges assigned to the host. In this case, the host is granted JDWP (Java Debug Wire Protocol) permission, allowing the debugging of Java applications in the database.
3. principal_name=>'SYSTEM'
   Here, the username (principal_name) associated with this ACL entry is specified. In this case, the username is "SYSTEM."

This command is used for managing security and access control at the network level in the database, ensuring that only authorized hosts and users have access to specific functionalities and resources. In this particular example, it enables the debugging of Java applications on the local computer "127.0.0.1" for the user "SYSTEM."

Establishing and Terminating a Debug Connection

To enable remote debugging, establish a TCP/IP connection to Oracle Debugging JDWP using:

BEGIN

    DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1', 63946);

EXCEPTION

    WHEN OTHERS THEN

        write_log(SQLERRM(-20000), sqlcode);

END;

To terminate the connection after debugging, run the following command to stop the debugging session, ensuring that all processes are properly closed.

Starting Remote Debugging

In the remote debugging settings, specify the port (in our example, 63946) and the local address (e.g., 127.0.0.1). When the frontend reaches the breakpoint, Oracle listens on the specified port and establishes a connection between the frontend and backend for the debugging session.

How Remote Debugging Looks in Practice

• We start the debugging process on the frontend side.
• When we reach the part of the code that triggers actions towards the Oracle database, it listens on the specified port within the Remote Debug settings and automatically connects to the database.
• It is necessary to set a breakpoint below the code that establishes the connection to Oracle Debugging JDWP. (DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1', 63946)), where the debugging execution will pause.
• At this step, the incoming variables within the debug protocol contain values sent from the frontend to the database, significantly speeding up the process and reducing the time needed for error resolution, while also increasing the accuracy of the parameters entered from

You can see how this actually looks in real time here: Debugging in Practice.

Conclusion

Debugging backend processes through frontend applications using Oracle PL/SQL facilitates easier connections, insights, and real-time corrections in the code. Oracle's rights management system, PL/SQL structures, and JDWP debugging tools ensure high precision in tracking and optimizing data, enhancing the reliability of both frontend and backend systems. This approach helps us avoid potential errors in testing when entering parameters into the backend application and speeds up the troubleshooting process. In large applications with numerous input parameters, this method allows for significant accuracy and time savings in resolving bugs.