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:
- Install Oracle Database: Download and install it from the Oracle Database website.
- For local installation, use administrative privileges and save the login credentials.
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.
CREATE TABLE SYSTEM.PHONEBOOK (
ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
NAME VARCHAR2(100 BYTE),
PHONE_NUMBER VARCHAR2(20 BYTE)
);
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:
- Open the project in Visual Studio and set breakpoints at the points where calls to the backend are made.
- Run the application in Debug mode.
- 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.
- (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).
- 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.
- 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.
