Debugging PL/SQL package with global temporary table (GTT)

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often these temporary data store are defined using Oracle Global Temporary Tables(GTT). The data inserted in global temporary table is private and can only be accessed by that session. If you have a java program calling a PL/SQL package which uses GTT's then it makes it difficult to debug. Here are some steps that can be followed.

1. Access the package in SQL developer



2. Right click the package and select debug option
 
 3. Then the Debug PL/SQL block appears as follows


Note that in this example the package TEST_SAMPLE_SQL has only one function. If it has multiple functions then it will appear in the target section. If there are multiple function, select the function which has GTT usage that you want to debug.

4.  Copy the PL/SQL block into clipboard. (By first clicking on the PL/SQL block section and then CTRL+A, CTRL+C)




5. Now open another SQL worksheet by right clicking on the DB connection


6. Now paste the content from the clipboard (CTRL-V) and make some modification

• Un-comment the dbms.output statements
• Assign the input variables that are getting set from the calling program

Before:

After:


7. Display DBMS output in sql developer unless it is already open by selecting View-> Dbms Output


Then the DBMS output window will be displayed as follows


8. Enable DBMS output for the session by clicking the green + sign and selecting the connection




9. Now execute the package call in SQL developer using Run Statement(). Then it will ask for entering values for the bind variables


10.  Click on Apply. It will display the successful completion of PL/SQL block




and display the DBMS output as follows


Additional DBMS.OUTPUT messages can be added to the function/procedure to display debugging information.
11. Now in this SQL worksheet window, you will have access to the GTT table. You can run queries from GTT's as normal tables.

Once the activity with GTT is complete, please make sure to rollback the changes using Rollback button()

Happy Debugging