Skip to Main Content
  • Questions
  • Remote Debug with SQL Developer from Apex - "ORA-01031: insufficient privileges”

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Victor.

Asked: June 11, 2019 - 4:53 pm UTC

Last updated: June 18, 2019 - 3:18 am UTC

Version: 5.1.0.00.45

Viewed 1000+ times

You Asked

Hi Tom,
I'm facing a problem when trying to use remote debug feature in SQL Developer, I appended an ACE as recommended and granted execution permissions on the DBMS_DEBUG_JDWP package for my app schema MY_SCHEMA and the Apex Schema as well.

BEGIN
      dbms_network_acl_admin.append_host_ace(
            host=>'*.mydomain.com',
            ace=> sys.xs$ace_type( 
                  privilege_list=>sys.XS$NAME_LIST('JDWP'),
                  principal_name=>'MY_SCHEMA', --Appended also for APEX_050100
                  principal_type=>sys.XS_ACL.PTYPE_DB
            ) 
      );
      COMMIT;
END;


So, when I start listening on SQL Developer I can connect and remote debug from another session of SQL*Plus or maybe Toad without issues. But for some reason it's not working directly from an APEX application, even when the application is pointing to one of the schemas I granted access, for example MY_SCHEMA. I always get "ORA-01031: insufficient privileges” error.

--Remote debug test
DECLARE
    lv_source VARCHAR2(500);
BEGIN
    DBMS_DEBUG_JDWP.CONNECT_TCP('MyPCName.mydomain.com', 4000);
    lv_source := PKG_MYPACKAGE.AnyFunction('XXYYZZ');
    DBMS_DEBUG_JDWP.DISCONNECT;
END;


I've been trying to find the root cause for a while but no success. Also in internet there is not enough information, so no idea what's going on. Any help or clue will be appreciated, thanks.

I'm using:
APEX 5.1.0.00.45
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

and Connor said...

Jeff Smith did a full set of presentation slides to walkthrough this just recently.

https://www.thatjeffsmith.com/archive/2019/05/debugging-pl-sql-in-apex/

See how you go with that.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Unfortunately not working

Victor Gtz, June 26, 2019 - 8:49 pm UTC

Hi Connor,
The presentation is a high level, where all the setup such package permissions, ACL creation, firewall port permission, etc. is ready, and is not mentioning remote debugging from Apex.

Actually as previously said, I can remote debug from SQL*Plus, Toad for Oracle or another machine with SQL Developer. The problem comes when trying to do the same from an apex application. Then the permission error raises.

Before there wasn't any problem when using Oracle 11g, but now with Oracle 12c something changed, additional setup seems to be pending but not sure what.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library