Something to Whet the Appetite: Sneak Peak 1

Have you been to the SQL Developer home page on OTN recently? Did you spot the Statement of Direction? We have just released this document on OTN and hope it'll give you an idea of the features planned for next few releases.

A statement of direction typically does not drill down to the finer level of the features, so as I was tracking forum issues and the progress of bugs and enhancements logged in the latest developer build, I thought you might be interested in a sneak peak at a few little bits you've been asking for. You won't see these until SQL Developer 1.1, and I'm not a great fan of 'vaporware', so I won't do too many of these, but in the next few weeks I'll do a few little blog entries to show a few things to look out for in SQL Developer 1.1.

Take a look at the Table Definition Tabs:

The illustration shows one of the most frequently requested features, which is to be able to sort on column headers. This feature is throughout the product now on any grid of data, whether it's instance data or the object definitions. Notice that once you've clicked on the column header, in this case Column Name, the direction of the sort is also indicated.

(Just click on the image for a larger view)

The second illustration shows another frequently asked for feature, which is to display a single record on a form layout. This is particularly useful if you have great chunks of text that you need to read.

More later this week.

Reporting with SQL Developer

Know nothing about the Reports available in SQL Developer? Want to know how to create drill down reports? Oracle Ace Lewis Cunningham (blogs on ITtoolbox ) has written and published a great article about all the reports you can run using SQL Developer. He also goes on to show you how to create your own reports, even your own drill down reports and he gets into the nitty gritty of fixing the titles of parameters for your user defined reports that require parameter screens.

For a few weeks this article will be easily accessible from the front page of OTN, after that we'll link to the article from the SQL Developer home page, so you'll be sure to continue to have access to this detail.

Remote Debugging with SQL Developer

It occurs to me that while we talk about using the remote debug facility in SQL Developer that you may not know how to use it. So in a few steps and with a few screen shots, I'd like to show you how it's done.
(Please note, to see any of the images clearly, just double click on them.)
1. Let's start with a connection to the database. Create a database connection. (File -> New Connection) and complete the details. I only use the basic tab, so don't need to set up tnsnames or anything else. You'll see the database is on my own machine in this example, but it need not be.




















2. Now you can connect to any objects this user owns, using SQL Developer. You can browse the various objects the user has access to. I'm only interested in this procedure, which my user HR owns.














3. You can run and debug this procedure in SQL Developer, but that's not the purpose of the exercise. What we want to do is debug the procedure when it is executed from elsewhere, such as another programme or application. The starting point is to start a remote debug session in SQL Developer. You do this from the Database Connection as shown.














4. A dialog will display, requesting the listening port number and the IP address of the machine running SQL Developer - effectively it's your computer, listening for the database to connect and using that port range to do so. You can set the range of ports through a Preference in SQL Developer. If you run into issues, look out for firewalls between the machines, blocking these ports.













5. Once you have set the remote debug details, you should see the run manager display these.











6. Now you should start a remote session. Using a SQL *Plus command line session will do.

* Invoke SQL *Plus for this user
* In the SQL *Plus session enter the following command:
exec DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', 4000 );

You should recognize the parameters from the previous dialog.

If you are debugging an application remotely, then this PL/SQL procedural call will need to go into that application, just for the debug purposes. You'll remove it afterwards.

7. Return to SQL Developer and set a breakpoint in the procedure.
Note: If you are debugging a procedure, you must remember to Compile for Debug, before you can start debugging.














8. Now you need to return to the SQL*Plus session and execute your procedure. If you debug in SQL Developer, an anonymous block is created for you to execute the procedure. In this case you'll need to write one to execute the procedure from SQL*Plus.

Note: In the procedure we have a DBMS_OUTPUT command, so you should also add the 'Set Serveroutput on' command.

9. As soon as you execute the anonymous block, you'll be returned to SQL Developer to debug the session there. Step into the code as you would in a usual debug session.
















10. You can watch data and modify values in the same way as a normal debug session.

In this example, I'll modify the hire date.

11. Once you have reviewed the data, or made the modifications you want, resume debugging. Once the debug session is complete the control is returned to your remote session. In this case, SQL*Plus. You'll notice the modified date reflected in the output.












Quite a long piece, I know, but I think we often veer away from the unknown or untried. Hopefully once you have walked through an example, you might be able to make more use of this very useful feature.