Package dependencies

There are multiple ways to track the dependencies between objects in an Oracle database, and I won't go into those here.
But what I'd really like (or even need) is a tool that shows the dependencies between procedures and functions within a package body. Not just the simple one-level tree-view you see in all PL/SQL IDE's, but something that shows the package procedure and functions and what functions/procedures (within the packages) they, in their turn, call, as tree nodes:

SOME_PACKAGE

|- PROCEDURE do_general_stuff
   +- PROCEDURE do_this_first
      +- FUNCTION get_that_over_there
      |- PROCEDURE delete_leftovers
|- FUNCTION is_that_so
|- PROCEDURE some_other_things

In fact, this would be such a great help in programming modularly I imagine there should be tools like this already, I just can't find them. So if you have any hints or tips, I'd like to hear them...

Loops - wtf

Ponder this for a moment (on Oracle 9i):

begin
    for r_x in (select * from x)
    loop
        for r_y in (select * from y where x_id = r_x.id)
        loop
            for r_z in (select * from z where y_id = r_y.id)
            loop
                -- some code ...
            end loop;
        end loop;
    end loop;
end;

Thinking that it was too obvious, I spent ten confused minutes wondering what could've been the idea behind it. When I inquired with the creator, the succinct "I thought this was more readable code" landed me back in reality.

In case you're wondering, I prefer:

select  * 
from    x
join    y on x.id = y.x_id
join    z on y.id = z.y_id;

Oracle Beta Exam: 1Z1-047 SQL Expert

By mere chance, I came across the Oracle Beta exams, and as it's most relevant for my day job, I took a glance at the the SQL Expert exam. Some thoughts on that:

• (+) It's good to see they included some of the 'newer' SQL features, like Merge, Group By extensions, hierarchical queries, even Regular Expressions! (I know some of these are not so new, but ok...)
• (-) Nothing on Analytic Functions?
• (-) Nothing on Oracle Text Searches?
• (-) There's still too much basic SQL in it, like multiple-column subqueries, manipulating data (just to name two).

I'm just reflecting on the topics mentioned, I have no clue about the content (Laurent Schneider took the beta exam, if you want a first hand opinion). To me it looks like half of it is introduction to SQL, and only half of it is 'Expert' stuff (too generous, or too harsh?). When it's past beta, I'll probably take the exam, but I'd still like to see something more worthy of the name "Expert"...

A native list of months

A while ago I blogged about making a list of months in an (APEX) application, for which I used a query with connect by. I got some feedback on that, and Gunnar Andersson informed me that such lists are actually provided with APEX as views. For example:

Select * from WWV_FLOW_MONTHS_MON;

MONTH_DISPLAY        MONTH_VALUE
-------------------- ----------------------
Jan 1
Feb 2
Mrt 3
Apr 4
Mei 5
Jun 6
Jul 7
Aug 8
Sep 9
Okt 10
Nov 11
Dec 12

12 rows selected

The same goes for
WWV_FLOW_HOURS_12
WWV_FLOW_HOURS_24
WWV_FLOW_MINUTES
WWV_FLOW_MINUTES_5
...
I was curious as to how Oracle defined these views, if they used the same connect by "trick", so I drilled down the DDL.

select  dbms_metadata.get_ddl( 'VIEW', 
                               'WWV_FLOW_MONTHS_MON', 
                               'FLOWS_030000'
                             ) as the_ddl
from    dual;

THE_DDL
----------------------
select "MONTH_DISPLAY","MONTH_VALUE" 
from   wwv_flow_months_mon_temp 
where  month_value < 13;

Seems I'll have to dig a little deeper:

select  dbms_metadata.get_ddl( 'VIEW', 
                               'WWV_FLOW_MONTHS_MON_TEMP', 
                               'FLOWS_030000'
                             ) as the_ddl
from    dual;

THE_DDL
----------------------
select to_char(to_date(to_char(rownum,'00'),'MM'),'Mon') month, 
       rownum value 
from   wwv_flow_dual100
where  rownum < 13
union all
select '                    ', 
       20 
from   dual;

I don't really get why the "union all" is there, but anyway: like its name suggests, WWV_FLOW_DUAL100 is actually a table with one dummy column and a 100 rows with numbers for values. No fancy SQL, and probably more efficient.

Mimicing Forms with ADF BC & ADF Swing

If you are a Forms Developer and you're into Java/Oracle ADF/JDeveloper, there's a great new Oracle White Paper by Grant Ronald, where he demonstrates mimicing the Forms look with ADF Business Components and ADF Swing.
Considering that Swing is even closer to the Forms look and (development) feel than JSF, this might be a better introduction to J2EE than the book by Mills & Koletzke (cf. the image on the right). Which is to say, for a J2EE novice like myself, I tought the JSF architecture was a bit daunting at first.

OTN Forms forum, week 30 and 31

Some interesting stuff the last two weeks. The amount of noob questions on the forums used to put me off in the past, but I have to admit there's a lot of quality posts and ideas on the Forms forum. Now I wish I had more time to try everything that seems worthwhile :) Anyway, here's my list:

Zdebug -- Download a Forms debugging message tool

A very small but very handy tool for debugging your forms, by Steve Cosner. It uses a substitute for the message built-in and captures all those messages in a recordgroup. By including an extra button in your own form, you can call the debug form, which displays all the captured messages. It also supports getting serveroutput (dbms_output). Simple, efficient, practical!

Implementing JFreeCharts instead of FormsGraph.jar?

An unanswered question I as well would like to know the answer to. If you happen to know a solution, post it in the forum, pretty please...

Search a string in all .fmb in a directory

Not really an issue on Unix or Linux, but if you use Windows as an OS (my condolences), WinGrep is the app you need.

Using Enter-Query Mode Programmatically

A workaround to hitting the Enter Query and Execute Query buttons every time the user wants to perform a search. The user can stay in Normal mode, enter a search criterium in one of the fields, and Execute the search. The subsequent discussion is definitely worth reading too.

Regular Expressions in Oracle 10g database

As I was on holiday in Toulouse (France), I'll have to skip a week or two of my OTN Forms forum posts. However, while cleaning up my browser bookmarks, I found some gems of the SQL and PL/SQL forum I thought I'd share. Last year, user "cd" wrote an excellent introduction on using regular expressions in the Oracle 10g database. You can find the series here:
Part 1
Part 2
Part 3

If you haven't got a clue about what regular expressions are, make the effort of trying out the examples, you'll find they are very powerful and can make your programming life a lot easier. If you prefer your reading material to be portable, you could try the Oracle Regular Expressions Pocket Reference by Jonathan Gennick.