Subscribe to Jornica feed Jornica
He's a nice guy but doesn't get paid for it
Updated: 2 weeks 6 days ago

Remote debugging Code Tester

Tue, 2007-07-24 12:04

Quest Code Tester for Oracle (Code Tester) helps you with defining test cases, generating test harnesses and presenting the test results in a structured way. Code Tester does not provide any features to debug your code. If you run into a red light situation when a test case fails, you have discover where the error is located. This means checking inputs and outcomes in order to exclude incorrect setup and incorrect initialization code. And of course checking your code, recompiling your (test) code and login again.

If this does not result into a green light, it is time to debug your code with a development IDE. As a result you have to transfer your test code into your development IDE. Wouldn't it be nice if you could enter debug mode seamlessly: when executing your test case the execution stops at a breakpoint and you can debug your code. The answer is: yes, you can. With SQL Developer you can remote debug your code within a test run.

The linking pin between Code Tester and SQL Developer is the package sys.dbms_debug_jdwp where jdwp stands for Java Debug Wire Protocol. This protocols needs a debugger process i.e. SQL Developer and a debuggee process i.e. Code Tester. The debugger listens for requests from the debuggee i.e. PL/SQL package procedure calls to connect_tcp and disconnect.

Setting up the debuggee
In Code Tester you have to modify the initialization section of your test case. Add the following code
dbms_debug_jdwp.connect_tcp(host => '', port => 4000);
The first parameter is your IP address of the client where the Code Tester IDE runs (as seen from the database server you're connected to). Because I'm running Code Tester and Oracle XE on the same machine, I use the local host address The second parameter is the default port. An alternative for the hard coded IP address is SYS_CONTEXT ('USERENV', 'IP_ADDRESS').

After the test case is executed, switch off remote debugging. Add the following code to the cleanup section:

Setting up the debugger
In SQL Developer login with the same user as Code Tester and right click on your connection, a context menu appears and select the 'Remote Debug' option. A small window with the title 'Debugger - Listen for the JPDA' (Java Platform Debugger Architecture) appears, enter the address or host name where SQL Developer should listen to connect. Use the same IP address as in dbms_debug_jdwp.connect_tcp. Also check if the port is the same.

Before switching to Code Tester again set a breakpoint in your code (and compile your code for debug) otherwise the debugger will not stop at your breakpoint. At last but not at least make sure the user has the debug connect session privilege and the debug any procedure when debugging other users objects.

Debugger meets debuggee
It is time to run your test with Code Tester. SQL Developer will stop on your breakpoint. Note: while debugging your code Code Tester will not respond. After stepping through your code press the resume button in SQL Developer to return to Code Tester. As an example I modified the code of the normal usage test case of the function qctod#betwnstr (see for an explanation and the source code How Quest Code Tester for Oracle can help you get rid of bugs in your PL/SQL procedures). In the picture below SQL Developer hits the breakpoint at line 13. In the data tab you can see all variables and their values.

This example shows how to use remote debugging with Code Tester. Instead of using SQL Developer as debugger you can also use Toad for Oracle or Jdeveloper. And also every front end can be used as debuggee for remote debugging as long the calls to dbms_debug_jdwp can be implemented. Let's start debugging!

I found the following links usefully while writing this blog entry:

Returning into clause and post statement triggers

Fri, 2007-06-01 17:04

If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

To show this behavior, set up a small test case first. A table t1 with one column c1 with an after insert statement trigger, modifying the c1 column.


Table created.

4 SET c1 = 'TRIGGER';
5 END;
6 /

Trigger created.

Insert a row in t1 and return the new value of c1 into bind variable c1.

2 /

1 row created.

And inspect the contents of the table and the value of the bind variable.

SQL> SELECT c1 "column",
2 :c1 "bind"
3 FROM t1
4 /

column bind
---------- ----------

1 row selected.

As expected the value of column c1 is TRIGGER. The value is the bind variable is SQLPLUS. This value is set before the after statement trigger starts.

Although this code is not production code, you might notice this behavior in production code as well: I discovered this behavior hidden in a mutating table workaround.

APEX reports and SQL Developer

Sun, 2007-05-13 08:08

Both Kris Rice and Dimitri Gieles blogged about new APEX reports in SQL Developer 1.1.3 . Minor detail: those reports are build for Application Express version 3.0.1 , which is not released yet.

I installed the new version of SQL Developer, and tried to run the APEX reports (location in the reports tree: All reports, Data dictionary Reports, Application Express). Surprisingly, I did not get the message Application Express 3.0.1 or higher required. In a few cases I received an error: ORA-00904 invalid identifier.

To find the SQL behind the reports a simple copy and paste of the report did not work i.e select the Application report, right click and select copy. Select the User Defined Reports, right click and paste from, nothing happened. Even export of the report(s) did not work.

After some searching I found the report SQL hidden in a file: (location sqldeveloper\extentions directory). This .jar file includes an apex30.xml, this file contains all the APEX reports in XML format (you can unzip the XML file with an unzip utility). The next step is to import the apex30.xml and then you will see all the reports in the User Defined Reports branche as well.

Changing the SQL is left as an exercise to the reader... ;-)

Multi value selectors

Wed, 2007-04-18 00:36

APEX had three multi value selectors: check box, multi select and list manager. APEX 3.0 introduces a new multi value selector: the shuttle. An shuttle consists of two text areas with buttons between the the area's to move items back and forth. At the right side, the selection, there are buttons to change the order of the items in the right text area. All selectors are based on a list of values. Setting or getting values of these selectors can be done with colon separated strings i.e. 'Violet:Indigo:Blue'.

The shuttle and list manager also preserve the select order as you can see in the picture. For each selector the same values in the same order are selected: first Violet and second Indigo. As you can see in the text boxes the shuttle and list manager preserve selection order in contrary with check box and multi select. The picture shown here is a modified version of on-line demo Item Types.

Enumeration types in PL/SQL?

Thu, 2007-03-22 02:11

In the book Oracle PL/SQL for DBAs the definition of the type BOOLEAN is shown:

FROM all_source
WHERE owner = 'SYS'
AND type = 'PACKAGE'
AND line <5


/********** Types and subtypes, do not reorder **********/

4 rows selected.

Have you seen that kind of type definition before? I did, it looks like a Delphi enumeration type when you replace the is with = . Let's define our own type.

create package TRAFFIC is 

Warning: Package created with compilation errors.


2,19 PLS-00505: User Defined Types may only be defined
as PLSQL Tables or Records
2,3 PL/SQL: Declaration ignored

2 rows selected

Perhaps this is an enhancement request for Oracle 11g release 2?