Tag: oracle

Cuidado com o ADD_MONTHS do Oracle

Sabe aqueles erros esquisitos onde você olha o código é pensa: “não é possível, tá tudo certo…”?

O cenário

Há muito tempo, trabalhei na migração de diversas procedures de um sistema financeiro de SQL Server para Oracle. Eram procedure complexas com milhares de linhas, muitos cursores e algumas péssimas práticas.

A princípio, a ordem era para migrarmos tudo manualmente. Mas, depois de analisar algumas ferramentas de migração automática, descobri que se fizesse alguns ajustes nas procedures em SQL Server poderia migrá-las inteiramente para Oracle usando o SQL Developer, disponibilizado gratuitamente pela Oracle.

Ao migrar uma procedure, o  SQL Developer utiliza algumas rotinas de adaptação para o Oracle, que deve estar disponível num package chamado qlserver_utilites. Fiz um parser simples que substituía as chamadas a esse package por funções nativas do Oracle, evitando criar código desnecessário e que diminuiria a performance.

Escrevi um documento com os ajustes necessários e se tornou o padrão do projeto. Assim conseguimos manter o código-fonte único.

Entretanto, identificamos que algumas operações específicas do sistema geraram lançamentos com valores estranhos, mas a maioria estava correta. No caso, o cálculo envolvia períodos de datas e a primeira observação é que a data inicial do período era dia 30, o último do mês.

Cálculos daqui, cálculos dali, verificou-se que, apenas para esse tipo de período, a procedure estava calculando um dia a mais numa diferença entre as datas. Uma dessas datas era calculada somando-se um número X de meses.

A origem do problema

No Oracle, a rotina usada para somar datas é ADD_MONTHS. Seria o equivalente de DATEADD do SQL Server.

Entretanto, essa rotina segue um padrão ANSI muito esquisito:  se a data é o último dia do mês, ao somar X meses, o resultado será sempre no último dia do mês.

Vamos supor que a data fosse 28/02, ao somar um mês, o resultado seria 31/03, a não ser no ano bissexto. Para 30/04, por exemplo, somando-se um mês, o resultado seria 31/05.

Isso estava gerando a distorção nos cálculos.

Solução

Foi necessário criar uma function para somar meses e que mantivesse o dia fixo, a não ser quando o mês do resultado tinha menos dias  do que o mês original.

Conclusões

Não confiar na implementação de uma rotina somente pelo nome, principalmente entre softwares tão diferentes.

No caso de migração, analisar a fundo a equivalência de funções, métodos, classes, etc. Se possível, com testes unitários.

Script de Geração de Carga de Dados (Oracle e SQL Server)

No dia-a-dia do desenvolvimento, é comum precisarmos reproduzir uma determinada situação ocorrida no cliente. Só que precisamos dos dados exatos para isso!

Geralmente solicitamos SELECTs ou backups. Mas e quando não dá?

Em ambientes com restrições onde não é possível solicitar um backup e um SELECT não é suficiente, pois precisamos trabalhar com os dados e não apenas visualizá-los, existe uma alternativa prática para recuperar os dados do ambiente.

Script que gera INSERTs

Inicialmente desenvolvi um script para Oracle em PL/SQL para migração de dados entre ambientes. O resultado foi bom e fizemos também uma versão para SQL Server.

No caso do SQL Server trata-se de uma procedure que, quando executada, gera INSERTS a partir dos dados de uma tabela. Veja os exemplos abaixo:

-- Gera INSERTS para todos os registros da tabela TAB1
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB1',
    @BANCO_ORIGEM = 'MINHA_BASE',
    @BANCO_DESTINO = DEFAULT,
    @OWNER = DEFAULT,
    @WHERE = DEFAULT,
    @GERAR_DELETE = 0

-- Gera INSERTs para todos os registros da tabela TAB2
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB2',
    @GERAR_DELETE = 0

-- Gera INSERT para a tabela de produtos somente para o produto 'PROD' da empresa 'EMP1'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'PRODUTOS',
    @WHERE = 'CODPRODUTO = ''PROD'' AND LECOLCOD = ''EMP1'' '

-- Gera INSERT da tabela BOLETOS somente para o boleto '123'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'BOLETOS',
    @BANCO_ORIGEM = 'MINHA_BASE',
    @BANCO_DESTINO = DEFAULT,
    @OWNER = DEFAULT,
    @WHERE = 'CODBOLETO = 123'

Observações

No primeiro exemplo, é definido o banco de origem como  “MINHA_BASE”. Esse parâmetro é opcional, se o banco de dados já estiver selecionado (“USE”).

Os parâmetros com valor “DEFAULT” indicam que o SQL deve usar o valor padrão definido pela procedure. Eles poderiam ser simplesmente omitidos.

O parâmetro @GERAR_DELETE define se o script gerado vai conter também um comando “DELETE” para apagar os dados da tabela.

Os parâmetros @OWNER, @BANCO_ORIGEM e @BANCO_DESTINO podem ser omitidos e somente precisam ser usados se houver necessidade de executar os scripts a partir do master ou com um usuário cujo owner é diferente do owner  do banco de dados. O padrão para o @OWNER é “dbo”.

Caso esses parâmetros sejam informados, o caminho para os objetos será escrito como no exemplo:

AB_BANCO_DESTINO.dbo.TABELA

No segundo exemplo, o parâmetro @WHERE faz com que os dados gerados e o DELETE (se houver) contenham o filtro passado. Então também é possível criar uma carga parcial com o filtro desejado.

Somente é preciso tomar cuidado com a saída. O Management Studio limita a quantidade de texto retornado, então é recomendável alterar a configuração  Tools > Options > Query Results > SQL Server > Results to Text > Maximum number of characters... para o valor de “4000” ou simplesmente jogar a saída para um arquivo.

Vantagens

Armazenamento e migração de dados de forma transparente, sendo possível analisar e alterar os dados.

O desenvolvedor pode selecionar exatamente os registros que deseja. São poucas as ferramentas que permitem geração de scripts com filtro por registros.

Não é necessário que a pessoa que vai executá-lo saiba usar uma ferramenta de geração de scripts e tenha que selecionar as tabelas ou até registros manualmente.

Problemas

Tabelas com tipos de dados que não podem ser representados em texto.

Tabelas muitos grandes vão gerar scripts impossíveis de editar.

Versão para Oracle

A versão Oracle atual não tem todas as opções da versão SQL Server, mas já foi utilizada para migração de dados entre ambientes com sucesso.

Disponível em

https://github.com/utluiz/database-insert-script-generator

Dúvidas, sugestões, correções ou apontamento de erros são bem-vindos!

Data loading script generator (Oracle and SQL Server)

In software development, there’s a common scenario where we need to transport data between separated environments.

For instance, we routinely have to reproduce customer’s problems. In order to do that, we need the same data. Sometimes we just enter the production environment or request a backup, but in a few cases we aren’t allowed to do that! Some environments have security constraints that makes it impossible to obtain a backup, for example. We can request a query, but it isn’t proper because we want to work with the data.

How we can accomplish that?

Script that generates INSERTs

At first I developed an Oracle PL/SQL script to migrate data between different environments. It worked well so I did a SQL Server T-SQL version too.

Indeed, I’m talking about a procedure that generates INSERT commands from table data. Check out the following examples:

-- Generates INSERTS for all records from table TAB1
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB1', -- table name
    @BANCO_ORIGEM = 'MY_DATABASE', -- source database
    @BANCO_DESTINO = DEFAULT, -- target database
    @OWNER = DEFAULT,
    @WHERE = DEFAULT,
    @GERAR_DELETE = 0 -- generate DELETE command?

-- Generates INSERTs for all records from table TAB2
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB2', -- table name
    @GERAR_DELETE = 0 -- generate DELETE command?

-- Generates INSERTs for product table, but only when code is 'PROD' and company is 'COMP1'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'PRODUCT', -- table name
    @WHERE = 'ID = ''PROD'' AND ID_COMPANY = ''COMP1'' '

--Generates INSERTs for client table when client code is 123
EXEC GET_INSERT_SCRIPT
    @TABELA = 'CLIENT', -- table name
    @BANCO_ORIGEM = 'MY_DATABASE', -- source database
    @BANCO_DESTINO = DEFAULT, -- target database
    @OWNER = DEFAULT,
    @WHERE = 'ID = 123'

Remarks

In the first example, we set the source database as MY_DATABASE. This parameter is optional if the database is already selected with USE command.

The parameters with DEFAULT value instructs SQL Server to use the default value defined in procedure signature. They could be omitted.

The parameter @GERAR_DELETE defines if the generated script will have a DELETE command to remove the table data before the insertions.

The parameters @OWNER, @BANCO_ORIGEM, @BANCO_DESTINO can also be omitted and only need to be informed if you execute the script from master or from a different database owner. The default owner is dbo.

If these parameters are informed, the “path” to the database objects will be like this:

TARGET_DATABASE.dbo.TABLE

In the second example, the parameter @WHERE adds a filter clause to the generated data and DELETE command. This way we can load data selectively.

We only need be cautious with the output. Management Studio limits the text output size, so my advice is to change the configuration Tools > Options > Query Results > SQL Server > Results to Text > Maximum number of characters... to 4000 (maximum characters per line) or just output to file.

Advantages

  • Store and migrate data in a clear and simple way.
  • Easily see and change all data.
  • Select exactly the data needed. Many tools don’t give you the option to export data selectively.
  • It ins’t necessary deploy any tool. The person who will generate the scripts just need to run a single script.

Potential problems

  • Tables with data types that cannot be represented as text cannot be generated.
  • Large tables will generate huge scripts hard to open in an editor.

Oracle version

The Oracle version haven’t all features I implemented in SQL Server, but as I told you in the beginning, it was successfully used to migrate data between different environments.

Available in

https://github.com/utluiz/database-insert-script-generator

Questions, suggestions, and bug fixes are welcome!

Creative Commons O blog State of the Art de Luiz Ricardo é licenciado sob uma Licença Creative Commons. Copie, compartihe e modifique, apenas cite a fonte.