Roels Blog

Assume we have a table of locations like Name Null? Type ----------------------------------------- -------- ----------------- ID NOT NULL NUMBER NAME NOT NULL VARCHAR2(50) and a table of possible lines (or route parts) like Name Null? Type ----------------------------------------- -------- -------- ID NOT NULL NUMBER FROM_LOC_ID NOT NULL NUMBER TO_LOC_ID NOT NULL NUMBER DISTANCE NOT NULL NUMBER How can we calculate the best route (in this case: the route with the shortest distance) from a startpoint to an endpoint? The answer is: By using an hierarchical query: select dep_name , arr_name , route , distance , execute(distance) total from ( select connect_by_root dep.id dep_id , conn...

On November 9th my employer, LogicaCMG, organised it’s own version of Oracle Open World, but just a little bit smaller. About 130 colleagues came to Amstelveen to listen to the keynote (after a good dinner) and after that three times a choice from one of the three parallel sessions. As usual the evening was very well organised (thank you committee!), but there was some experience as it was already the 7th version of this annual event. The keynote speaker was Rob Blaauboer who enlightened us about Innov8 (the 8 should be the mathematical infinite sign, but I can’t find that one on my keyboard), a method to visualize what technologies a company should adopt or look in to in the nearer or farther future. The first parallel session I visited was Oracle2Go by Reza and Robert. Oracle2GO is a framework as an extension of the ADF Framework, to develop applications faster and with higher quality. They mentioned a couple of success stories and proudly pointed out that they managed to achieve a ...

Due to a pleasant evening on Wednesday, I had to skip the first session (at 8:00!). But at the start of the second session, Test-Driven Development in the World of PL/SQL by PL/SQL Evangelist Steven Feuerstein, I was quite awake. Steven demonstrated - in his own special interesting way : what a great presenter! - the advantages of Test Driven Development. The message: Specify your testcases and build your test program before you even start building the program-to-test! . He also announced and demonstrated Quest Code Tester, what looks like a great tool to support PL/SQL unit tests. I surely will try this out soon. Available for download at ToadWorld , free until the end of February. After that one I went to Peter Koletzke for a session with the - rather long title Oracle JDeveloper 10g with Oracle ADF Faces and Oracle JHeadstart: Is it Oracle Forms Yet? . He pointed out the similarities and dissimilarities between Oracle Forms and JDeveloper - with and without JHeadstart. His conclusi...

Wednesday was Keynote Day. First in line was John Wookey, who emphasized the continuity of the complete Oracle Applications stack and pointed out a couple of new features and enhancements in the future releases of Peoplesoft, JD Edwards, Siebel and the E-Business Suite. In this timeslot Giovanni Contino of Ducati got 15 minutes to present himself as an "happy customer". The two Ducati motorbikes that drove through the hall were rather cool. After this break John continued with the four key values of the new features of the products: Search: A Google look-a-like search engine to search through structured and unstructured enterprise data. XML based reporting: Using XML Publisher to create good looking reports. Role based analytics: Presenting exact the information the user needs given his role. Sustainable Integration: Creating a layer above all the different applications, so the end users doesn't even know what application he's using. Then he showed a Sneak Previ...

On this Tuesday I spent the whole day at the Hilton (only 1,5 block from our hotel) attending sessions from the Developer track. The day started of with the Developer keynote of the day : Tom Kyte with Things you think you know . The message was that things (read "Oracle software") changes over time and with every Oracle update your knowledge needs an update too. The quote of the day : "It ain't so much the things we don't know that get us into trouble. It's the things you know that just ain't so or just ain’t so anymore or just ain’t always so." This great presentation (especially if it's presented by the master himself) is also downloadable from asktom . You can get an impression by watching the first 3 minutes on video from Eddy Awad's site . The second session of the day was Oracle BPEL Process Manager Performance and High Availability by Francis Ip and Randy Stafford. This was the only session at OOW where I misjudged my knowledge (o...

For me this Monday started with an (Developer) Keynote by Thomas Kurian: The next application platform . Thomas pointed out three main trends : SOA, Information Driven Architecture an Grid Computing Architecture. For the developers (about 1200 in the Grand Ballroom of the Hilton) he mentioned the tools Oracle offers for building applications on the 3-tier architecture (of course: JDev, SOA Suite and SQL Developer). The most important announcement was the availabilty of the Oracle Developer Depot , were you can easily download an install Java applications to facilitate code reuse and simplify the development process. Of course you can upload your work to this comunity. You can even win a meet and greet with Larry (or an HD TV) if your software is selected as "the best". For the next session a headed over to Moscone for The Future of DB Technology by Andy Mendelsohn. He addressed (a.o.) the next interesting new products / features / options : Information Lifecycle Management H...

For me OOW started on Sunday morning with the ODTUG Oracle Developer Suite Special Interest Group Meeting in the Hilton. In this session 2 Oracle Emps of the Forms and Designer Development Depts answered questions of the audience. The highlights : The main goals for Forms 11 are : better Application Server integration; higher interoperability (with webservices, BPEL and browser); ease of upgrade. JInitiator will be replaced by (native) JVM WebUtil will be a part of Forms (not a seperate utility anymore) Forms PL/SQL scanner that checks the quality of the code No plans for extra UI widgets Oracle Applications is migrating to Forms 10gR2, so Forms is here to stay XML Publisher is the standard reporting tool in Applications (you can imagine the consequences for the future of Oracle Reports) Designer 11 will not contain any new functionality, just a new version to keep up with Forms Of course no time frame for the release has been given. At 6:00 pm Charles Phillips had the honour of kicki...

Back in the office it’s hard to imagine that it was only a week ago that I was in San Francisco for the “greatest Oracle show on earth”. So many people (over 40,000), such huge session rooms at the Moscone (at least 200 attendees could fit in), 7 really big screens in the keynote arena with impressive audio visual performances…etc, etc. Golden Gate Bridge And every hour these 40,000 people moved their way from one room to another - as if you would evacuate a small city! And still they managed to feed all these attendees without creating long waiting lines. What a great logistic performance. Especially when about 25,000 people where moved to and from the Cow Palace on Tuesday night to watch Sir Elton J. Alas there were a few drawbacks. The most irritating thing was that a lot of people were so important (at least that’s what they were thinking), that during sessions they kept their cell phones on with really loud ringtones. And even dared to pick up the phone when it rang and talked out...

As other bloggers I also publish my OOW2006 schedule. It was hard to choose between 70 parallel sessions, but IMHO I succeeded in planning a nice schedule: I'll arrive on Friday, so on Saturday I have one day to recover from the jetlag and visit some interesting sites in San Francisco (also plan a schedule for this touristic part of my visit). Sunday ODTUG Oracle Developer Suite (Forms and Reports) Special Interest Group Meeting I am a user of some of the ODTUG mailing lists. I hope to meet a couple of the people I read posts from for a couple of years. IOUG Oracle Application Express (APEX) Special Interest Group Meeting There is - sadly - very little about APEX during OOW and I'll plan to visit most of the sessions with APEX in the title. Build a Dynamic Menu Framework with Oracle Application Express And this is also one of the APEX-sessions Monday Developing PL/SQL Programs, Using Automated Unit Testing by my English colleague Andrew Clarke - I'm looking forwar to meet h...

Just fiddling around with my ApEx (2.2) installation on XE I tried to add a Webservice. So I used the Web Service wizard and choose Yes in "Search the UDDI registry" and clicked Next. In the next screen I selected (the only available) "xMethods UDDI v2" and clicked Next. The I searched for "stock". The next screen showed " ORA-20001: The webservice was unreachable because either the URL you supplied was invalid, or your environment requires a valid proxy server address for HTTP requests. ". So I added the proxy server under the Application Attributes Definitions and followed the same steps as above. The search for "stock" returns " ORA-20001: The response from the UDDI registry was invalid. " this time. Looking at the Apex and XE forums didn't return any valuable hits. The I switched (from FireFox) to IE. This time the error told me something more: "the proxy server required authentication", so I added my usernam...

As you might have noticed APEX 2.2 is available for download. On the download site nowhere is stated that is doesn’t work for on XE, but installing on XE fails directly. How to solve this? This is due to the following lines in the upgrade procedure in the file apexins.sql: declare t_edition varchar2(30); edition_is_xe exception; begin --select edition into t_edition from v$instance; execute immediate 'select edition from v$instance' into t_edition; if nvl(t_edition,'x') = 'XE' then dbms_output.put_line('---------------------------------'); dbms_output.put_line('- Ap Ex cant be installed in Express Edition.-'); dbms_output.put_line('-------------------------------------'); raise edition_is_xe; end if; exception when edition_is_xe then raise; when others then null; -- no edition column, not xe end; / Once the lines are REM’med out the upgrade process runs fine. After finishing the other st...

If there is a requirement to produce output from an application into Excel, you would probably create a CSV (Comma Separated File) with the data and start Excel to show the data - at least that's what I did...until now. The drawback of this solution is that you could only produce data and no nice layout. But Excel is also capable of opening HTML-files and using this you could create Excel files with data and magnificent layout! Let me give an example: 1. Create a procedure to show the data in formatted in an HTML table. CREATE OR REPLACE PROCEDURE display_emp_list IS v_emp_count NUMBER(5); v_empno NUMBER(8); v_ename VARCHAR2(50); v_job emp.job%TYPE; v_sal emp.sal%TYPE; v_bg_color VARCHAR2(10) := ''; CURSOR c_emp IS SELECT empno, initcap(ename), job, sal FROM emp ORDER BY ename; BEGIN SELECT COUNT(*) INTO v_emp_count FROM emp; owa_util.mime_header('application/ms-excel', FALSE); htp.p('Content...

I found a nice blog where the future of Forms is described. Click here for the complete article.

Allways wanted to spell out numbers ? The most simple solution is using a SQL date (!) format mask: SQL>select to_char( to_date( 12345, 'J'), 'JSP') Words from dual; WORDS ---------------------------------------- TWELVE THOUSAND THREE HUNDRED FORTY-FIVE There is one drawback : it works only with integers between 1 and 5373484..

Despite Belgium is not qualified for entering the World Cup Soccer in Germany, Dimitri Gielis found the time to create a tool for entering and betting the scores for the games. All created using APEX and the result is very good looking! Click here to see what he has accomplished!

On this page you can find two fine extensions on SQL Developer. With ErrorsLookup4Raptor a small entry field is added to the toolbar where you can enter an Oracle error code. The cause and action of the error will be displayed. Saves a lot of time searching through manuals! The other extension is Insider4Raptor, a graphic tool that shows all your database activity and traffic. Looks a lot like SQL Monitor from Quest, but a lot cheaper (like free Laughing). Take a look at this screendump (in real life there are a lot of "moving dots" and it looks even more fancy!) Try it out yourself!

Last week we encoutered the problem that reading a 10,000 record csv-file took 45 minutes from a location in Belgium. From The Netherlands it took about 20 minutes. The Application Server and Database Server is located in the UK. The difference is due to available band width. The performance from both locations were not acceptable. How did we solve this problem? First we stripped down the code so that only the lines in the csv-file were read and no validation and processing (insert into table) took place. From Belgium only this took even 17 minutes! Then we changed the code so that the file got transferred to the Application Server (using webutil_file_transfer) and replaced the use of the webutil package client_text_io with 'regular' text_io. The results were astonishing: The transfer of the file is done in a split second and just reading the file line-by-line took less than a minute! After adding the validation and processing the process could finish in just 2 minutes. So a li...

Let's consider the contents of the famous dual table: SQL>select * from dual; D - X SQL>select rownum from dual; ROWNUM ---------- 1 Now let's try to get multiple rows returned form dual: SQL>select rownum from dual connect by rownum ROWNUM ---------- 1 So there's still one row, whichs results in a rownum of 1. Now let's ask the max of rownum: SQL>select max(rownum) from dual connect by rownum MAX(ROWNUM) ----------- 5 Kinda strange isn't it? Now let's try to get these 5 rows: SQL>select * from (select rownum from dual connect by rownum ROWNUM ---------- 1 2 3 4 5 Another strange feature is that in TOAD select rownum from dual connect by rownum returns 5 rows (and in SQL*Plus only one as you can see above). Does this kind of behaviour also occurs on other tables/views? In my application I've got one materialized view, so a select from all_mviews returns one ro...

Object Types are available since Oracle 8i, but just recently I encountered a situation were they seemed to be usefull. Picture this: We have a product that has a certain size (length, width and heigth) of its own and a certain size when packed into a box. Ofcourse we could model this by repeating the length-, width- and heigth-attributes for the two sizes, but (mostly) the same checks are liable for both sizes. And this 'size' is also used in other tables. So my idea was : defining an Object Type could be usefull here! So here we start, first create the Object Type "cbm_type": SQL>create or replace 2 type cbm_type as object 3 ( length number(6,2) 4 , width number(6,2) 5 , heigth number(6,2) 6 ) 7 / Type created. Now create the table (I defined cbm as not null, because the length, width and height should always be known): SQL>create table product 2 ( 3 description varchar2(30) not null, 4 cbm cbm_type not null 5 ) 6 / Table created. Now add some data: SQL>ins...

After more than 2 years of functional and technical design and build (and test of course) the project I'm working on comes to an end. We started with almost nothing and yet the customer has an fully operational Oracle Webforms application that is used in more than 800 locations. The HR systems consists of different modules as Employees, Contracts, Salaries (and other payments), Illness, Education. We build interfaces for getting distances between locations (via webservices), communicating with external parties (using XML) and with a payment system. We used Oracle Designer 9i, Oracle Forms, Headstart and ofcourse Oracle RDBMS 9i. I had a fun time doing this and now moving on to the next challenge (for the same customer) : a supply chain management project.

This is the first entry to my blog. Hopefully more te follow!