Sometimes you need to remove nasty duplicate on a table, based on a subset of the column. On every big database there is something called “rowid” which can be used to indentify a column in a unique way. On PostgreSQL is called ctid, as we shall see:

Read More

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

Even if  Oracle databases (<12) does not support auto increment, you can easily ask to your sql data modeler to generate for you a sequence and a trigger in a automatic way.

Read More

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:
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:
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.

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

Read More

Working in a big company, my work laptop came with MS-Windows7 Enterprise installed. But as you imagine, Unix is my preferred desktop environment. So let’s how to configure a perfect Debian Linux for a old wolf consultant like me. You will be able to install commercial software mubmo jumbo like Oracle Express and IBM Websphere in a snap, and have a 64 rock solid system, easy to bring in a USB stick if you like.

Read More

I stumbled upon a very brain-f**k error on Oracle 10g on these days.

Context: the following query [sql]SELECT * FROM (
SELECT TO_NUMBER(CUSTOMER_ID) AS SNDG FROM BAD_CODES_TABLE WHERE
AND I_LIKE=UPPER(‘STATIC_CONDITION’) AND CUSTOMER_ID NOT LIKE ‘%P%’ ) S WHERE TO_NUMBER(S.SNDG) >2000[/sql] could trigger a Invalid number if CUSTOMER_ID column contains invalid numbers.

Why?

Well…if you ask to “explain plan”, you will get something like

• a table full scan
  • Filter Predicates AND
    • I_LIKE=UPPER('STATIC_CONDITION')
    • TO_NUMBER(S.SNDG) >2000
    • CUSTOMER_ID NOT LIKE '%P%'

Read More

I run into a very big trouble on these days.

• My big company delivered to me a Windows7 64bit system (first error)
• I asked for Office 64 bit and Visio2007 32 bit
• I tried to connect to Oracle (second error) to reverse engenieer a DB with Visio2007

I got a "driver architecture" error under Visio2007. Launching %WINDIR%\\System32\\odbcad32.exe

(the 32bit guy of the odbc drivers side) I was able to get the same error… and this was helpful:

Read More

Oracle buys BEA and Sun buys MySql.

In reply,Migrosoft is hungry and is watching Yahoo :-)

<joking> 

Any bets on the next money-based-news?

I'm going for

• Oracle buys Sun: "We need more hardware to run Oracle and WebLogic together! "
  
• Google buys Oracle: "We have *even* more hardware...come on!"
  
• Apple buys Google: "So nice LOGO", retrodating a bunch of stock options, I suppose.
  
• Microsoft buys Apple ("So annoied their are more cool then us!") and shut down all them all so you will end up using their search engine, I hope.

</joking> 

Read More

Sometimes I need to build a small script to delete a huge set of tables of a schema.

In Oracle is easy to do it, using the meta tables "all_tables", "all_views", and "all_sequences":

select  'drop table ' || table_name || ' cascade constraints;' 
   from all_tables where owner='protoss' and  table_name not like 'BIN%';

select  'drop view ' || view_name || ' ;' 
   from all_views where owner='zerg' ;
  
  select  'drop sequence ' || sequence_name || ' ;' 
   from all_sequences where sequence_owner='spacemarine' ; 

The "BIN%" table are special temp tables of oracle.

Read More