Weblog for the AMIS Technology corner
Archive for July, 2007
New in Oracle 11g: PL/SQL Function Result Cache
Jul 31st
A new feature in the Oracle 11g database is the ‘Function Result Cache’. When you create a function with the new clause ‘RESULT_CACHE’ the result of the function is stored in the cache for each parameter value it is called with. When the function is called again with the same parameter the result is retrieved from the cache instead of determined again. And of course, this can be much faster.
A small example to demonstrate this.
Read the rest of this entry »
New in Oracle 11g: the FOLLOWS Clause in Create Trigger Statement
Jul 30th
The order in which triggers of the same type fire is arbitrary. But in Oracle 11g the create trigger statement has a FOLLOWS clause. With the FOLLOWS clause you can specify after which other trigger of the same type the trigger should fire. For example, if you have two triggers, testa and testb, on table test and you want trigger testb to fire after testa, then you can create the triggers as follows: Read the rest of this entry »
Searching the AMIS Weblog directly from your Firefox searchbar
Jul 29th
Why bother using Google if you know you will find the answer on this blog anyway? It would be a nice feature if you could search directly from your Firefox searchbar then, right?
Alex just pointed out he already created a plugin before me: http://technology.amis.nl/blog/?p=764
I made one small improvement, that is I embedded the image in a single XML file.
The contents of the file is like this:
<SearchPlugin xmlns="http://www.mozilla.org/2006/browser/search/">
<ShortName>AMIS Weblog</ShortName>
<Description>Find articles on Oracle, Java, database- and projectmanagement</Description>
<InputEncoding>utf-8</InputEncoding>
<Image width="16" height="16">data:image/jpeg,%FF%D8%FF%E0%00%10JFIF%00%01%01%01%00H%00H%00%00%FF%DB%00C%00%0A%07%07%08%07%06%0A%08%08%08%0B%0A%0A%0B%0E%18%10%0E%0D%0D%0E%1D%15%16%11%18%23%1F%25%24%22%1F%22!%26%2B7%2F%26)4)!%220A149%3B%3E%3E%3E%25.DIC%3CH7%3D%3E%3B%FF%DB%00C%01%0A%0B%0B%0E%0D%0E%1C%10%10%1C%3B(%22(%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%3B%FF%C2%00%11%08%00%0F%00%0F%03%01%22%00%02%11%01%03%11%01%FF%C4%00%17%00%00%03%01%00%00%00%00%00%00%00%00%00%00%00%00%00%00%02%03%04%FF%C4%00%15%01%01%01%00%00%00%00%00%00%00%00%00%00%00%00%00%00%03%05%FF%DA%00%0C%03%01%00%02%10%03%10%00%00%01%B1%9D%0E%97%FF%C4%00%1C%10%00%02%01%05%01%00%00%00%00%00%00%00%00%00%00%00%01%02%04%00%03%05%11%12%14%FF%DA%00%08%01%01%00%01%05%028%FDW%84t%D2%1D%80%93%7DO%FF%C4%00%17%11%00%03%01%00%00%00%00%00%00%00%00%00%00%00%00%00%00%03%11Q%FF%DA%00%08%01%03%01%01%3F%01%8D%D3%FF%C4%00%16%11%00%03%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%01%11%FF%DA%00%08%01%02%01%01%3F%01%A8%FF%C4%00%1E%10%01%00%01%02%07%00%00%00%00%00%00%00%00%00%00%00%01%00%11!%02%03%131Aaq%FF%DA%00%08%01%01%00%06%3F%02%BEe%1ArD%D4_0%C4M%E5D%B7S%FF%C4%00%1C%10%01%01%00%02%02%03%00%00%00%00%00%00%00%00%00%00%01%11%00!1Aq%81%C1%FF%DA%00%08%01%01%00%01%3F!%5DF%87%80%B4%EF%DE%1B%88.%A8%B1%F3%83%CD%B5N%B6%DF%98%40%A6%A3%3A%CF%FF%DA%00%0C%03%01%00%02%00%03%00%00%00%10%97%FF%C4%00%17%11%00%03%01%00%00%00%00%00%00%00%00%00%00%00%00%00%00%01%11Q%FF%DA%00%08%01%03%01%01%3F%10P%D1%FF%C4%00%14%11%01%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%00%FF%DA%00%08%01%02%01%01%3F%10%7F%FF%C4%00%1A%10%01%00%02%03%01%00%00%00%00%00%00%00%00%00%00%00%01%111%00!AQ%FF%DA%00%08%01%01%00%01%3F%10%22!C%AF%04%12%AFQ%CC%5C%C8%90%D0%83B%0AV%F9%9B%06%C1%13%81j%26%C3%7C%C8%99%91%15%95%D9%D8%BC%FF%D9
</Image>
<Url type="text/html" method="GET" template="http://technology.amis.nl/blog/index.php">
<Param name="s" value="{searchTerms}"/>
<Param name="sourceid" value="Mozilla-search"/>
</Url>
<SearchForm>http://technology.amis.nl/blog/index.php</SearchForm>
</SearchPlugin>
This xml file contains an embedded image. Any image can be converted to text using this little tool: the URI Kitchen. I did not check the "base64" option, if you do check it the output looks a bit less messy (but still unreadable to the human eye).
The result after restarting Firefox:
How to execute a search by pressing the ENTER key in UIX
Jul 27th
Many end users are used to pressing enter after inserting one or more search terms in an HTML form. Many, if not most, online search engines support this. Unfortunately, search areas in UIX pages don’t. Having consulted both the Oracle JDeveloper forum and the Oracle JHeadstart forum, no solution for this seems to be available. On some occasions, statements like "this is not possible at all" can be found in thses forums. Well, it IS possible 
If you want to know how, read on.
Demonstration of the ADF Faces Matrix Component
Jul 26th
This article will demonstrate some of the capabilities of the ADF Faces Matrix Component I have been working on for last few weeks. It is a fairly generic component that can be configured declaratively and will be able to render a rich matrix that can be used to present complex analytical data in a compact way. The example demonstrate in this article is too simple to fully demonstrate the matrix capabilities, but it will serve to give a good impression.
The need for a matrix frequently arises when data from an intersection table need to be presented: the records have two master-contexts, and we want to present data in both contexts at the same time. Another situation is where we want to present detail-data for all masters at once. Let’s look at an example. Read the rest of this entry »
JSF EL Calculator bean – to overcome “coercion” errors and add functionality to EL Expressions
Jul 25th
In a recent project I was working on JSF page that needed to display the result of a calculation performed on values taken from various bean properties. The beans were somewhat beyond my control, the properties were all of a numeric type so I tried an EL expression like:
<af:outputText value="#{SimpleBean.operand1 + SimpleBean.operand2}"/>
Simple as it may seem, JSF EL Evaluation resulted in errors such as: "(Coercions) -Attempt to coerce a value of type "oracle.jbo.domain.Number" to type "java.lang.Long"". The two bean properties involved in this case were of different types. The standard EL evaluator in JSF 1.1 assumes that properties involved in numerical operations are either Long (no fraction) or Double (when a fraction is involved). If they are not one of those… too bad.
An attempt to base the rendering expression for this outputText element on one of the operandi also failed, with same error:
<af:outputText value="#{SimpleBean.operand1 + SimpleBean.operand2}" rendered="#{SimpleBean.operand1 > 0}"/>
Again the operation requires a numerical value and EL Evaluation goes for Long. In my case I had used the oracle.jbo.domain.Number – hence the error.
Along the same lines are requirements for operations that EL evaluation does currently not support, such as rounding numbers (for example after calculating #{operand1/operand2*100} %, you can easily end up with output like "33.3333333333 %", which is slightly overdoing it. Rounding the division result to 33.3 would be a boon, but EL cannot do it.
I have come up with a very simple class that can be used in any JSF application to overcome such challenges. Using expressions such as:
<af:outputText value="#{Calculator.RESET[SimpleBean.operand1][SimpleBean.operand2].ADD.EQUALS}" rendered="#{Calculator.RESET[SimpleBean.operand1].LONG.EQUALS > 0}"/>
all the coercion problems disappear.
Using the Calculator bean, we can even create a fairly dynamic calculator, such as shown here:
Any time we change one of the operands or the operator, the outcome is immediately recalculated. The essential piece Read the rest of this entry »
Oracle 11G: describing a refcursor
Jul 22nd
In Oracle 11G the supplied package DBMS_SQL is extended with two new procedures:
- to_cursor_number, which transfers a refcursor to a "dbms_sql cursor"
- to_refcursor, which transfers a "dbms_sql cursor" to a refcursor
These can be used, together with dbms_sql.describe_columns to describe a refcursor.
SYS@LAB> declare 2 rc sys_refcursor; 3 v varchar2(10); 4 n number; 5 c integer; 6 cnt integer; 7 dt dbms_sql.desc_tab3; 8 begin 9 open rc for 'select dummy, cast( 4 as number(3,1)) from dual'; 10 c := dbms_sql.to_cursor_number( rc ); 11 dbms_sql.describe_columns3( c, cnt, dt ); 12 dbms_output.put_line( 'no. columns = ' || cnt ); 13 for i in 1 .. cnt 14 loop 15 dbms_output.put_line( dt(i).col_type ); 16 dbms_output.put_line( dt(i).col_name ); 17 dbms_output.put_line( dt(i).col_max_len ); 18 dbms_output.put_line( dt(i).col_precision ); 19 dbms_output.put_line( dt(i).col_scale ); 20 end loop; 21 rc := dbms_sql.to_refcursor( c ); 22 fetch rc into v, n; 23 close rc; 24 end; 25 / no. columns = 2 1 DUMMY 1 0 0 2 CAST(4ASNUMBER(3,1)) 22 3 1
PL/SQL procedure successfully completed.
Anton
DBMS_PROFILER Report for SQL Developer
Jul 19th
Oracle SQL Developer offers the ability to create reports with graphs in it. A number of reports are included with the tool, unfortunately a report which shows information based on DBMS_PROFILER built in package isn’t there. DBMS_PROFILER allows developers to profile the run-time behaviour of PL/SQL code, making it easier to identify performance issues. Because I couldn’t find an existing report to show this information, I created a couple of reports to show this information. 
Read the rest of this entry »
Puzzelen met SQL – De Ideale Televisieavond van Madelon
Jul 17th
Dit artikel is de on-line tegenhanger van de rubriek Puzzelen met SQL die verschijnt in de Optimize, het vakblad voor Oracle ontwikkelaars in Nederland. In dit on-line artikel kunnen alle scripts worden gedownload, zowel de DDL en data-load scripts, als scripts met mogelijke oplossingen. Ook bevat dit on-line artikel enkele extra uitdagingen en verdere uitwerkingen. Bijdragen aan dit artikel zijn geleverd door Lucas Jellema en Anton Scheffer, vaste medewerkers aan de rubriek Puzzelen met SQL.
How to test for DBMS_OUTPUT with Quest Code Tester
Jul 16th
What is the most common way of testing PL/SQL code? I think it is using DBMS_OUTPUT and manually verifying the results. Is it the best way of testing code? No, I don’t think so, but it is the most common way.
Steven Feuerstein created Code Tester, and if you are a frequent reader of this blog you may know that I really like this tool. However I have been struggeling with it for a bit the last couple of hours and that was while I was trying to figure out how to test DBMS_OUTPUT. Read the rest of this entry »
