When installing Oracle Enterprise Linux in VirtualBox, or downloading one of the available images on Oracle Developer Days, it can be tricky installing the Guest Additions. I was struggling with those, until I found a very useful blogpost by R.Wang. This worked like a charm for me!
Only thing you might want to check, is the kernel version you are using. In bullet 5 is referred to the KERNEL_DIR. In your specific case the version number might be different. go to /usr/src/kernels and check for the latest kernel version in that directory. Also, there was no need for me to alter the yum.repos.d.
You can find the steps for installing the Guest Additions right here.
I wrote a blog entry on my company’s Blog about deploying Application Express applications. It describes the pitfalls on manual and automatic deployment, and hopefully some useful tips.
If you have any comments or questions, feel free to place a comment and I’ll update the blog.
In Application Express, there is no standard way of copying users in an existing workspace to a new workspace. Everytime you create a new workspace, all developers and users must be added manually.
Here’s a little script that does the work for you. Just replace the WORKSPACE with the name of your own workspace where you want to copy the users from, and execute in a SQL Command window in a new workspace. Might save you some time!
declare cursor c_usr is select t.user_name, t.first_name, t.last_name, t.email_address , t.default_schema , fd.developer_role from APEX_040100.WWV_FLOW_FND_USER t join apex_040100.wwv_flow_developers fd on fd.user_id = t.user_id where t.security_group_id = ( select cp.PROVISIONING_COMPANY_ID from apex_040100.wwv_flow_companies cp where cp.short_name = 'WORKSPACE') ; begin for r_usr in c_usr loop APEX_UTIL.CREATE_USER( p_user_name => r_usr.user_name , p_first_name => r_usr.first_name, p_last_name => r_usr.last_name, p_description => null, p_email_address => r_usr.email_address, p_web_password => 'Welkom01', p_developer_privs => r_usr.developer_role, p_default_schema => r_usr.default_schema, p_allow_access_to_schemas => null, p_change_password_on_first_use => 'Y' ); end loop; end;
A question often asked by users, is the addition of tooltips with extended functions (like line breaks or formatting) to Application Express. There is no default functionality in Apex for that, other than the standard alt or title tag in HTML, which I think is rather boring.
There is a more versatile and nice looking alternative for that, called qTip2. It’s an extension for JQuery, which is already incorporated in Apex since version 4.0.
I found it a little challenging to get qTip2 working in Apex, so here’s a how-to for you.
- go to the Edit Page section and add this to the HTML Header Atribute :
<link type="text/css" rel="stylesheet" href="#WORKSPACE_IMAGES#jquery.qtip-2.0.0.css" />
- Add this to the Footer Text Attribute a little lower:
- And add this code to the Function and Global Variable Declaration. It replaces the standard title attributes with the qTip attribute. Mind you: alltitle attributes on the page. That’s ok, it ensures a consistent look of your tooltips on the page.
- Now let’s give it a try. Create a new Text item on your page, and put this in the Label attribute:
<a href="#" title="Your <i>custom</i> label Tooltip">A tooltip label</a>
- And look at the result:
Of course you can also use the syntax used in step 7 in other parts of your page, like report headers, poplists or just plain text.
Here are 2 simple tips for Application Express. Watch the short videos for a hands-on tutorial.
1) creating a Shared LOV from a LOV in an item.
This is especially useful if you created an item LOV and discover you want to use it in othet places too.
2) Create a dynamic autocomplete field.
In our production environment, we have a process that runs for about 30 minutes, and may even take hours on busy days. Way too long of course!
I’ve been rewriting this function this week, and finally brought it back to a matter of minutes by using a materialized view and a more efficient query.
Still, I think nowadays most data should be available instantanious, if possible. So, a few minutes is way too long.
I have 2 tables, tabel A with 2.4 million records, materialized view B with 82000 records. The materialized view is a joined subset of 3 tables containing about 240.000 records each.
Here’s the culprit:
SELECT last_name, date_of_birth , bsn FROM mat_view JOIN table_a ON mat_view.fk_id = table_a.fk_id WHERE mat_view.fk2_id = :BIND
Not too bad I thought. Relatively small view, Oracle knows how to handle this! The result was lots of full table scans and hash joins, and of course a high cost. After giving it some thought, I remembered the thing called inline views. Maybe they would bring Oracle to some other thoughts about the execution path.
(SELECT * FROM mat_view WHERE fk2_id =:BIND
ON gpl.fk_id = table_a.fk_id
Fortunately, this did the trick. Believe it or not, this query gives instantanious result, instead of in minutes. The drawback is that I needed to add some extra, quite complex clauses, so the responsetime is now about 2-3 seconds. In the end, not bad compared to the 30 minutes we started with!
Last week I installed OS X Lion, which was great fun. I’m not going to say it is revolutionary, but it certainly has some nice improvements. One particular issue often mentioned on the internet, is that some software stops working on OS X Lion or Mountain Lion.
I needed Oracle SQL Developer, so downloaded the installer from Oracle Technet. To my surprise, it wouldn’t start. It would be very inconvenient if SQL Developer would be broken under OS X for me! After some research I found that OS X Lion does not have a Java Runtime, and SQL Developer does need one.
You might want to check your log files too. I found that SQL Developer generated a whopping 30 gigabytes of logfiles when it couldn’t find the java runtime.
Installing is simple. Go to your terminal, enter java -version and press enter. Voila, OS X asks if you want to install the JRE. Of course you want to! Press Yes and you’re all set to go. Enjoy.
Something I’ve posted before…. but I thought it was time to rewrite it a little.
We all are at least a bit aware of the awkward behaviour of NULL in Oracle. But it still is difficult every time we encounter it in a function or where-clause.
Some examples to keep in mind when comparing NULL values:
1. null equals what?
Null equals nothing, in fact. It doesn’t even equals to itself.
And because of that, a condition does not always behave in the way you would expect.
Let’s start straightforward.
a := 10 a is null results in FALSE a is not null results in TRUE
a := null a is null results in TRUE a is not null results in FALSE
We’ve all been there, done that and got ourselves the t-shirt.
a := null b := null
This is a situation that you’re bound to encounter sooner or later. Both a and b are null. Let’s compare it!
select 'x' from dual where a = b;
What is the result? FALSE?
Wrong! But it’s certainly not TRUE either. This expression results to UNKNOWN. And that state is a very dangerous one to be in!
Because UNKNOWN acts very similar to FALSE you might be tricked in thinking that they always behave the same. And that is not true. When a condition evaluates to UNKNOWN, no rows will be returned. In that sense it is similar. But look at this:
SQL> create table nulls( id number(9), value1 varchar2(20)); Table created. SQL> insert into nulls values ( 1, 'A collection of '); 1 row created. SQL> insert into nulls values( 2, null ); 1 row created. SQL> insert into nulls values( 3, 'something else'); 1 row created. SQL> select id 2 from nulls 3 where value1 = 'A collection of ' 4 /
ID ---------- 1
That is exactly what we expected. “Where value1 = ‘A coll…’ ” evaluates to TRUE, so a row is returned, and “Where value1 = null” evaluates to UNKNOWN. No row is returned.
Finally, “Where value1 = ’something else’ “, evaluates to FALSE and no row is returned. All expected behaviour.
SQL> select id from nulls where NOT( value1 = 'A collection of '); ID ---------- 3
If we place a NOT operator around the conditions, the difference becomes clear. TRUE has become FALSE, FALSE has become TRUE, and UNKNOWN… is still UNKNOWN. And hence row 2 is not displayed.
2. Nulls in indexes
I’ve created a table with one varchar2 column and 5000 records. The column contains a normal B-Tree index (tst1) and a function based-index tst2 (with function “where NVL( value1, ‘|empty|’ )”. One record is NULL, and unfortunately, that is exactly the one I am looking for. How does this affect performance?
SQL> select count(*) from psi_test where value1 is null; 1 row selected. Execution plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=11) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'PSI_TEST' (TABLE) (Cost=11 Card=509 Bytes=5599) SQL> select count(*) from psi_test where nvl(value1, '|empty|') = '|empty|'; 1 row selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'TST2' (INDEX) (Cost=2 Card=510 Bytes=5610)
As you see, the optimizer does a full table scan on the first example, and an index range scan on the second. Of course, this is a very basic example, but it explains the idea.
3. null functions
NVL is well-known, it’s been there for ages. The function NVL returns the value of its second argument if its first argument is null.
SQL> select value1, nvl( value1, '!! NULL values....' ) from nulls; VALUE1 NVL(VALUE1,'NULLVALU -------------------- -------------------- A collection of A collection of !! NULL values.... something else something else
Powerful, very convenient, so often used.
And what if you want to determine the returned value based on whether an expression is null or not null? NVL is not so useful for that. But NVL2 is. NVL2 has not two but three arguments. The first is the tested value. If that is not null, the second expression is returned. If it’s null, the third expression is returned.
SQL> select id , nvl2( value1 , 'this column is not null' , 'and this columns is NULL' ) test from nulls; ID TEST ---------- ------------------------ 1 this column is not null 2 and this columns is NULL 3 this column is not null
I think these little examples cover most of the issues you can encounter with null values. Don’t get tricked by a null!