Roels Blog

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!