SQL Developer

I've been trying to use bind variables in the master report of master/detail reports with multiple detail reports; but, I've been getting a problem with the detail reports not displaying data.

Here's a sample sequence of events:

1. Select the master/detail report under User Defined Reports.
2. Connect to a DB (if necessary).
3. Enter any value(s) for the bind(s) for the master report.
4. Select a row in the master report.
5. The currently selected detail report displays column names and data.
6. Select a different detail report.
7. Nothing displays except column names.
8. Select a different detail report.
9. Nothing displays except column names.
10. Click the currently selected row in the master report.
11. Now the current selected detail report displays (column names and) data.

I have found that if, I am more selective with my bind variable names, I can get things to work as expected.

First, let me show an example of some SQL that was giving me problems.
Master Report:

select distinct OWNER
from SYS.ALL_OBJECTS
where (OWNER like :OWNER or :OWNER is NULL)

Detail Reports:

select *
from SYS.ALL_OBJECTS
where OWNER = :OWNER
and OBJECT_TYPE = 'INDEX'

replacing 'INDEX' with other values for the other detail reports.
(I did have this problem with other SQL too but hopefully this SQL will run on everyone's DB.)

The first solution I found was to change the name of the bind in the master to something like OWNER_BIND.

The second solution I found was to change the master to

select distinct OWNER as POWNER

and change the detail reports to

where OWNER = :POWNER

Another solution might be to adjust the bind variable names' case, but I didn't look at that.

While I was trying different things to get this to work, one of the goofy things that SQL Developer did was add <binds>s to the <query>s of the detail reports in my UserReports.xml. IIRC, it even did this to a master/detail report that I was not editing. So, if you run into this problem, you might want to open your UserReports.xml and clean it up by hand. (Actually, that might be good advice for other problems too.)

Tip: I've had other problems with my User Reports such as disappearing reports; so, not only do I regularly make copies my UserReports.xml, I also save report(s) into separate XML files and use Tools > Preferences... > Database > User Defined Extensions to add REPORT-type extensions to SQL Developer. (Kris Rice talked a bit about REPORT-type extensions in this blog entry: http://krisrice.blogspot.com/2006/11/xml-extension-points-reports.html)

FYI: I'm using sqldeveloper-1.1.2.2579 on Windows XP w/ SP2. The DB is Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production.