Há um tempo atrás precisei migrar um projeto específico do MySQL para o Oracle, e vou colocar aqui várias observações e dicas que pude notar. Se algo estiver errado, ou você souber uma maneira melhor de como fazer, poste um comentário.

Também quero deixar claro que este não é um artigo do tipo “software proprietário x software livre”, já que o motivo da minha migração foi bastante razoável: um potencial cliente - muito importante - só rodaria meu sistema em Oracle. Portanto aqui estão apenas dicas e não comparações sobre qual deles é melhor.

Como muitos, eu comecei primeiro mexendo com o MySQL, e algumas vezes fiz algo para Postgres. Bem, Postgres é uma mãe, e tenta emular tudo que os outros bancos possuem. Mas este não é o caso do Oracle.

Como após publicar este artigo aqui no site surgiram vários serviços relacionados à migrar bases de dados do MySQL para Oracle, sempre vou acrescentando mais detalhes com o decorrer do tempo, pois novos problemas vão surgindo.

Update: Existe um ótimo artigo (em inglês) da própria Oracle sobre a diferença entre os dois SGBDs, não deixe de conferir.

Índice

Histórico
Iniciando
Bases de Dados
Caracteres de escape
Problemas com caixa baixa
Auto Increment
Cláusula LIMIT
Cláusula GROUP BY
Date e Datetime
Números e formatação
Número de registros de uma consulta
Listas (ENUM e SET)
Transações
Outras Dicas
Conclusão

Histórico

10/08/2007 - Versão inicial
15/08/2007 - Correções
05/09/2007 - Adicionadas informações sobre oci_num_rows
22/10/2007 - Adicionadas informações sobre datetime
02/01/2008 - Adicionado índice e informações novas sobre datime, number format e case-sentitive
03/01/2008 - Novas informações sobre databases
27/05/2008 - Adicionadas novas informações sobre enum e set
20/11/2008 - Adicionadas informações sobre group by
12/09/2010 - Corrigidas informações sobre o tipo DATE (obrigado Pablo Santiago Sánchez)
30/07/2014 - Melhorando formatação
31/07/2014 - Acrescentando link para artigo da Oracle (obrigado João Borsoi)

Iniciando

A primeira coisa que fiz foi baixar a versão XE do Oracle (versão Express). Esta versão é gratuita, mesmo para uso comercial, e possui como limitação usar apenas 1 processador, até 4 Gigas de disco rígido e 1 Giga de RAM. A idéia da Oracle? Simples: comece seu sisteminha pequeno usando Oracle XE, e se um dia ele se tornar maior que estas limitações, então é porque provavelmente o custo de comprar uma licença do Oracle não será um obstáculo.

Logo após instalar, você irá notar que o Oracle, com a base limpa, irá usar quase 1,5 Gigas de disco rígido. Também não irá consumir pouca memória.

Junto com a instalação, vem uma interface web. Bonita, amigável, porém limitada e um pouquinho lerda.

Mas esta interface tem algumas vantagens para quem migra do MySQL: ela tenta imitar o comportamento do auto-increment (vou falar melhor sobre mais adiante), e outras coisinhas legais (como um assistente para criar chaves estrangeiras). Se você não se sentir a vontade nela, você pode usar a linha de comando, ou pegar a versão gratuita do SQL Developer, que além de funcionar com o Oracle, também funciona com o MySQL, e possui inclusive um assistente de migração (este assistente fica para um artigo futuro).

Vou listar agora outras diferenças e conceitos que eu encontrei pela frente.

Bases de Dados

Bem, partindo daqui, a primeira diferença para o MySQL: se neste criávamos várias bases diferentes para projetos diferentes num mesmo servidor, no Oracle um servidor significa apenas uma base de dados. Antes de imaginar que tudo é bagunça, não é: cada usuário criado consegue enxergar apenas seu schema, que é o conjunto de suas tabelas, dados, triggers, procedures, etc. Ou seja, você só enxerga suas tabelas (e outros objetos), outro usuário só as dele, e assim por diante.

Portanto, se antes você criava databases para cada projeto, agora você irá criar usuários para cada projeto. Talvez até tablespaces (mas não vou falar sobre isso aqui).

Ou seja: a primeira coisa que você vai fazer após instalar o Oracle, será logar com o usuário SYS e criar um novo usuário para o seu projeto (nada de usar o SYS, hein?).

Um detalhe importante, caso você esteja usando a versão XE, é que você obrigatoriamente terá que informar “XE” como nome da base de dados ao conectar-se (em outra versões, apenas informar o endereço do servidor é o bastante, já que temos apenas uma base por servidor, lembra?).

Caracteres de escape

Para identificar nomes de campos e tabelas, sempre usamos caracteres de escape, para que, por exemplo, o gerenciador de banco de dados saiba que minha tabela chamada GROUP é uma tabela, e não o comando GROUP.

No MySQL, embora se possa usar em alguns casos aspas simples ou duplas, o caracter oficial de escape é a crase (`). No Oracle, é sempre a aspa dupla (").

Qual o problema com isso? Bom, além de todas alterações nos códigos SQL que você terá que fazer, é provável que seu comando SQL esteja em uma variável do tipo String, dentro de aspas duplas. Ou você coloca barra invertida antes de cada uma das aspas duplas dentro do seu comando, ou declara todo o comando dentro de aspas simples (válido somente para PHP, que aceita Strings tanto com aspas simples quanto com aspas duplas).

Problemas com caixa baixa

Você irá ler em vários lugares que o Oracle é case insensitive, etc. Mas não é bem assim. No MySQL é muito comum usarmos tudo identificado por letras minúsculas.

No Oracle não: o próprio assistente web irá transformar nome de tabelas e campos para maiúsculas (mas você pode alterar isso). O problema de manter as caixas baixas (como fazíamos no MySQL) é que todo comando SQL irá exigir asplas duplas para identificar campos e tabelas, enquanto o mesmo não acontece se usarmos caixas altas.

Por exemplo, mantendo tudo em minúsculo, se no MySQL fazíamos:

SELECT nome FROM produtos;

No Oracle você vai ser obrigado a inserir as asplas duplas:

SELECT "nome" FROM "produtos";

Isso não acontece quando usamos caixas altas. Portanto, cabe à você decidir: manter as caixas baixas e colocar asplas duplas em tudo, ou alterar para maiúsculas.

Auto Increment

Se você nasceu e cresceu com o MySQL, usar chaves primárias com auto-increment é a coisa mais natural do mundo. O problema é: praticamente só o MySQL tem o tipo auto-increment! Todos os outros bancos utilizam sequences (ou seqüências).

Sequences são seqüências numéricas armazenadas pelo banco em um espaço separado, onde você é quem precisa perguntar o valor atual ou incrementar este valor. Não é feito automaticamente. Desvantagem: o trabalho que isso dá ao criar as tabelas, mas depois de acostumar, fica indiferente. Vantagem: você não precisa usar sequence só com a chave primária, pode usar ela em qualquer lugar.

Se você usar a interface web do Oracle Express para criar as tabelas, ele irá simular um auto-increment para você. Ou seja: depois de criada a tabela, basta apenas usar NULL como valor para a chave primária, e voilá, seu índice terá o valor da última entrada somado com 1.

Se você não usa a interface web, ou quer entender como ele simula isso, vamos lá: primeiro é criada uma sequence; depois disso um trigger é criado para que sempre que uma nova linha seja inserida no banco, o valor da sequence seja lido, somado com 1, e tenha esse valor atribuído a sua chave primária. Se você não sabe o que é trigger: é um evento (na verdade um comando ou procedure SQL) que é disparado automaticamente quando determinada condição acontece (e nós é quem definimos essa condição).

Tomando como exemplo uma tabela chamada PRODUTO (com os campos ID_PRODUTO e NOME), a interface web criaria os comandos abaixo (criação da tabela, criação da sequence e da trigger e sua ativação, respectivamente):

CREATE TABLE "PRODUTO"
( "ID_PRODUTO" NUMBER,
  "NOME" VARCHAR2(100) NOT NULL ENABLE
  CONSTRAINT "PRODUTO_PK" PRIMARY KEY ("ID_PRODUTO") ENABLE
);

CREATE SEQUENCE "PRODUTO_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER  "BI_PRODUTO"
 before insert on "PRODUTO"
 for each row
begin
   select "PRODUTO_SEQ".nextval into :NEW.ID_PRODUTO from dual;
end;

ALTER TRIGGER "BI_PRODUTO" ENABLE;

Repare que tanto a chave primária, a sequence e o trigger tem um nome para identificá-los.

Para pedir ao banco qual foi o valor inserido, usando como exemplo o PHP, faça da seguinte forma:

<?php

  $sql = "SELECT $sequence.CURRVAL AS next FROM DUAL";

?>

Onde $sequence é o nome da seqüência que estamos usando na chave primária. No exemplo da tabela PRODUTO, usaríamos PRODUTO_SEQ.

Cláusula LIMIT

Aqui o bixo pegou. Há anos eu lia que Oracle não tinha LIMIT, e que sempre uma versão vindoura iria implementar isto. Bem, a versão Express é a versão 10, já existe uma versão 11, e adivinhem? Sem LIMIT.

O LIMIT é legal principalmente para fazer algoritmos de paginação de dados: você diz quantos registros quer pegar, e começando em qual linha da tabela. Por exemplo: SELECT * FROM PRODUTO LIMIT 0,30, e isso significa “traga 30 linhas, começando da posição 0”.

O Postgres tem esta cláusula, o Microsoft SQL Server tem um TOPROW (que limita apenas superiormente), e o Oracle tem o ROWNUM, mas basta apenas usar um ORDER BY, e o ROWNUM já perde toda a validade. Isso porque ele primeiro limita os resultados com o limite fornecido por ROWNUM, e depois aplica o ORDER BY.

Em Java, no JDBC existe um método chamado setMaxRows, da interface Statement, mas ele não limita sua consulta, e sim apenas a quantidade de linhas que o ResultSet pode guardar. Isso não é o que queremos.

Depois de ler algumas coisas na internet, eu vi que simular o LIMIT só funcionaria usando sub-selects. O desempenho não deve ser nada bom, mas ao menos uso apenas nas paginações. E se você souber como melhorar isso, me avise!

A primeira coisa que fiz, foi ao invés de escrever o LIMIT diretamente no código SQL, criar uma função, que apenas para simplificar, seria no PHP algo como setLimit($sql, $limit, $offset=0).

Depois disso, caso o banco seja MySQL, você simplesmente pega a variável $sql e concatena com a cláusula LIMIT:

<?php

  function setLimit($sql, $limit, $offset=0) {

    $result = $sql." LIMIT $limit OFFSET $offset";
    return $result;
      
  }
    
?>

No caso do Oracle ficou um pouquinho maior. Bem maior, aliás. E feio. Mas funcionou aqui na maioria dos casos, e claro, desde que o SELECT não seja muito complexo.

O código ficou assim:

<?php

  function setLimit($sql, $limit, $offset=0) {
    $max = $offset + $limit;
    $reg = "/SELECT\s+(.*)\s+FROM\s+.*/smi";
    if(0 < preg_match($reg, $sql, $m)){    
      $clearFields = array();
      foreach(explode(",", $m[1]) as $f){
        $f = trim($f);
        if(($p = strrpos($f, " "))!==false) $f = substr($f, $p+1);                
        if(($p = strrpos($f, "."))!==false) $f = substr($f, $p+1);
        $clearFields[] = $f;
      }
      $sql = "SELECT 
                ".implode(", ", $clearFields)."
              FROM 
                (SELECT ROWNUM as num_line, T.* FROM ($sql) T WHERE ROWNUM <= $max) 
              WHERE num_line > $offset";        
    }
    return $sql;
  }
    
?>

Eu avisei que ficava feio! Algum voluntário?

Cláusula GROUP BY

O GROUP BY do Oracle é levemente diferente do MySQL, mas nada que seja muito trabalhoso.

No MySQL, a seguinte consulta funcionaria normalmente:

SELECT "id_produto", "nome", "descricao" FROM "produtos" GROUP BY "id_produto"

Porém, no Oracle, essa mesma consulta irá gerar o seguinte erro: ORA 00979 "not a GROUP BY expression" (ou "não é uma expressão GROUP BY")

O motivo desse erro: no Oracle, quando fazemos um GROUP BY precisamos colocar nele todas as colunas que estão na cláusula SELECT. Ou seja, a consulta acima teria que ser modificada para incluir no GROUP BY a coluna nome e descricao (e qualquer outra coluna que estivesse na cláusula SELECT):

SELECT "id_produto", "nome" FROM "produtos" GROUP BY "id_produto", "nome", "descricao"

Date e Datetime

Para datas, o Oracle tem dois tipos de campos, assim como o MySQL: o tipo TIMESTAMP (que permite armazenar os segundos com grande precisão, como por exemplo, os milisegundos), e o tipo DATE (que guarda data e hora, ou se necessário, apenas a data).

Para o campo DATE, existe uma diferença: o Oracle irá usar o formato de data e hora da região configurada no seu sistema operacional. Ou seja, se seu sistema operacional está configurado com Brasil como região (nada mais normal, não?), o formato de data não será o formato que estamos acostumados no MySQL (Y-m-d H:i:s, usando os caracteres da função date do PHP). O MySQL permite algumas configurações por língua e região, mas o mais comum é ninguém alterar isso.

Como arrumar isso? Você tem três opções: alterando a cada consulta (o que é chato); avisando o formato a cada nova conexão (resolve fácil nosso problema, ainda mais se você tiver uma classe e colocar isso no construtor); ou alterando o próprio arquivo de inicialização do Oracle (oraclexe/app/oracle/product/10.2.0/server/config/scripts/init.ora).

Para alterar o formato, faça o seguinte (a cada nova conexão, ou no arquivo de inicialização):

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'

Um último detalhe importante sobre o tipo DATE: como é de se esperar, as funções de DATETIME (lembre-se, no Oracle é TIMESTAMP) são diferentes. Se você usa as do MySQL, consulte o manual de funções do Oracle (em inglês, se precisar de ajuda, entre em contato) para alterar de acordo com sua necessidade.

O mesmo problema de formato acontecerá com números, como veremos no item seguinte (e a solução é a mesma).

Números e formatação

Em Oracle temos apenas o tipo NUMBER, tanto para inteiros, quanto para números com casas decimais. Na verdade o funcionamento é bem parecido com o tipo DECIMAL do MySQL.

Para números inteiros, informamos a quantidade de casas requeridas, avisando que não queremos casas decimais. Por exemplo, para um SMALLINT do MySQL, usamos no Oracle um NUMBER(2,0).

Para números com casas decimais, usamos igualmente ao que fazemos com o DECIMAL. Se queremos armazenar um número com 8 dígitos, sendo destes que destes 8, usaremos 2 para casas decimais, então usamos NUMBER(8,2).

Quanto ao caracter de separação das casas decimais, iremos cair no mesmo problema do tipo DATETIME: o Oracle irá usar o formato de data e hora da região configurada no seu sistema operacional. Ou seja, se seu sistema operacional está configurado com Brasil como região, o separador de casas decimais será a vírgula (por exemplo 100,79) e não o ponto (por exemplo 100.79).

Para alterar isso, temos as mesmas alternativas usadas com datetime: alterando a cada consulta (o que é chato); avisando o formato a cada nova conexão (resolve fácil nosso problema, ainda mais se você tiver uma classe e colocar isso no construtor); ou alterando o próprio arquivo de inicialização do Oracle (oraclexe/app/oracle/product/10.2.0/server/config/scripts/init.ora).

Para alterar o formato, faça o seguinte (a cada nova conexão, ou no arquivo de inicialização):

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'

Você pode fazer as 3 alterações em conjunto: DATE, DATETIME (no Oracle é o TIMESTAMP) e formatação de números.

Número de registros de uma consulta

Este problema é relacionado apenas ao PHP. Se você está usando Java, pode pular para o próximo tópico.

Em PHP é muito comum usarmos a função mysql_num_rows para obtermos o número de registros em uma consulta com SELECT (para INSERT, UPDATE, REPLACE e DELETE usamos a função mysql_affected_rows).

Para Oracle, temos apenas a função oci_num_rows, que tem o funcionamento meio estranho. Para INSERT, UPDATE, REPLACE e DELETE, ela irá funcionar normalmente. Mas para SELECT, ela funcionará apenas depois da função oci_fetch_array. E detalhe: como o oci_fetch_array traz apenas uma linha de resultados (tupla) por vez, para ter o número real de resultados, você tem que chamar oci_num_rows depois de ler todos os resultados com oci_fetch_array.

Ou seja, se tentar usar oci_num_rows antes de pegar qualquer resultado (como é comum no MySQL), você irá obter como resultado 0 (zero). Se tentar chamar durante o laço em que faz a leitura de todos os resultados, terá um valor incremental (primeiro 1, depois 2, depois 3, assim por diante), até chegar no valor real apenas após o laço ter terminado.

Um modo de contornar seria tentar usar a função oci_fetch_all (para pegar todos os resultados) e aí contá-los, mas quando eu tentei usá-la, obtive o misterioso erro ORA 24374 (que parece ser mais um bug, já que eu não fiz o que a mensagem diz). Vi uma possível solução, que não parecia ter nada a ver com o problema, e a maioria das pessoas disse que essa possível solução não funcionou, por isso nem vou colocá-la aqui.

Se para você usar oci_num_rows depois de ler todos os resultados com oci_fetch_array for um problema, e o oci_fetch_all também não funcionar, a única solução será fazer um consulta com SELECT COUNT(CAMPO) as NUM_ROWS FROM TABELA antes para apenas ler o número de resultados que virá.

Listas (ENUM e SET)

Não existem ENUM ou SET no Oracle. Você terá que usar uma coluna do tipo VARCHAR (ou VARCHAR2) mesmo.

Porém, existem dois jeitos de fazer o Oracle validar os valores que inserimos: através de uma trigger, ou através da constraint CHECK. Vamos ver como fazer a validação através de CHECK.

Vamos supor que a tabela PRODUTO contém uma coluna que descreve a categoria do produto. Os valores possíveis para categoria são A, B, C, D e E. O comando SQL então ficaria:

ALTER TABLE PRODUTO ADD CONSTRAINT CK_PRODUTO_CATEGORIA CHECK ( categoria IN('A','B','C','D','E'));

O que se pode ganhar aqui ao usar strings é na hora de criar índices: como sabemos que os valores possíveis são poucos, é possível usar índices BITMAP do Oracle, que são índices bastante otimizados para colunas que possuam baixa cardinalidade.

Em último caso (desespero?), o que se pode fazer é simplesmente não fazer o SGBD validar os valores entrados. Isso é válido no caso de sistemas que já estão bastante estáveis e com documentação bem clara sobre os possíveis valores para as colunas.

Transações

Em MySQL, caso você esteja usando versão com suporte à transações, o padrão é operar com AUTO COMMIT. No Oracle não, mas isso irá te afetar quando você estiver executando uma atualização diretamente no banco, via linha de comando ou SQL Developer por exemplo, sem o auxílio de nenhuma biblioteca de linguagem de programação.

Em Java, se você estiver usando JDBC puro, pode usar o método setAutoCommit() com valor true da interface Connection. Se estiver usando JPA, sempre é obrigado a começar uma transação antes de qualquer alteração ao banco (através do método begin da classe Transaction).

Em PHP, se você estiver usando PDO, ele irá atuar em modo AUTO COMMIT. Usando diretamente a extensão OCI (e não o PDO), se você usar a função oci_execute sem definir um segundo parâmetro, o padrão é OCI_COMMIT_ON_SUCCESS. Se você alterar este parâmetro para outro valor, ainda pode chamar explicitamente commit usando a função oci_commit.

Outras dicas

Remova a formatação de textos e datas das suas consultas. Deixe isso a cargo de sua linguagem. Isso vai tornar sua vida mais fácil quando migrar de um banco para outro. Além disso, as linguagens de programação costumam ter várias conveniências a mais do que seu banco para estes casos.

Também óbvio, e pelo mesmo motivo, não use outras funções específicas de um determinado banco.

Em relação ao uso de procedures, existe uma discussão eterna sobre usá-las ou não, pois elas envolvem conhecimento que para alguns deveriam estar apenas no aplicativo. Não vou entrar neste mérito, pois cada uma das abordagens tem suas vantagens. Mas o fato é: se você quer fazer um aplicativo independente de banco, terá que evitar as procedures.

Conclusão

Estes foram os obstáculos que tive que enfrentar. Como eu disse, sempre que aparecerem novas dificuldades relacionadas à esta migração, eu atualizo este artigo.

Como já pedi no início, caso tenha notado algum erro, precisa de ajuda, ou conhece algum modo melhor de fazer algo que descrevi, me escreva.