Here introduce the process to remote debug Oracle procedure. Will also solve common issues as well.
Debug Procedure
1. Firstly, you need to download and install latest Oracle SQL Developer from: https://www.oracle.com/database/sqldeveloper/technologies/download/
2. Then need to specify a port for remote Oracle database to callback. Use a fixed port is easier for your firewall configuration.
3. Launch SQL Developer, choose Settings at menu. Go to Debugger setting and config as below, you can choose other ports below 60000. Please follow all settings in red box, except the port you can customize.
4. Redirect traffic from the port to your machine. Configure the port forwarding at your home router:
5. Now you can use SQL developer connect to remote database and open a procedure for debug. Before start, you need to recompile this procedure for debug, right click on procedure and choose "Compile for Debug". It will show a bug logo on the name.
6. Then add some break point in your procedure by double click at the left of code line.
7. Use "Debug..." function to run the procedure. It will promp you to input your IP, use your public IP.
8. You will see some connect progress in debug log
9. Once it hit the break point, your code will stop and highlight the current line. You can find regular control buttons at top and there are some useful panels at below.
This is the happy ending of the story, if you have any issue, below section may helps.
Trouble shooting
Debug Hung ORA-30683, ORA-12535, ORA-06512 DBMS_DEBUG_JDWP
If SQL Developer no response after launch debug process, and show following errors in log. It may caused by the compatibility issue with remote server. Solution is use DBMS_DEBUG_JDWP, no worries, we already using this mode.
https://stackoverflow.com/questions/22324939/pl-sql-developer-debugging-hangs-on-certain-code
Cannot execute procedure ORA-06512, ORA-01846
You need to keep SQL Developer charset match remote server.
Related settings are:
Cannot switch to debug layout
You can manually switch to debug layout by
Cannot get DBMS output
Try this command in your procedure, it also allows to set output cache size:
dbms_output.enable(1000000);In case you do not know how to connect DBMS output in SQL Developer:
文章评论