Tuesday, March 16, 2010

SQL Developer - Search Source Code - now including Views!

I am attempting to promote Oracle's SQL Developer as a standard PL/SQL development tool, in order to reduce current spending on licences for Quest TOAD and SQL Navigator, within our organisation.
Most of my colleagues have managed to move across smoothly, with a few minor issues that will probably see us holding on to a number of TOAD licences for the heavy-users, while the majority should be able to work happily and productively with SQL Developer.
On of the main points of contention in the migration is the in-place editing of SQL results, but Oracle have acknowledged that request and should be fitting it into some future release.

A fellow developer wanted to know if the Search Source Code report in SQL Developer included Trigger and View source. So I did a little bit of checking around and saw that Trigger code *was* included, but View code was not. So, off I went to see if anyone had created and published an extension that did what we wanted. A quick look at SQL Developer Exchange brought back nothing, so I set about to create my own.

The tricky thing about Views (see all_views or user_views) is that the source is held as a LONG datatype, which doesn't play well with others. In this instance, I wanted to UNION the results from the standard Search Source Code output (which returns source lines as a VARCHAR2) with the View source, as well as finding the location of the Search criteria within the View source. Clearly a LONG and a VARCHAR2 will not UNION, nor can you SUBSTR or INSTR a LONG, so I had to implement a conversion of some sort.

According to Tom Kyte you can easily write a wrapper PL/SQL function to do that for you, but I am lazy (and didn't want to be placing conversion functions in multiple databases) so I began looking for a pre-existing routine that would do that for me. DBMS_METADATA to the rescue!

Using DBMS_METADATA.GET_DDL gave me the full creation source for the View object, which was just great. But it returned a CLOB, which was not so great. Luckily DBMS_LOB gives us a SUBSTR method which would suit my needs, for display purposes. So, using these DBMS built-ins together, I now had the means to create a user-defined script for including View source within the Search Source Code report.

And here is the final result, which I have placed up on SQL Developer Exchange: (Right-click and Save As)