Roels Blog

From the 11g New Features Guide: "New in 11.1 is the ability to mark a PL/SQL function to indicate that its result should be cached to allow lookup, rather than recalculation, on the next access when the same parameter values are called. This function result cache saves significant space and time. Oracle does this transparently using the input actuals as the lookup key. The cache is system-wide so that all distinct sessions invoking the function benefits. If the result for a given set of actuals changes, you can use constructs to invalidate the cache entry so that it will be properly recalculated on the next access. This feature is especially useful when the function returns a value that is calculated from data selected from schema-level tables. For such uses, the invalidation constructs are simple and declarative. Concurrent, multi-user applications that use this feature experience better response times. Applications that implement a session-private scheme consume significantly l...

My company took part in the Oracle 11 g Beta test and I also ran some (small) test on the new PL/SQL features. One of them is that access to sequences is allowed in PL/SQL, so no need to "SELECT tst_seq.nextval INTO n FROM dual" anymore! DECLARE   n NUMBER; BEGIN   SELECT seq.nextval   INTO n   FROM dual; END; can now be coded as DECLARE   n NUMBER; BEGIN   n := seq.nextval; END; For "currval" the same solution is possible. So there is no need for a cursor anymore, but more important to me : the usability and readability of the PL/SQL is code is enhanced by this new feature.

In SQL Developer v1.2 the trigger tab for views is missing. You can add this tab (and many many others you can think of) by using User Defined Extensions. Create an XML file with the definition of the tab: <?xml version="1.0" encoding="UTF-8"?> <items>   <item type="editor" node="ViewNode" vertical="true">     <title><![CDATA[Triggers]]></title>     <query>       <sql>         <![CDATA[select table_owner                 , trigger_name                 , trigger_type                 , triggering_event       ...

Yesterday the design environment (JDev) for WebCenter Suite was released. After the demo by Thomas Kurian at OOW2006 I was very curious about this new product. The WebCenter Suite is the first result of the Fusion stack: The Oracle Fusion developers needed it to build the Fusion apps. But now we can use it too! The WebCenter Framework (the runtime environment) will be available any day now. Using the Framework you can use out-of-the-box WebCenter Services, like Content Management, Search, Presense Server, Instant Messaging, Wiki and Forums. WebCenter supports building applications by putting small blocks of UI together. So after modular development in PL/SQL (what every developer should do), and modular development of services - using SOA - we can now develop modular UI blocks and put these all together to build a even more user friendly process driven work environment for the end-user. I hope I can spend some time in the near future to try out this new product. The development team is...

Today I discovered that Firefox crashes when I started up an Oracle Forms application using JInitiator. One thing I noticed that two JVM's were started (two icons showed up in the taskbar). After switching off Java inside Firefox (via Tools->Options->Content) everything works fine. So no need for IE anymore...

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!