Question

REST Debug


Userlevel 2
Badge +8

Hi,

 

Please give me some advice on how to debug effectively the PL/SQL side of an incoming OData REST webservice request when the input is a complex structure.

 

As long as there are some simple parameters to the request, it is fine, I can specify them in the test window of PL/SQL Developer.

However, it can be quite painful to build a complex structure (like several customer orders and their lines and their child rows) in PL/SQL before calling the request.

 

Ways I tried:

  • Remote debug with Oracle SQL Developer → could not get through the error: ‘insufficient privileges’ for DBMS_DEBUG_JDWP package (I granted everything and added ACLs)
  • Tried to find within IFS how the conversion of JSON to Oracle types are done - that way I could copy the JSON part of the request to the test window of PL/SQL Developer, where I would convert it to the proper Oracle record type that is defined in My…._SVC package and pass it to the the appropriate method in My..._SVC package

Any recommendation is welcome.

Thanks 
Janos

 


This topic has been closed for comments

13 replies

Userlevel 6
Badge +12

Janos,

Clarification -- do you have the debugger working in SQL Developer or not? You mention using the test window of SQL Developer already, but later it sounds like the debugger is not working? 

Getting the debugger working can be tricky, but is doable, especially with more recent versions of SQL Developer. Have you trued checking the box “Use DBMS_DEBUG” in the Debugger preferences of SQL Developer? That uses a different methodology than the JDWP option. If you still have privilege issues, do a search on Oracle debugger and it should tell you the GRANTs you need to make for debugging to work.

That all being said, it would be great to be able to set a break somehow in the debugger and then run live IFS (Aurena or IEE) and be able to step through. I just don’t think that is possible given the debugger is linked to the current session.

Good luck,

JoeK

Userlevel 2
Badge +8
 

JoeK,

Thanks for reply.

Debugging the way I described above in SQL Developer did not work for me, because of the ‘insufficient….’ error.

Thanks for the suggestion regarding newer version sof SQL Developer and DBMS_DEBUG, if no other options work I will try that.

I did an extensive search on this error and granted all privileges the Oracle docs listed and still got the error.

Regarding your thoughts on remote debugging, it is definitely possible, see:
https://docs.ifs.com/techdocs/Foundation1/050_development/050_development_tools/002_developer_studio/030_reference/320_pl_sql_debugger/010_debug_from_e_e_client.htm

 

 

Userlevel 6
Badge +12

Janos,

Thanks for that link! I will try to get that working! First obstacle, though, is that “Remote Debug” is greyed out in the IEE debugger (under “Advanced” button), so I cannot attach to the debugger process. Any ideas on why that would be? Is that an IFS permission set issue (I even tried logged in as the App Owner, and it still is greyed out)? I have debugging working from SQL Developer, and it looks like I can follow all the required steps in IFS Developer Studio.

The GRANTs I used to get debugging working are these:

  • GRANT CREATE SESSION, CREATE PROCEDURE TO <schema>
  • GRANT DEBUG CONNECT SESSION TO <schema>
  • GRANT DEBUG ANY PROCEDURE TO <schema>
  • GRANT EXCUTE ON DBMS_DEBUG_JDWP TO <schema>

Once that was done I started getting an ACL error:

Connecting to the database <user>@<env>.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '<Host IP>', '<Port>')
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database <user>@<env>.

To get the ACL set up correctly to allow access, you can check existing ACLs with this statement (requires DBA privileges): 

SELECT * FROM dba_network_acls;

The following is a procedure that adds ACLs for a range of IP addresses:

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
host => '10.10.*.*',
lower_port => null,
upper_port => null,
ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
principal_name => '<schema>',
principal_type => xs_acl.ptype_db)
);
END;

The above allows debug communication on the subnet 10.10.*.* (which is my company’s LAN range of IP addresses).

If you are stuck on “insufficient privilege”, though, you are not to the ACL problem yet, I don’t think.

The other DEBUG mode supposedly does away with JDWP and the ACL issue, though I found it to be slower to connect and more finicky on maintaining a connection.

Once you get debugging working, though, you should be all set? Or would you still have the issue of not being able to attach to the same debug process from Aurena (like you theoretically can from IEE)?

Hope this helps, and I will keep monitoring this thread. Debugging is essential!

 

Thanks,

JoeK

Userlevel 2
Badge +8

JoeK,

Remote Debug option in IEE is greyed out for me too, actually I never used it, I was just happy to see it in the docs that it should be possible.

On the other hand it says in the docs:

Remote Debugging

Prerequisites:

  • Grant role FND_DEVELOPER to user IFSSYS.
     
    Note: The application server will most likely require a restart for the changes to take place.

So this might help :)

 

And back to the main issue:

 

I have been already able to execute DBMS_DEBUG_JDWP.CONNECT_TCP from a separate anonymous PL/SQL Block with the setting you listed above too, that is good and necessary.

To problem comes when I post my REST request and it tries to execute PL/SQL code in my custom ..._SVC package where I added  the DBMS_DEBUG_JDWP.CONNECT_TCP statement at the beginning of my PL/SQL method which performs the action behind the REST operation.

This call is not from Aurena , this is part of a Webservice customization and I make the actual test call from ‘Postman’.

There I still get ‘Insufficient privileges….’.

Oracle user is IFSSYS, Fnd user is IFSAPP. I granted all the above to them.

So this is where I am stuck now ….

 

Userlevel 6
Badge +12

Janos,

Thank you for the follow-up! I added that GRANT to IFSSYS, then restarted MWS, and even reconfigured MWS on that server. I did not restart the server itself, so if that is what they are talking about it will have to wait until the next server maintenance reboot. In any case -- “Remote Debug” is still greyed out.

It could still be a permissions issue, and could still be the same permission issue you are having, for all we know. Have you tried the FND_DEVELOPER grant on IFSSYS, then reconfigured MWS and reboot the server? Not sure why some of these GRANTS require a restart, but if they do, it might help you out. It sounds like you are sort of setting up your own remote debug session via code, so maybe it isn’t working for the same reason “Remote Debug” is greyed out in the IEE Debugger…

Permissions are the cause of a lot of disabled menus and cryptic Oracle errors, so there could easily be one or two more grants required…

 

Thanks,

JoeK

Userlevel 2
Badge +8

JoeK,

Thanks for trying to solve this ‘greyed out Remoted Debug’ problem, I see that that could be very useful, but at the moment it has actually marginal importance to me only.
The main thing is the ‘Insufficient privileges..’ error on DBMS_DEBUG_JDWP.CONNECT_TCP when being called in a webservice implementation by a third party tool (Postman) as I wrote above.
Therefore I am not that keen on testing this FND_DEVELOPER related stuff :)
Do have any more ideas on the ‘Insufficient pivileges...’ error?

Thanks
Janos

Userlevel 6
Badge +12

Janos,

My thought is that the greyed-out option could be the same “insufficient privilege” issue behind the scenes, which is why I mentioned it one last time.

I do not have any other ideas at the moment beyond asking for help from IFS. When it comes to privilege issues, I do not know how to determine which permission (lacking) is causing the issue. I am not entirely sure what user would even be lacking the privilege at that point? App Owner? IFSSYS? I suppose you could start granting DBA privileges to various usernames and see if that helps?

 

Thanks,

JoeK

Userlevel 2
Badge +8

JoeK,

Ok, I got it now, it makes sense what you said regarding the correlation between my problem and Remote Debug.
Actually I have now managed to enable the ‘Remote Debug’ option in Debug Console. In my case it seems the missing point was enabling remote debug in screen ‘System Settings’ and logout/login.
However, it did not affect the main problem, I still get ‘insufficient privileges’ when calling my webservice.
I think I will report this problem to IFS.
Thanks for help! :)
Janos

Userlevel 6
Badge +12

Janos,

I hope IFS can help, because you have helped me a ton! I am now almost there, but when I start a remote-debug session and try to perform some activity in IEE I get the following popup:\

 

 

Any idea what schema needs to be granted that role, and does it mean a Permission Set in IEE or a GRANT in SQL Developer? I will play around with it and follow up, but was wondering if you had run into this.

Thanks so much for your help, sorry this thread turned out to be more help for everyone else but you!

 

Thanks,

JoeK

 

Userlevel 2
Badge +8

JoeK,

No worries, I got wiser from this thread too :)

I granted FND_DEVELOPER to both IFSSYS and IFSAPP to be on the safe side. It is a predefined permission set in IEE, though I granted it directly in Oracle. And I also restarted the MWS (probably not necessary), and did a logout+login in IEE (probably necessary).
One more thing:
I tested this remote debugging afterwards with Developer Studio and IEE first with a small package, ISO_COUNTRY_API and it worked fine. Then I thought maybe it solves a long-lasting problem of big Oracle packages, namely that neither PL/SQL Developer no Oracle SQL Developer can show values of variables during debugging, because of the big size of the package. So I tested CUSTOMER_ORDER_LINE_API which is one of those packages in IFS, and unfortunately debugging did not work there with this remote debug feature at all, I did everything the same way I did with ISO_COUNTRY_API and it still did not stop at my breakpoints :-(
So good luck to you, please let me know if you find something interesting! :)

Thanks
Janos

Userlevel 6
Badge +12

Never mind, I just needed to use the App Owner login, and everything worked! Live, remote debugging!

One also has to be sure to use a host value that matches the ACL access set up. In my case, I could not use localhost as the Host in the Remote Debug session, I had to use my actual IP address to match the subnet mask of 10.10.*.*.

Thank you so much for your help! Again, I hope IFS comes through on what you are trying to do, as you will truly be the debug master if you can get that working!

 

Thanks,

JoeK

Userlevel 6
Badge +12

JoeK,

No worries, I got wiser from this thread too :)

I granted FND_DEVELOPER to both IFSSYS and IFSAPP to be on the safe side. It is a predefined permission set in IEE, though I granted it directly in Oracle. And I also restarted the MWS (probably not necessary), and did a logout+login in IEE (probably necessary).
One more thing:
I tested this remote debugging afterwards with Developer Studio and IEE first with a small package, ISO_COUNTRY_API and it worked fine. Then I thought maybe it solves a long-lasting problem of big Oracle packages, namely that neither PL/SQL Developer no Oracle SQL Developer can show values of variables during debugging, because of the big size of the package. So I tested CUSTOMER_ORDER_LINE_API which is one of those packages in IFS, and unfortunately debugging did not work there with this remote debug feature at all, I did everything the same way I did with ISO_COUNTRY_API and it still did not stop at my breakpoints :-(
So good luck to you, please let me know if you find something interesting! :)

Thanks
Janos

 

Janos,

I have run into the “won’t stop at breakpoints” in only one place (in SQL Developer), but good to confirm it is based on size (and not just me losing my mind). I have not run across the issue of watching variables not working, just that none of my breakpoints worked. It was a pretty big package, IIRC. Sounds like the IFS Developer Studio might not help when I run across ornery packages, but it sure is fun to step through “live” executing code!

I doubt I will find anything more interesting than what you have already put on display on this thread, but I will post if I do. I hope IFS helps you out!

 

Thanks,

JoeK

Userlevel 2
Badge +8

Thanks and have fun! :)