Debugging an APEX application remotely

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.

Getting ready

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.

How to do it...

  1. Start Oracle SQL Developer.
  2. Right-click on the database connection where the procedure is stored.
    How to do it...
  3. Select Remote Debug.
  4. A pop-up is shown with three text fields: Port, timeout, and local address.
  5. Choose a port number that is not yet assigned to any process and is not blocked by a firewall. You need this port number later.
  6. Enter a number of seconds in the timeout field to indicate how long the debug session must be kept open before it automatically closes. Enter 0 to leave the session open without closing. You can leave the local address field empty.
    How to do it...
  7. Click OK to start the debug session.
  8. Click the database connection open and go to the APP_SEARCH_USER procedure.
  9. Right click the procedure and select edit. The procedure is now open for editing.
  10. Set some breakpoints by clicking the line number in the gutter.
  11. When you are ready, click the compile for debug icon.

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.

  1. Go to APEX, go to the application builder, and select the application that you want to edit.
  2. Click the Create Page button.
  3. Select blank page.
  4. Click Next.
  5. Enter a name and a title for the page. For example. 'Check Twittername'. Click Next.
  6. Click Next.
  7. Click Finish.
  8. Click the edit page icon.
  9. In the regions section, click the add icon to create a new region.
  10. Select HTML.
  11. Click Next.
  12. Enter a title for the region, for example check. Click the Create button.
  13. In the items section, click the add icon to create a new item.
  14. Select text.
  15. Select text field. Click Next.
  16. Enter a name for the item, for example, P_TWITTERNAME. Click Next.
  17. Click Next.
  18. In the buttons section, click the add icon to create a new button.
  19. Click Next.
  20. Click Next.
  21. Enter a name for the button, for example Check name. Click Next.
  22. Click Next.
  23. Click Next.
  24. Click Create button.
  25. Click the create item button.
  26. In the validations section, click the add icon to create a new validation.
  27. Select item level validation and click Next.
  28. Select the item P_TWITTERNAME and click Next.
  29. Select PL/SQL.
  30. Select Function returning error text and click Next.
  31. Enter a name for the validation, for example chk_twt. Click Next.
  32. In the validation text area, enter the following code:
    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.

  1. In the error message text area, enter an error message, like 'User not found'. Click Next.
  2. In the when button pressed list box, select the CHECK_NAME button.
  3. Click the Create button.

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.

How to do it...

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.

How it works

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.15.55.18