Oracle Repairman

Package dependencies

2007-09-14T05:37:00.000-07:00

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

2007-08-30T23:08:00.000-07:00

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

2007-08-29T22:56:00.000-07:00

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

2007-08-23T22:14:00.000-07:00

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

2007-08-06T22:07:00.000-07:00

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

2007-08-06T02:44:00.000-07:00

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

2007-07-31T22:59:00.000-07:00

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.

OTN forms forum, week 28

2007-07-15T23:17:00.000-07:00

As I was either away from home, studying, or too busy at work, my summary for the last week is kind of limited, maybe quality makes up for quantity ;)

* Delete table for a user in when_button_press

The solution works fine of course, but I still feel one should ask why someone would have such a requirement.

* Need some help?

A fun read on a very specific business requirement. Try coming up with a solution yourself before scrolling down to see what others came up with! :)

* Do form menus respect hierarchical roles?

A Forms limitation you might have stumbled upon yourself: due to the view Forms uses to determine the roles for a user, it fails to see nested or hierarchical roles. The solution is quite simple however.

OTN forms forum, week 27

2007-07-08T22:58:00.000-07:00

I kept an eye on the Oracle Forms forum on OTN last week, I'll probably get tired of it soon, but for what it's worth, here are some topics I found interesting. My personal wrap-up for week 27:

* How long is Oracle Forms/Developer Suite to be supported?

Should be reassuring for those who invest(ed) a lot of their time in the Forms technology, be it as a company or as a developer. If I got a euro for every time it's proclaimed dead, I could buy myself some new Oracle books. As stated, it's mostly the new trends in webdevelopment that toll the bell.

* François Degrelle's Forms look & feel project

While you're hanging on to Forms, a great way to upgrade your skills is integrating Java Beans and Pluggable Java Components. François Degrelle has been doing this for a while, it's worth your time checking out his blog.

* Bean to load image item from URL?

One simple example of the Forms & Java integration mentioned above.

* Any way to know the source table of a form at runtime?

I picked this one to point out the difference between where you query from (block_property QUERY_DATA_SOURCE_NAME) and where you write your data to (block property DML_DATA_TARGET_NAME). They don't have to be identical.

* Substitute for JInitiator

For those who think they're stuck to JInitiator to run forms on the web, this thread provides some links to installing the Sun JRE plugin as an alternative.

Coaching

2007-07-08T10:19:00.000-07:00

The last few weeks I spent a significant portion of my time coaching junior developers, which I enjoy a lot. I'm learning learning people :-) You have to pay attention to how you help someone. Just giving or showing them the solution won't get them any further when they encounter a similar problem.
Most of the time, I'll show the relevant section of the help files (in case of Forms) or the Oracle docs, hoping they'll pick it up as a problem-solving strategy.
I also try to ask questions in a certain direction, so they can 'give birth' to a solution themselves. If my terminology strikes you as odd, it refers to the maieutic method of Socrates.

R.S. Wurmans's book (in the currently reading section to the right) also has some interesting ideas on giving instructions. It's not the most recent, but certainly still up-to-date, and plenty of food for thought. If you reckon the original book came out at the start of the 90's, that's an amazing feat.

Going flat on your face

2007-07-05T03:28:00.000-07:00

It's been a while since I posted here, I've been too busy in the 'real' world searching/buying a house. We found a nice place and are probably moving by the end of summer.
There's a new 'story' on my technical blog, on PL/SQL AFTER triggers, you can read it here.

Oracle ADF for Forms & PL/SQL Developers

2007-05-15T22:28:00.000-07:00

As you can see, I added a page element to the right with the book(s) I'm currently reading. I bought Oracle JDeveloper 10g for Forms & PL/SQL Developers yesterday. Even though I'm still working on Forms projects, I think it's important to stay on top of the new technologies and get at least the basics of them down. Also on my wish list is the APEX book by Jon Scott and Scott Spendolini, it should be out next month, I can't wait.

I nearly finished my apex home accounting application, it just needs a report or two with some balance overviews. My next project will be a new website for our band. If I find the time, I'll also make the same projects with JDeveloper+ADF, could be interesting...

APEX and a list of Months

2007-05-07T12:30:00.000-07:00

My colleague Dimitri will be glad to hear I started experimenting with APEX again. I have been doing my home accounting in Excel for years, and it seemed like a nice project to try and build in APEX. As I wanted the data structure to be a little different, I didn't just import the excel sheets, but designed a new data model and started from there. I might still import the excels to have the old data available in Oracle as well, even write a small ETL routine to convert to the new model. For now I'm just having fun learning a new way to make client interfaces.

One of the (mainly SQL) 'issues' I had was creating a select list with all the months of a year, in a meaningful and efficient way. I used a combination of date formatting and a row generator, you can read more about that here.

Are you the smartest?

2007-05-03T22:15:00.000-07:00

I finished the last test of Oracle's "Are you the smartest?" contest yesterday, it was about SOA (Service Oriented Architecture), and it was hard...
For those of you who don't know what I'm talking about: AYTS is an annual contest organised by Oracle. It consists of attending four sessions of Sales Consultants presenting business solutions. This year's sessions were about BI Suite, JDeveloper & ADF, SOA Suite and Identity Management. I liked the BI and JDev sessions, the others were a case of "slow death by powerpoint" for me.

I liked the ease, transparancy and intuitive interface of the BI Suite. JDeveloper and ADF I had already worked with, and I'll admit it's an amazing and wide-ranging tool (for free!), but when it comes to building User Interfaces, my bets are on APEX. As for winning prizes (first price is a trip to Oracle Headquarters in SF, USA), I don't think I'll be among the lucky ones...

Extract

2007-04-30T14:53:00.000-07:00

I made a new entry in my technical blog on my 'discovery' of this SQL function. Check it out here.

Mind Mapping as an analyzing tool

2007-04-26T08:03:00.000-07:00

A few weeks ago I read an article by Kathy Sierra on Tablet PC's and Mind Mapping. I had only seen the technique used once before, by one of my colleagues, to organize the topics for a meeting. I thought it looked neat, but as attending or organizing meetings isn't one of my day-to-day tasks, it didn't stick to my mind.
Kathy Sierra however, is quite good at rousing enthousiasm, so I downloaded an open source mindmapper, FreeMind. I played around with it for a while, and then I got the idea to use it as an aid in extracting some structure out of a myriad of bussines rules summed up in Use Case documents. So far they've been a great help, seeing as on the project I'm working on now, there's a vast no man's land between the Use Cases and actual coding. Oh, and they look hip too. Too bad I can't post any screenshots, as the content is confidential, but there are plenty on FreeMind's Sourceforge page.

A Modest Destiny (Sean Howard)

2007-04-24T03:26:00.000-07:00

I'm a big fan of Sean Howard's pixel art comics. If you're into strong characters, witty humor and well-constructed plot twists, check out A Modest Destiny, a series already spanning four episodes. The pixel art is great too, I can't imagine the time and dedication it takes to make something like that. A lot of people compare it to the old Zelda and Mario Bros graphics, but it doesn't do him justice.

Oracle nr 30 in Brandz Top 100

2007-04-24T02:45:00.001-07:00

Millward Brown released its annual report ranking the top 100 of strongest brands measured by dollar value. Google shines at number 1, dethroning Microsoft. Oracle is on 30, SAP on 27. However, Oracle has a 28% growth in business value. It would be nice to have a more detailed analysis of this figure. I'm guessing the Fusion/Middleware catch-up is a significant factor.