Helpful Tips

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

2016-08-09T00:27:00.005-07:00

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

Audit Trail in Web Application

2016-08-08T22:36:00.003-07:00

Background:

Generally Web applications simply authenticate users (using LDAP) to the middle tier and the middle tier connects to the database as a single generic user. This model works as long as the end user identity isn't required in the back end database. But identity propagation has become increasingly important for both regulatory compliance and overall security. This document gives some ways of implementing it.

Solution:

The most common method used by existing multi-tier applications is to pass a client identifier from the middle tier to the backend database across an existing generic DB connection. Client identifiers were introduced in Oracle Database 9i to enable an application to pass an identifier across an existing thick or thin JDBC connection from the middle tier to the backend database. When the Oracle database auditing is turned on globally, nothing is audited by default. The DBA has to explicitly define the table and operations that need to be audited.

For e.g. tables/operations audited:

EMPLOYEE_DETAILS: INSERT/UPDATE/DELETE

Once this is done, when some inserts or updates happen on those tables, the DBA can verify the audit trail as follows.

select owner, obj_name, username, userhost, timestamp, action_name, client_id, ses_actions, returncode from sys.dba_audit_object where obj_name in ( 'EMPLOYEE_DETAILS') order by timestamp desc;

The client_id field will contain application end user or whatever identifier was passed in.

Option 1:

To set the client Identifier for JDBC applications, use the following oracle.jdbc.OracleConnection interface methods setClientIdentifier() : Sets the client identifier for a connection. But this method is deprecated since 10.1.0.x

Option 2:

A workaround to this above method is to use the API setEndToEndMetrics

String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "junittest";

((OracleConnection) getConnection()).setEndToEndMetrics(metrics, (short) 0);

These two methods works fine in a standalone/JUnit environment. But when the application is deployed in OAS, the above API does not work as OAS picks up the jar ojdbc14dms.jar by default. So in order to use the jdbc jar without DMS*, the following parameter needs to be used at the server startup -Xbootclasspath/a:<path>/ojdbc14.jar.

*[Oracle DMS(Dynamic Monitoring Service) provides a set of API'S to measure and report performance metrics, status, information, and diagnostic information. To use DMS in the application, the application needs to use these DMS API's where it wants to measure the performance.]

Option 3:

To have the client_id in a deployed application, the following code can be used everytime we get a connection.

CallableStatement cstmt = getConnection().prepareCall(

"begin DBMS_SESSION.SET_IDENTIFIER(?); end;");

cstmt.setString(1, getClientContext().getUser().getUserName());

cstmt.executeUpdate();

This approach has a little overhead as for every connection we are calling a procedure. This options can be wrapped in a system property like –Denable.audit.trail= true so that client can enable/disable it.

Debugging PL-SQL calls from Java Session Using Eclipse and SQL Developer

2011-01-14T11:00:00.000-08:00

Suppose I have a java class PriceInquiryPLSQLOperation which is calling a PL/SQL package PRICE_INQUIRY_SQL.GET_PRICE_DETAILS(?, ?, ?). Currently while debugging some issue, the developer can only debug till the java call from java side and debug the PL/SQL call separately. After reading this document you will be able to debug the PL/SQL package using eclipse and SQL Developer from the same java session.

Before you start debugging your package

Open the package in Oracle SQL developer. Right click both body and spec and compile for DEBUG.

If the package is using some user defined type objects as input or output then compile those objects for DEBUG.
Go to tools -> Preferences -> Debugger. Select the option Prompt for Debugger host for Database Debugging.

Right click the DB connection and start the remote debug session.

It will open the Listen for JPDA dialog. In Local address enter the ip address of the machine and hit OK.

This will start the debug listener as follows.

Now in the java program calling the PL/SQL package before the actual PL/SQL call enter the following code

//For debugging only

CallableStatement debugStatement = getCallableStatement("begin DBMS_DEBUG_JDWP.CONNECT_TCP( '<ipaddress provided above>', 4000 ); end;"); //Note the ipaddress and port – Same as the values provided in the previous dialog.

debugStatement.execute();

// execute the actual PL/SQL call

statement.execute();

Now open the PL/SQL package and put a break point where you want to debug something.

When you invoke the PL/SQL program from Java

It will stop at the first break point in PL/SQL as follows. Note that you can see the values passed by Java in the user defined object. The objects appearing as opaque needs to be recompiled for debug. Now you can debug through each line in PL/SQL much like java code.

Enjoy – Happy PL/SQL Debugging. Let me know if there is any question.