Oracle procedures in java

Yesterday I found in scripts prepared by my colleague some Java code. I didn't do something like this. I tried to modify a sample of code which returns current time in long number format. However I couldn't do any changes because it is possible only execute a compiled and loaded class and return the result of execution, so if jre fits your needs you are lucky.
From Google I found out that I need to load a class of application but I didn't tried. Example was simply hallo word, so I don't now if it is possible to execute query inside class.

I noticed that Java isn't loaded by default because first start took a few seconds.

Java tests - my notices

Last one or two weeks I spend on reading a blog www.petrikainulainen.net. I focused over testing. I'd like to find solutions of my problem with testing a dao which use advanced pl/sql queries. Unfortunately I didn't. However I get to know other valuable notices about testing. I will list here the most important for me:
1) Test are benefit especially  during maintenance (they describe, how application work) and during changing one of component,
2) Clean code in tests are the same important as in application code,
3) Tests, which are low quality, are unnecessary and you should change it or remove.
4) Don't test framework code. If you don't trust it, you shouldn't use it.
5) Separate unit tests and integration tests. You can do it in maven.
6) Don't do unit tests for dao, do integration tests.
7) Use determined data for test, then you won't have false-positive.
8) To prepare data to test you can use memory db and DbUnit to prepare data.
9) One test should test one logic thing. When it is tested many logic things, nobody now what's goes wrong when test failed.
10) Never accept habit do tests after project deployment. It is almost certain that it won't be time to do that and much more of cases you will forget.


There is one thing that I didn't find solution. How can I test my advanced queries build and executed from Java? Queries uses some specific command for oracle database so I can't use to test them other database. However maybe I should test them from database side, not from Java side, but how to test mappings?

Tuning Oracle queries with null values

Today I tested two queries in Oracle database. I noticed that when I execute query there column value is null, oracle engine directly use full access to table. I created index like:

create index MY_IDX on MY_TABLE (COLUMN_CAN_BE_NULL, -1);

and I have to use hint to use that index. Automatically oracle used full access, even when I counted statistic for this table.
However when it used index like this, it took much more then full access.

I have to dwell on a subject....

SQL and null conditions

Today I found in my SQL package code one bug. I created function/procedure with a few input parameters and used them in simple sql query as condition. In some cases that input parameters are null. I used that parameters with = sign. However oracle interpret construction like:
some_column = input_val
even column and input_value are null as not fulfill condition.

In older oracle sql it is possible to use function decode(some_column, input_val, -1) = -1 but it is not performance
In newer oracle db you can create index like:

create index some_index_name for table_name (some_column, -1)

You have to look out for possible values in some_column.