Italian Accent Oracle fix

In Italian language we have accent words like àèéìòù

They are quite important because for instance “is” is spelled

è

whereas “and” is spelled

e

Accent are bad guys even today: if you copy them from MS-Word inside an Oracle sql script you can end up with different UTF-8 values.
Also the accent are likely to be destroyed if you past them in your html page without using the correct html entity (i.e. è )

Sometimes you need to export the data inserted with accent: an Oracle Virtual column can be an elegant solution….if you have at least Oracle 11g.

 

Also a magic update can fix some nasty problems….here the solution:

 

ALTER TABLE TEST_TABLE ADD( NAME_NORMALIZED generated
always
AS
( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
ACCENT_COLUMN,
'à','a'''),'è','e'''),'é','e'''),'ì','i'''),'ò','o'''),'ù','u''') ));

Oracle SQL Developer Keep Alive plugin

On some environment, on some customers, Oracle connections are drop after some idle time (i.e. 2 minutes).

I have found a small plugin for Sql Developer to solve this issue: it is very handy and logs its usage. Code is also very clean

 

Oracle Database Sync

Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now.

I will show here a very fast way of comparing different database and auto-generate migration script.

  1. Reverse engineer the source database using Oracle Data Modeler
  2. Now select the right arrow  shown below:
    sync-with-db
  3. Select the destination datasource (more here) when asked.
  4. The compare pane will enable you to compare the databases.
    But the default options will also use schema name to detect different object, so you will not get what you want in every scenario.
    In 50% of my daily job, schema are different, so you must select the options below to fix it:
    Select “Options” and then “Compare options”. Then  de-select “Use schema property” like suggested below:
    magic-option
  5. Push the “Sync new object”. Then push the “DLL Preview” button and inspect the generated database.
  6. Bonus: by default the tool will not include tables to drop. Inspect the two pane ad check the table you want to drop in the left pane.
  7. Refrain launching the script without proper testing. You are migrating your valuable data.

ORA-01461: can bind a LONG value only for insert into a LONG column

Disapprove with me!
Disapprove with me!

Okey, you know Oracle. A very good database, a very old database, a very solid rock. Not famous for its error messages. I have already stumbled upon a misleading error on the old rock.

This error anyway is incredible. If you try to push a very long text in a varchar2, you can end up with a

ORA-01461: can bind a LONG value only for insert into a LONG column

error.

Stack overflow promptly reported this years ago, so you can check it with your eyes.