ODTUG Sneak Preview, the dressed rehearsal

On Monday, June 15 there will be a sneak preview of the upcoming ODTUG Kaleidoscope Conference. Some of the European speakers (that is Belgian and Dutch) will be doing their presentation. I'm just guessing here, but I can imagine that the presentation will be in Dutch.



The presentation that will take place are:

• Aino Andriessen (AMIS Services) - ADF Development: More tales from the Trenches


• Lonneke Dikmans (Approach Alliance) - Top Ten Tips: Best Practices for Designing Services, Events, and Business Processes


• Olivier Dupont (iAdvise) – APEX at the Belgium airport


• Dimitri Gielis (APEX Evangelists) – Mastering an APEX page


• Roel Hartman (Logica) – How to integrate APEX and Oracle Forms?


• Lucas Jellema (AMIS Services) - Truth and Dare—The Story of How an Oracle Classic Stronghold Successfully Embraced SOA


• Toon Koppelaars – Fat databases: A layered approach


• Ronald van Luttikhuizen (Approach Alliance) - Customer case: Implementing SOA in a database-centric environment


• Alex Nuijten (AMIS Services) – SQL Holmes – The case of the missing performance

Last year we did a similar Sneak Preview which was a great success. If you want to attend then register early, seating is limited. Oh, and did I already mention that this session is free?
More information and registration can be found in the Agenda via the AMIS homepage.

Copy and Paste: Clipboard Items

By accident I discovered something in SQL Developer. Another one of those things I need to remember for future reference. Hope you can use this feature too.

You know you can use CTRL + C for copying and CTRL + V for pasting. Nothing new there, but what is really neat is that you can use SHIFT + CTRL + V to see the contents of all your clipboard items.

Because I needed to do a lot of copying today, this came in really handy.

Oracle Text: diacritic search

For the client where I'm currently working, the need arose that we needed to find lastnames regardless of diacritical characters. In Dutch, we have lastnames (and firstname too) where characters like ü, ä, ö, é occur.

The "usual" way to handle this was to add a column to the table, and store the name there without the diacritical characters. Meaning the name "müller" would be in one column, while another column would contain the name "muller", the same letters but without the double dots (called a trema in Dutch) over the "u". A common complaint with this approach, is that when you query with the double dots, you won't be able to find what you are looking for.
There is also the possibility of using Oracle Text to handle this. In the past I have been to a presentation on Oracle Text, but never used it before. It scared me in a certain way. I thought it would be quite complicated to use it, but it turned out to be real easy.

Let's start with the table

create table text_test
(name varchar2(255)
);

insert into text_test values ('muller');
insert into text_test values ('müller');
insert into text_test values ('MULLER');
insert into text_test values ('MÜLLER');
insert into text_test values ('mueller');
insert into text_test values ('MUELLER');
--
insert into text_test values ('möller');
insert into text_test values ('moller');
--
insert into text_test values ('mäller');
insert into text_test values ('maller');
--
insert into text_test values ('Médar');

commit;

In this table are some variations of "Muller", upper- and lowercase and with and without the diacritical character.
In order to use Oracle Text to search for the name regardless of diacritic, we need to create a Custom Lexer. This custom lexer is needed, because we need to change the Base Letter setting. From the documentation:

With base-letter conversions enabled, letters with umlauts, acute accents, cedillas, and the like are converted to their basic forms for indexing, so fiancé is indexed both as fiancé and as fiance, and a query of fiancé returns documents containing either form.

And this is exactly what we're after.

To be able to search using the Base Letter conversion, as described in the quote from the ducmentation, we need to create a Preference.
To change the setting of the Base Letter, the default is NO, we to set this attribute to YES.

begin
   ctxsys.ctx_ddl.create_preference ('cust_lexer','BASIC_LEXER');
   ctxsys.ctx_ddl.set_attribute ('cust_lexer','base_letter','YES'); -- removes diacritics
end;
/

In the above code, we create a preference, called CUST_LEXER. Because the main language will be Dutch, I use the BASIC_LEXER. More information regarding this in the Oracle documentation.
The attribute that I want to override is the BASE_LETTER, so set that attribute to YES. And that should take care of it.
Now the only thing to do is create the Oracle Text index and specify that we want to use our preference set.

CREATE INDEX text_test_idx ON text_test(name) INDEXTYPE IS CTXSYS.CONTEXT
parameters ('LEXER cust_lexer');

Now that we are all set, let's run a query and verify the results.

SQL> select name
  2    from text_test
  3   where contains (name, 'muller') > 0
  4  ;

NAME
-----------------------------------------
müller
MULLER
muller
MÜLLER

And there you have it, it works as advertised.

While writing this blog, a colleague of mine pointed out that in Germany it is accepted to spell the name "müller" as "mueller". Oracle Text can even handle that.
Add this to your preference set, and you're good to go.

ctxsys.ctx_ddl.set_attribute ('cust_lexer','alternate_spelling','GERMAN');

With a little demo:

SQL> select name
  2    from text_test
  3   where contains (name, 'muller') > 0
  4  ;

NAME
-----------------------------------------
muller
müller
MULLER
MÜLLER
mueller
MUELLER

This also works when searching for "Mueller"

SQL> select name
  2    from text_test
  3   where contains (name, 'mueller') > 0
  4  ;

NAME
-----------------------------------------
muller
müller
MULLER
MÜLLER
mueller
MUELLER

The problem with Oracle Text used to be the synchronization of the Text indexes with DML actions. In the old days, you needed to take care of this yourself. Schedule a "make sure the Text index is updated to be in sync with the table". This could mean that after you added a name like "Désiré" to your table, you wouldn't be able to find because it wasn't in the text index.
Starting with Oracle 10g (release 2) you can indicate that the index needs to be synchronized during a COMMIT. Just specify it with the creation of the index, and that's it.

CREATE INDEX text_test_idx ON text_test(name) INDEXTYPE IS CTXSYS.CONTEXT
parameters ('LEXER cust_lexer SYNC (ON COMMIT)');

This Oracle Text is really cool stuff, and this is just the beginning. The Oracle Documentation is a great source of information. A link is included at the bottom.

Finally the cleanup:

drop table text_test cascade constraints
/
begin
   ctxsys.ctx_ddl.drop_preference ('cust_lexer');
end;
/

Oracle Text Documentation

Display Images with SQL Developer

Images can be stored as a BLOB in the database. SQL Developer can show them as well.

Step 1) Create a Table to store the image in

create table t
(img blob);

Step 2) Upload an Image




Choose an image from your file system, and commit your changes.

Step 3) On the "Data" tab, click the "..." button with the BLOB column.


Step 4) In the dialog box, check the "View as Image" box


And that's it... Looks a lot better than SQL*Plus would display images :)

SQL> select img
  2    from t
  3  /
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> 

Debugging Associative Arrays

My favorite development tool is PL/SQL Developer by Allround Automations. The Debugger included with this tool is great, I really like it… but… not for Associative Arrays.
Let’s take a look at some code we want to debug

type emp_rt is record
(ename    emp.ename%type
,hiredate emp.hiredate%type
,sal      emp.sal%type
);
type emp_tt is table of emp_rt
index by pls_integer;

First we declare a Record, based on some of the columns of the infamous EMP table. Next we declare the Associative Array based on the Record structure.
Then we need a (packaged) procedure, which we are going to debug. The type declaration and the procedure are both in a Package, of course.

procedure test
is
emps emp_tt;
idx  pls_integer;
begin
select ename
    , hiredate
    , sal
 bulk collect
 into emps
 from emp
;
idx := emps.first;
while idx is not null
loop
  dbms_output.put_line ('Employee: '||emps(idx).ename);
  idx := emps.next(idx);
end loop;
end test;

PL/SQL Developer has a Test Window, where you can run an anonymous block. It is possible to call this window whenever you feel like, no need to be in the context of a Package like in SQL Developer. Nor does the package need a breakpoint. And this is a good thing, 'cause most of time I forget to set breakpoints.

When you are stepping through the code and you want to see the content of the Associative Array, you will get the message:

And that's too bad.

SQL Developer to the Rescue

SQL Developer, Oracle's free IDE, also has a Debugger. And what is really nice about this debugger, is that you can view the content of the Associative Array.

1. Set a Breakpoint in the procedure (this can only be done when you are in edit mode)
   
2. Compile the Package in Debug (with the black compile button
3. Choose Run --> Debug from the menu

4. Run the procedure from the Debug window
5. Execution halts at the Breakpoint

You can look on the "Smart Data" tab to see the content of the Associative Array.

Simply Brilliant! Great Job, Sue!

Overloading Functions that Return Boolean

When you have a function that returns a Boolean, you know that you can't use it in SQL. SQL just doesn't have a Boolean datatype.

Say you have a function like the following:

function ftest (p_param1 in varchar2
               ,p_param2 in varchar2
               )
   return boolean;

It is easy to overload the function, provided you placed it in a package, to return a datatype which is compatible with SQL.
All too often an overloading would look like

if ftest (param1, param2)
then
   return 1;
else
   return 0;
end if;

Can you spot the flaw in this?
The function will return a zero when the original function returns FALSE or NULL.

A better way to overload is to use SYS.DIUTIL to do the job

   return sys.diutil.bool_to_int (ftest (param1, param2));

This function will return a 1, 0 or NULL.

SQL> begin
  2     dbms_output.put_line ('True : '||sys.diutil.bool_to_int (true));
  3     dbms_output.put_line ('False: '||sys.diutil.bool_to_int (false));
  4     dbms_output.put_line ('NULL : '||sys.diutil.bool_to_int (null));
  5  end;
  6  /
True : 1
False: 0
NULL :

And there is also a "reverse" function, to turn an integer into a Boolean

SQL> begin
  2     if sys.diutil.int_to_bool (1)
  3     then
  4        dbms_output.put_line ('True');
  5     end if;
  6     if not sys.diutil.int_to_bool (0)
  7     then
  8        dbms_output.put_line ('False');
  9     end if;
 10     if sys.diutil.int_to_bool (null) is null
 11     then
 12        dbms_output.put_line ('Null');
 13     end if;
 14  end;
 15  /
True
False
Null

Value Error and Invalid Number

Currently I'm in the process on reviewing some chapters on an upcoming book... more on that in a later blog, 'cause I am not sure if I can talk about it or not.

One of the chapters is on "Exceptions" and I noticed a line from the Oracle documentation which I didn't notice before. The VALUE_ERROR (ORA-6502) and INVALID_NUMBER (ORA-1722) are predefined exception, which can be handled by name in your exception handler.
Quote from the docs:
"...In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)"

Let's first check the latter part, about the SQL statements:

SQL> select to_number ('a')
  2    from dual
  3  /
select to_number ('a')
                  *
ERROR at line 1:
ORA-01722: invalid number

Now let's check the procedural part, in PL/SQL.
Based on the description, this statement would cause a VALUE_ERROR exception

SQL> declare
  2     n number;
  3  begin
  4     n := 'a';
  5  exception
  6    when value_error
  7    then
  8      dbms_output.put_line ('Value Error');
  9  end;
 10  /
Value Error

PL/SQL procedure successfully completed.

...and it does.

But what if you would use a SQL statement in PL/SQL?
Would this also raise a VALUE_ERROR?

SQL> declare
  2    n number;
  3  begin
  4     select 'a'
  5       into n
  6       from dual
  7     ;
  8  exception
  9    when value_error
 10    then
 11      dbms_output.put_line ('Value Error');
 12  end;
 13  /
Value Error

PL/SQL procedure successfully completed.

Yes, it does. In accordance with the documentation, doing a conversion in procedural statement will raise the VALUE_ERROR exception,...
One last option, let's use the first SQL statement from this blog and use it in PL/SQL:

SQL> declare
  2    n number;
  3  begin
  4    select to_number('a')
  5      into n
  6      from dual
  7    ;
  8  exception
  9    when value_error
 10    then
 11      dbms_output.put_line ('Value Error');
 12    when invalid_number
 13    then
 14      dbms_output.put_line ('Invalid Number');
 15  end;
 16  /
Invalid Number

PL/SQL procedure successfully completed.

As you can see, it raises the INVALID_NUMBER exception. I expected that it would raise the VALUE_ERROR as well.

Tracing the statements reveals the explanation. With the implicit datatype conversion, this is the statement that is executed by the SQL engine (I added some text to the statement to identify the actual statement):

SELECT /*+ implicit_conversion */'a' 
FROM
 DUAL 

and with the explicit datatype conversion, this SQL statement gets executed

SELECT /*+ explicit_conversion */ TO_NUMBER('a') 
FROM
 DUAL 

Sometimes it's too easy to ignore these little things. I learned something about exceptions again.
Link:
Oracle Documentation on Predefined PL/SQL Exceptions