When developing an application, you may want to see what's actually happening on page processing or running some PL/SQL code. Especially PL/SQL code that is stored in the database and called from a page within APEX, is hard to debug. However, Oracle SQL Developer offers a way to debug PL/SQL code which is called from APEX. We will show you how to do that.
It is important to have the latest version of SQL Developer on your computer. And that you have an application with a page that calls a PL/SQL procedure in the database. In this recipe, we take the Twitter search from Chapter 1. We will call the app_search_user
procedure.
Furthermore, you need to grant some privileges:
grant debug any procedure to <user>; grant debug connect session to <user>;
[1346_10_10.txt]
Here,<user>
is the user that needs the privileges. If you use the embedded PL/SQL gateway, use ANONYMOUS
, otherwise use APEX_PUBLIC_USER
.
If you are running APEX on Oracle 11g database, you also need to create an Access Control List (ACL) and its privileges.
begin dbms_network_acl_admin.create_acl (acl => 'acl_anm.xml' ,description => 'Description' ,principal => 'ANONYMOUS' ,is_grant => true ,privilege => 'connect' ,start_date => null ,end_date => null); -- DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'acl_anm.xml' ,principal => 'ANONYMOUS' ,is_grant => true ,privilege => 'resolve'), -- DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'acl_anm.xml' ,host => '127.0.0.1' ,lower_port => 4000 ,upper_port => 4000); -- commit; end; / begin dbms_network_acl_admin.create_acl (acl => 'acl_db.xml' ,description => 'Description' ,principal => '<dbusr>' ,is_grant => true ,privilege => 'connect' ,start_date => null ,end_date => null); -- DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'acl_db.xml' ,principal => '<dbusr>' ,is_grant => true ,privilege => 'resolve'), -- DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'acl_db.xml' ,host => '*'), -- commit; end; /
[1346_10_11.txt]
The code creates an ACL for the ANONYMOUS
user and the database user who is the owner of the procedure, marked with<dbusr>
. Replace it with your own database user (schema owner). The ACL for the ANONYMOUS user gives that user the privilege to connect to the localhost at port 4000, the port we will use for the remote debug session. To keep it simple, we create an ACL for the owner of the procedure with the privilege to connect to any host.
APP_SEARCH_USER
procedure.The procedure is now ready to be compiled. We will now create a page in APEX with a text item and a button and when the button is pressed, APEX will start the debug session.
P_TWITTERNAME
. Click Next. P_TWITTERNAME
and click Next. chk_twt
. Click Next.dbms_debug_jdwp.connect_tcp('127.0.0.1',4000); declare l_result varchar2(100); begin app_search_user('TWITTERUSER','password',:P_TWITTERNAME,l_result); if l_result = 'user found' then return null; else return 'false'; end if; end; dbms_debug_jdwp.disconnect;
[1346_10_12.txt]
The code starts with the call to DBMS_DEBUG_JDWP.CONNECT_TCP
to initiate the connection to the debug session. The call carries two arguments: the local address and the port number we had to enter when starting the debug session in SQL Developer. After that, the code calls the procedure APP_SEARCH_USER
with the arguments Twitter username, password, search argument and the result. The search argument is kept in the item P_TWITTERNAME
. If the result of the call is positive (The entered Twittername exists), a null value is returned, otherwise, the text 'false' is returned. The code ends with the call to dbms_debug_jdwp.disconnect
, which disconnects the session.
The page is ready. Run the page. Enter some text in the twittername field and click check name. The debug session is started and APEX gives control to SQL Developer. You can go to SQL Developer to debug the code.
In the debug mode, you have several ways to run the code, such as step over, step into, and resume to next breakpoint. At the bottom of the screen you can click the data tab to see the several variables and arguments that show their value at that moment. When you reach the end of the code after step over, step into, or resume, SQL Developer stops and gives control back to APEX. APEX resumes like it would normally do.
Actually this is not really an APEX feature. You can make use of the dbms_debug_jdwp.connect_tcp
and dbms_debug_jdwp.disconnect
in SQLPlus as well. When using the Embedded PL/SQL Gateway you just have to make sure that the ANONYMOUS
user has the right privileges, just like any other user that tries to remote debug in SQLPlus. When you are using another setup with Apache/MOD_PLSQL for example, it will probably connect to the database using APEX_PUBLIC_USER
. In that case, the privileges should be granted to that user instead.
18.118.163.250