UNIVERSIDADE REGIONAL DE BLUMENAU
CENTRO DE CIÊNCIAS EXATAS E NATURAIS
CURSO DE CIÊNCIAS DA COMPUTAÇÃO
(Bacharelado)
SOFTWARE PARA REPLICAÇÃO DE OBJETOS ENTRE
DUAS INSTÂNCIAS DE UM SGBD ORACLE
TRABALHO DE CONCLUSÃO DE CURSO SUBMETIDO À UNIVERSIDADE
REGIONAL DE BLUMENAU PARA A OBTENÇÃO DOS CRÉDITOS NA
DISCIPLINA COM NOME EQUIVALENTE NO CURSO DE CIÊNCIAS DA
COMPUTAÇÃO — BACHARELADO
ANDRINO CARLOS DE SOUZA JUNIOR
BLUMENAU, MARCO/2003
2003/03-22
SOFTWARE PARA REPLICAÇÃO DE OBJETOS ENTRE
DUAS INSTÂNCIAS DE UM SGBD ORACLE
ANDRINO CARLOS DE SOUZA JUNIOR
ESTE TRABALHO DE CONCLUSÃO DE CURSO, FOI JULGADO ADEQUADO
PARA OBTENÇÃO DOS CRÉDITOS NA DISCIPLINA DE TRABALHO DE
CONCLUSÃO DE CURSO OBRIGATÓRIA PARA OBTENÇÃO DO TÍTULO DE:
BACHAREL EM CIÊNCIAS DA COMPUTAÇÃO
Prof. Alexander Roberto Valdameri — Orientador na FURB
Prof. José Roque Voltolini da Silva — Coordenador do TCC
BANCA EXAMINADORA
Prof. Alexander Roberto Valdameri
Prof. Maurício Capobianco Lopes
Prof. Wilson Pedro Carli
ii
DEDICATÓRIA
Este trabalho de conclusão de
curso é dedicado à Andrino
Carlos e Maria de Lourdes,
pelo amor, carinho e força
recebida ao longo destes anos
de graduação.
iii
AGRADECIMENTOS
Aos meus pais, Andrino Carlos e Maria de Lourdes, que me deram força e energia
necessária para a realização desta graduação.
A empresa Bunge Alimentos, que forneceu-me equipamentos e ajuda nas dificuldades
encontradas por esta monografia.
Ao meu orientador, prof. Alexander Roberto Valdameri, pela confiança e perseverança
na conclusão deste trabalho, que sempre com idéias oportunas, auxiliou-me nos problemas
técnicos.
A minha querida amiga Joice, onde sua ajuda e companheirismo sempre estavam
presentes nos momentos difíceis.
A todos aqueles que, direta ou indiretamente, contribuíram para a realização deste
trabalho.
iv
SUMÁRIO
LISTA DE ABREVIATURAS...............................................................................................vii
RESUMO................................................................................................................................viii
ABSTRACT .............................................................................................................................ix
LISTA DE FIGURAS............................................................................................................... x
LISTA DE QUADROS............................................................................................................xi
1 INTRODUÇÃO ..................................................................................................................... 1
1.1
OBJETIVOS DO TRABALHO.................................................................................. 2
1.2
ORGANIZAÇÃO....................................................................................................... 2
2 AMBIENTE DESENVOLVIMENTO X PRODUÇÃO..................................................... 4
3 ORACLE ................................................................................................................................ 6
3.1
INSTÂNCIAS E OBJETOS DE UMA INSTÂNCIA................................................. 6
3.1.1
Tabelas ................................................................................................................... 6
3.1.2
Visões...................................................................................................................... 7
3.1.3
Unidades De Programação .................................................................................... 7
3.1.4
Sinônimos ............................................................................................................... 8
3.1.5
Índices..................................................................................................................... 8
3.1.6
Clusters................................................................................................................... 8
3.2
LINGUAGEM DE CONSULTA SQL ....................................................................... 9
3.2.1
Literais.................................................................................................................... 9
3.2.2
Comentários ......................................................................................................... 10
3.2.3
Tipos De Dados .................................................................................................... 10
3.2.4
Expressões ............................................................................................................ 12
3.2.5
Condições ............................................................................................................. 13
v
3.3
CURSORES ............................................................................................................. 13
3.3.1
Laços For Em Cursores........................................................................................ 15
3.3.2
Cursores Dinâmicos ............................................................................................. 16
3.3.3
Manipulação De Cursores Dinâmicos ................................................................. 17
4 DESENVOLVIMENTO DO SOFTWARE....................................................................... 19
4.1
REQUISITOS PRINCIPAIS DO PROBLEMA ....................................................... 19
4.2
ESPECIFICAÇÃO ................................................................................................... 19
4.2.1
Diagrama De Contexto......................................................................................... 20
4.2.2
Diagrama De Fluxo De Dados (Dfd)................................................................... 20
4.2.3
Diagrama De Entidade/Relacionamento ............................................................. 21
4.3
IMPLEMENTAÇÃO ............................................................................................... 23
4.3.1
Técnicas E Ferramentas Utilizadas ..................................................................... 23
4.3.2
Apresentação Da Implementação......................................................................... 27
5 CONSIDERAÇÕES FINAIS.............................................................................................. 35
5.1
CONCLUSÃO.......................................................................................................... 35
5.2
LIMITAÇÕES .......................................................................................................... 35
5.3
EXTENSÕES ........................................................................................................... 36
6 ANEXO 1 – CÓDIGO FONTE DA ROTINA .................................................................. 37
REFERÊNCIAS BIBLIOGRÁFICAS ................................................................................. 42
vi
LISTA DE ABREVIATURAS
AD
Administrador de Dados
ANSI
American National Standards Institute
DBA
Data Base Administrator
DER
Diagrama Entidade e Relacionamento
DFD
Diagrama de Fluxo de Dados
PL/SQL
Program Language / Structured Query Language
SGBD
Sistema Gerenciador de Banco de Dados
SGBDR
Sistema Gerenciador de Banco de Dados Relacional
SQL
Structured Query Language
vii
RESUMO
Este trabalho consiste no desenvolvimento de uma ferramenta que executa cópia de
dados entre tabelas de diferentes instâncias (ambiente produção x ambiente desenvolvimento)
juntamente com suas respectivas restrições de parentesco. O protótipo especificado e
desenvolvido neste trabalho efetua documentação das cópias realizadas, além de garantir que
as restrições de parentescos entre as tabelas também estejam existentes na instância de
destino, como por exemplo a nota fiscal e seus respectivos itens.
viii
ABSTRACT
This study consists in a development tool that analyzes and restructures PL/SQL
source codes, using legibility patterns, obtained in library research. The prototype specified
and developed in this work performs documentation of the programs and subroutines, shows
warnings about some programming constructions and format the attributes of the source code
like indentation, keywords, table columns and internal variables.
ix
LISTA DE FIGURAS
FIGURA 1 : DIAGRAMA DE CONTEXTO ........................................................................... 20
FIGURA 2 : DIAGRAMA DE FLUXO DE DADOS............................................................... 21
FIGURA 3 : TELA DE PARAMETRIZAÇÃO........................................................................ 28
FIGURA 4 : LISTA DE INSTÂNCIAS.................................................................................... 28
FIGURA 5 : TELA PRINCIPAL DO SOFTWARE ................................................................. 29
FIGURA 6 : ERRO DE INSTÂNCIA INCORRETA ............................................................... 29
FIGURA 7 : INSTÂNCIAS DISPONÍVEIS............................................................................. 30
FIGURA 8 : TABELA INEXISTENTE NO DESTINO ........................................................... 30
FIGURA 9 : ERRO CÓPIA VIA SQL PLUS ........................................................................... 31
FIGURA 10 : CÓPIA VIA SOFTWARE.................................................................................. 32
FIGURA 11 : RESULTADO CÓPIA VIA SOFTWARE......................................................... 33
FIGURA 12 : CONSULTA LOGS DE CÓPIAS ...................................................................... 34
FIGURA 13 : CAMPO COMANDO INSERT ......................................................................... 34
x
LISTA DE QUADROS
QUADRO 1 – COMENTÁRIO DE LINHA ÚNICA ............................................................... 10
QUADRO 2 – COMENTÁRIO DE MÚLTIPLAS LINHAS ................................................... 10
QUADRO 3 – TIPOS DE DADOS INTERNOS ...................................................................... 11
QUADRO 4 – EXEMPLO DE DECLARAÇÃO DE CURSOR............................................... 14
QUADRO 5 – CURSOR COM PARÂMETROS ..................................................................... 15
QUADRO 6 – EXEMPLO DE INICIALIZAÇÃO DE UM CURSOR..................................... 15
QUADRO 7 – REFERÊNCIA INDIVIDUAL DE CAMPOS DO CURSOR........................... 16
QUADRO 8 – VARIÁVEL DO TIPO CURSOR ..................................................................... 18
QUADRO 9 – COMANDOS FETCH E CLOSE...................................................................... 18
QUADRO 10 – CABEÇALHO DA SUBROTINA .................................................................. 23
QUADRO 11 – PROCEDIMENTO ORGANIZA_WHERE ................................................... 24
QUADRO 12 – PROCEDIMENTO RECRIA_WHERE.......................................................... 25
QUADRO 13 – PROCEDIMENTO GRAVA_LOG ................................................................ 27
xi
1
1 INTRODUÇÃO
No processo de testes de software, umas das maiores dificuldades encontradas
atualmente pelos desenvolvedores é a igualdade de ambientes entre desenvolvimento
e
produção.
O desenvolvedor, com o intuito de executar todas as tarefas possíveis em um ambiente
semelhante ao de um usuário, muitas vezes acaba executando um simples overview das
funcionalidades do sistema devido aos poucos dados existentes, acarretando assim, em
possíveis falhas no projeto final, dificultando sua manutenção.
Segundo
Lowell
(1985),
a
manutenção
consome
recursos
vitais
para
o
desenvolvimento de novos projetos. Sendo assim, o ideal é que este processo de manutenção
seja o mais rápido e eficiente possível. Para a averiguação das alterações efetuadas pelos
desenvolvedores, o correto seria possuir um ambiente de testes o mais próximo possível do
original, algo que raramente consegue-se obter.
Em tratando-se de sistemas baseados em estruturas de armazenamento em bancos de
dados, faz-se necessário a replicação dos objetos que constituem o modelo físico do sistema
em ambos os ambientes citados, para que se possa avaliar efetivamente as rotinas e seu
funcionamento.
Atualmente no mercado existem diversos Sistemas Gerenciadores de Banco de Dados
– SGBD, como exemplo pode-se citar o MySQL, que oferecem um mecanismo de replicação
de objetos entre duas instâncias. Porém, outros como o SGBD Oracle, objeto de estudo deste
trabalho, não oferece tal recurso, o que dificulta a consolidação do projeto através dos testes e
da validação.
Segundo Ramalho (1999), o Oracle é um sistema de gerenciamento de banco de dados
relacional que, além do banco de dados, possui uma instância de servidor Oracle. O Oracle
possui duas estruturas, a física e a lógica. Elas são separadas, podendo-se gerenciar o
armazenamento físico sem afetar o acesso lógico de armazenamento.
Assim, esta proposta tem por finalidade solucionar uma grande deficiência existente
em diversas empresas no que tange à criação, manutenção, integridade e confiabilidade de
2
uma base de dados adequada a ser utilizada em uma das principais etapas do desenvolvimento
do sistema, no caso, a fase de testes. Esta fase, conforme Hetzel (1987), deve ser considerada
uma atividade necessária na coleta de informações, de modo a permitir que se possa avaliar o
trabalho com eficiência. Sendo assim, será desenvolvido um software para replicação de
objetos entre duas instâncias de um SGBD Oracle, uma vez que o mesmo auxiliará os
desenvolvedores oferecendo um ambiente mais propício para testes e validações dos sistemas
desenvolvidos.
A tecnologia utilizada para desenvolvimento da ferramenta foi o Form Builder 6.0i,
que faz parte do pacote de desenvolvimento chamado Developer/2000, existente no Oracle.
1.1 OBJETIVOS DO TRABALHO
Este trabalho tem como objetivo principal o desenvolvimento de um software para
realizar cópias entre duas instâncias de um SGBD Oracle 8i com o intuito de auxiliar o
desenvolvedor na automatização do controle das restrições existentes por falta de
relacionamentos existentes entre objetos.
Os objetivos específicos do trabalho são:
a) executar a cópia de dados de tabelas entre duas instâncias previamente informadas;
b) armazenar históricos das cópias efetuadas para possíveis auditorias;
c) gerenciamento de chaves duplicadas e foreing keys / tabelas inexistentes;
d) gerenciamento de permissões de objetos para usuários.
1.2 ORGANIZAÇÃO
O trabalho é composto por quatro capítulos. Neste primeiro capítulo foram
apresentados a origem do trabalho, objetivo e organização.
No segundo capítulo são apresentadas as principais características do ambiente de
testes x ambiente de desenvolvimento a nível de banco de dados.
No terceiro capítulo são apresentados conceitos e características do banco de dados
Oracle, além de informações sobre a linguagem de programação PL/SQL.
3
No quarto capítulo é descrita a especificação do protótipo, assim como detalhes sobre
sua implementação.
No quinto capítulo são apresentadas as conclusões e sugestões a respeito do trabalho
desenvolvido.
4
2 AMBIENTE DESENVOLVIMENTO X PRODUÇÃO
Segundo Pressman (1995), a atividade de teste de software é um elemento crítico da
garantia de qualidade de software e representa a última revisão de especificação, projeto e
codificação. Se a atividade de teste for conduzida com sucesso ela descobrirá erros no
software. Como um benefício secundário, a atividade de teste demonstra que as funções de
software aparentemente estão trabalhando de acordo com as especificações e que os requisitos
de desempenho foram aparentemente cumpridos.
Para se obter confiança nos testes e facilidade no andamento desta fase, é de grande
importância possuir um ambiente propício para o desenvolvedor executar seus testes. O ideal
seria possuir o mesmo ambiente que o usuário final, mas como isto não é possível devido a
várias limitações, procura-se aproximar-se ao máximo deste.
Conforme Herbert (1997), as atividades de execução e de seleção de dados de entrada
são as mais importantes na fase de testes: a arte de testar um programa inicia quando a pessoa
que conduz o teste seleciona os valores de entrada.
Em se tratando de SGBD, o maior problema refere-se ao grande volume de dados que
o ambiente de produção possui. Quando o desenvolvedor procura igualar as bases, ocorre
diversas vezes estouro de área. Mesmo quando tenta-se buscar apenas dados referente ao seu
teste, acaba sofrendo problemas devido às restrições de parentesco nas tabelas. Tem-se por
exemplo uma nota fiscal, que quando copiada a nota, é necessário copiar também os itens da
nota, os produtos, os vencimentos, o cliente e assim por diante. Caso algum destes dados não
existir no ambiente de desenvolvimento, ocorre erro de integridade.
O SGBD Oracle não possui nenhuma ferramenta que permita ao desenvolvedor dizer
somente que deseja copiar a nota fiscal e que faça implicitamente a cópia das demais tabelas
relacionadas automaticamente. Esta é uma grande desvantagem, pois o desenvolvedor perde
uma grande parte do tempo destinado ao teste em apenas montar a base de dados.
Outro fator importante que praticamente é inexistente no ambiente de desenvolvimento
é a falta de concorrência. Muitos testes podem ser executados com sucesso no ambiente de
desenvolvimento, mas no momento da execução se tornam verdadeiros fracassos, onde o
5
sistema passa a maior parte de tempo “preso” por algum outro usuário, sem a necessidade do
travamento do registro.
Temos ainda como possíveis pontos de falhas no teste os vícios do desenvolvedor em
testar somente o que foi alterado, pois se for testar todo o componente, a base disponível é
muito restrita e com tão poucos dados, talvez seja gasto mais tempo testando do que alterando
o sistema.
Por último, temos ainda o ambiente cliente servidor, onde o cliente encontra-se em
locais distantes do SGBD, o que faz o sistema possuir um pequeno delay no processamento da
informação, algo que no ambiente de testes passa despercebido, tendo em vista que os testes
normalmente são feitos em ambientes locais.
Este trabalho tem como objetivo solucionar a parte de cópia de tabelas com registros
dependentes, ou seja, criar uma melhoria no SGBD Oracle para facilitar a montagem do
banco de desenvolvimento.
6
3 ORACLE
Segundo Ault (1995), o Oracle é um sistema de gerenciamento de banco de dados
relacional (SGBDR). Um SGBDR armazena dados em tabelas chamadas de relações, as quais
representações bidimensionais de dados, onde as linhas – chamadas de tuplas no jargão
relacional – representam registros, e as colunas – chamadas de atributos – são as partes de
informação contidas no registro.
Os três principais aspectos do modelo relacional, no qual o Oracle se baseia, são as
estruturas, as operações e as regras de integridade, conforme Morais (1995). As estruturas são
os objetos que guardam os dados de um banco de dados. As estruturas e os dados podem ser
manipulados através de operações. As operações são as ações que permitem aos usuários
manipularem os dados e as estruturas de um banco de dados. Essas operações devem aderir a
um conjunto de regras de integridade predefinidas. As regras de integridade são leis que
governam quais operações são permitidas nos dados e nas estruturas de um banco de dados,
com o propósito de protegê-los de operações indevidas.
3.1 INSTÂNCIAS E OBJETOS DE UMA INSTÂNCIA
Uma instância é uma coleção de objetos ligados à identificação única de um usuário
em um banco de dados, conforme Morais (1995).
Segundo Oracle (1992) objetos de uma instância são as estruturas lógicas que se
referem diretamente aos dados de uma base de dados. Objetos de um esquema incluem
estruturas como tabelas, visões, seqüências, store procedures, sinônimos, índices, clusters e
links entre banco de dados.
3.1.1 TABELAS
Uma tabela é a unidade básica usada para guardar os dados de um banco de dados
Oracle, isto é, ela contém todos os dados acessíveis pelos usuários.
Uma tabela é composta por linhas e colunas e possui um único nome que a identifica.
Cada uma das colunas das tabelas também possui apenas um único nome identificador, um
7
tipo de dado associado, um tamanho e uma escala ou precisão (usadas somente para os tipos
de dados numéricos). Uma linha é uma coleção de colunas com informações correspondente a
um único registro, segundo Morais (1995).
3.1.2 VISÕES
Segundo Oracle (1992), uma visão é uma representação customizada dos dados de uma
ou mais tabelas (ou outras visões). Conforme Morais (1995) uma visão é representada e
armazenada no dicionário de dados como um simples texto. Quando uma visão é referenciada
em um comando SQL, o Oracle combina o comando que referencia a visão com o comando
que a define no dicionário de dados, analisa o comando composto, armazena-o na área de
memória com os comandos SQL compartilháveis entre os usuários e, finalmente, o executa.
Um comando que referencia uma visão, somente é analisado novamente (para obter um plano
de execução do comando), quando não existe nenhum comando idêntico definido na área de
memória compartilhada que armazena os comandos SQL.
Sempre que possível, o Oracle combina a consulta que referencia uma visão com a
consulta que a define. Isso é feito para que o comando final não referencie mais um objeto, no
caso a visão.
3.1.3 UNIDADES DE PROGRAMAÇÃO
O termo “unidades de programação” refere-se a stored procedures, functions e
packages, conforme Morais (1995) .
As procedures ou functions são um grupo de instruções SQL e PL/SQL agrupados e
que formam uma unidade para executar uma tarefa específica. As functions e procedures
permitem a combinação da flexibilidade e facilidade da linguagem SQL com a funcionalidade
procedural da liguagem PL/SQL.
As packages permitem um meio bastante eficaz de encapsular as procedures e
functions relacionadas em unidades lógicas dentro de um banco de dados e oferecem
benefícios no armazenamento e manutenção destas procedures e functions. Elas oferecem
8
também nova funcionalidade a essas estruturas, assim como uma maior funcionalidade e
melhor performance para o banco de dados.
3.1.4 SINÔNIMOS
Um sinônimo é um alias (apelido) para uma tabela, visão, seqüência ou unidades de
programação. Um sinônimo não é um tipo de objeto por si mesmo, mas referencia
diretamente um objeto, segundo Oracle (1992).
Um sinônimo pode ser público ou privado. Um sinônimo público é pertencente a um
grupo especial de usuários chamado PUBLIC e é acessível a todos os usuários do banco de
dados. Um sinônimo privado está contido na instância de um usuário específico e disponível
somente para este e aos outros usuários a que este concedeu permissões.
3.1.5 ÍNDICES
Os índices são estruturas opcionais associadas com tabelas, que podem ser criadas para
aumentarem a performance da recuperação de dados, conforme Oracle (1992).
3.1.6 CLUSTERS
Clusters são um método opcional de armazenamento de tabelas de dados, segundo
Oracle (1992).
Segundo Morais (1995), clusters agrupam duas ou mais tabelas, fisicamente guardadas
juntas nos mesmos blocos de dados, pelo fato de elas compartilharem informações entre si.
Um cluster também pode ser usado para conter somente uma tabela. Como as linhas
das tabela agrupadas em um cluster estão fisicamente juntas, o seu uso pode melhorar o
acesso aos dados em algumas ocasiões.
9
3.2 LINGUAGEM DE CONSULTA SQL
O termo “SQL” é originado do acrônimo de “Structured Query Language”- Linguagem
de Consulta Estruturada. Segundo Date (1989), a linguagem SQL é composta por um grupo
de facilidades para definição, manipulação e controle de dados de um banco de dados
relacional. A partir da publicação do trabalho de E.F. Codd, “A Relational Model of Data for
Large Shared Data Banks” – um modelo relacional de dados para grandes bancos de dados
compartilhados, em 1970, várias empresas, incentivadas por este trabalho, desenvolveram
produtos para suportar a gerência de um banco de dados.
Em 1974, nos laboratórios da IBM, definida por D. D. Chamberlin e outros, surgiu a
linguagem Structured Query Language (SEQUEL). No ano seguinte, foi implementado um
protótipo da mesma, chamado SEQUEL-SRM. No final dos anos 70, e início dos anos 80, a
linguagem SQL foi implementada em alguns produtos da própria IBM, e também de outros
fabricantes, que também passaram a suportar a linguagem.
O interesse pela padronização da linguagem iniciou-se em 1982, por intermédio do
órgão de padronização American National Standards Institute (ANSI). O processo de
padronização foi baseado na linguagem SQL definida pela IBM. Posteriormente, os padrões
foram reconhecidos também pela International Standards Organization (ISO), sob forma da
norma ISO 9075 Date (1989). A seguir são apresentados os principais elementos da
linguagem SQL.
3.2.1 LITERAIS
Os termos “literal” e “valor constante” são sinônimos, e referem-se a valores de dados
fixos. Como exemplo, pode-se citar ‘BCC’ e ‘101’, que são literais caractere e inclusos entre
aspas simples e 500, que é um literal numérico, conforme Oracle (1997).
10
Muitas expressões e funções do SQL necessitam de valores literais caractere e
numéricos. Literais também podem ser expressões como parte de expressões e condições.
3.2.2 COMENTÁRIOS
Comentários não afetam a execução de procedimentos SQL, mas aumentam a
legibilidade das aplicações, portanto, devem ser usados sempre que possível.
Comentários podem ser expressos de duas formas: linha única e linhas múltiplas.
Comentário de linha única é representado pelo delimitador “--“, conforme o quadro 1:
Quadro 1 – Comentário de linha única
SELECT cd_empregado,
nome
FROM empresa
WHERE cd_empresa = 65;
-- AND cd_filial = 134;
Esta linha não será considerada
Fonte: Adaptado de Oracle (1997).
O comentário de múltiplas linhas é representado pelos delimitadores “/*” para iniciar
um comentário, e “*/” para encerrar um comentário, confome o quadro 2:
Quadro 2 – Comentário de múltiplas linhas
/*
A rotina abaixo bloqueia todos os clientes devedores
Implementado em 26/10/2002 */
UPDATE cliente
SET id_bloqueado = ‘SIM’
WHERE dt_liquidacao >= TRUNC(SYSDATE);
Fonte: Adaptado de Oracle (1997).
3.2.3 TIPOS DE DADOS
Todo literal ou coluna manipulada pelo Sistema Gerenciador de Banco de Dados
(SGBD) Oracle tem um tipo de dado, sendo que o tipo de dado do valor associa um conjunto
11
de propriedades, que servem para que o banco de dados faça o tratamento correto para cada
valor.
No processo de criação de tabelas, clusters e stored procedures no banco, são
utilizados tipos de dados internos. Estes tipos de dados definem o domínio de valores que
cada coluna ou argumento pode possuir, conforme o quadro 3.
Quadro 3 – Tipos de Dados Internos
Tipo de Dado
Descrição
String de tamanho variável em bytes com máximo de 4000 e mínimo de
VARCHAR2(tam)
1. Deve ser especificado um tamanho para VARCHAR2.
String de tamanho variável em bytes com máximo de 4000 e mínimo de
NVARCHAR2(tam)
1, de acordo com o conjunto de caracteres nacional escolhido. Deve ser
especificado um tamanho para NVARCHAR2.
NUMBER(p,s)
LONG
Numérico com precisão p e escala s. A precisão pode variar de 1 a 38 e a
escala de –84 até 127.
Dados em formato caracterer de tamanho 2 gigabytes, ou 231 -1 bytes.
Período de data válida de 1º de janeiro de 4712 AC até 31º Dezembro de
DATE
4712 DC.
Dados binários em formato Raw com tamanho em bytes. O tamanho deve
RAW(tam)
ser especificado e é no máximo 2000.
LONG RAW
Dados binários de tamanho variável até 2 gigabytes.
String hexadecimal que representa o endereço único de uma linha em uma
ROWID
tabela.
CHAR(tam)
Caracter de tamanho fixo, com tamanho em bytes. Possui tamanho
mínimo e padrão de 1 byte e máximo de 2000 bytes.
Caracter de tamanho fixo, com tamanho em bytes. Possui tamanho
NCHAR(tam)
mínimo e padrão de 1 byte e máximo de 2000 bytes, de acordo com o
conjunto de caracteres nacional escolhido.
MLSLABEL
Formato binário de um label de sistema operacional. Usado para manter
12
compatibilidade com versões antigas.
Objeto caracter grande contendo caracteres de byte simples. Tamanho
CLOB
variável de conjunto de caracteres não é permitido. Tamanho máximo de
4 gigabytes.
Objeto caracter grande contendo caracteres de múltiplos bytes. Tamanho
NCLOB
máximo de 4 gigabytes. Usado para armazenar dados de conjuntos de
caracteres nacionais.
BLOB
Objeto binário grande. Tamanho máximo é de 4 gigabytes.
Contém o localizador para um arquivo binário de grande armazenado fora
BFILE
da base de dados. Tamanho máximo de 4 gigabytes.
Fonte: Traduzido de Oracle (1997).
3.2.4 EXPRESSÕES
A expressão é a combinação de um ou mais valores, operadores e funções do SQL que
resultam em um valor. Uma expressão geralmente assume o tipo de dado dos seus
componentes. Abaixo, um exemplo de uma expressão, que utiliza funções e operadores; esta
expressão adiciona sete dias para a data atual, remove a quantidade de horas adjacentes e
converte o resultado para o tipo de dado CHAR (Oracle, 1997):
TO_CHAR(TRUNC(SYSDATE+7))
As expressões podem ser utilizadas em:
a)
a lista de seleção do comando SELECT;
b)
como condição das cláusulas WHERE e HAVING;
c)
as cláusulas CONNECT BY, START WITH, e ORDER BY;
d)
a cláusula VALUES do comando INSERT;
e)
a cláusula SET do comando UPDATE.
13
3.2.5 CONDIÇÕES
Um condição especifica uma combinação de uma ou mais expressões e operadores
lógicos que resultam em VERDADEIRO, FALSO ou desconhecido, segundo Oracle (1997).
A condição pode ser usada na cláusula WHERE destes comandos:
a)
DELETE;
b)
SELECT;
c)
UPDATE.
No comando SELECT, podem ser usadas condições nas seguintes cláusulas:
a)
WHERE;
b)
START WITH;
c)
CONNECT BY;
d)
HAVING.
Pode-se dizer que a condição seria um tipo de dado “lógico”, mas a linguagem não
suporta formalmente este tipo.
Operadores lógicos podem combinar múltiplas condições em uma única condição,
conforme o exemplo abaixo, que utiliza o operador AND para combinar duas condições:
(1 = 1) AND (5 < 7)
3.3 CURSORES
Oracle utiliza áreas de trabalho para executar comandos SQL e armazenar a
informação processada. Um cursor permite percorrer esta área de trabalho para ler as
informações armazenadas. Há dois tipos de cursores: implícitos e explícitos. Cursores
implícitos são criados pelo Oracle a cada execução de um comando SQL, mas não podem ser
utilizados em um programa em PL/SQL. Cursores explícitos podem ser manipulados em um
programa PL/SQL.
A declaração de um cursor segue a sintaxe DECLARE CURSOR nome_cursor IS
comando_select; Por exemplo,
14
Quadro 4 – Exemplo de Declaração de Cursor
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
A declaração do cursor associa um comando SQL a ele. Um curso permitirá ler, linha
a linha, o resultado de um consulta. Da mesma maneira que um arquivo, o cursor deve ser
aberto, percorrer uma a uma cada linha, até o final do conjunto de dados.
O comando OPEN executa a consulta associada ao cursor, obtém um conjunto de
linhas como resposta a consulta e posiciona o cursor na primeira linha. O comando FETCH
retorna os valores da linha corrente e avança uma posição na lista. O comando CLOSE
desabilita o cursor, liberando o espaço de memória ocupado pelo conjunto resposta.
A sintaxe do comando OPEN é:
OPEN nome_cursor;
A sintaxe do comando CLOSE é:
CLOSE nome_cursor;
E do comando FETCH é
FETCH nome_curso INTO [lista_variáveis].
A lista de variáveis pode conter uma ou mais variáveis. Há duas alternativas: utilizar
variáveis individuais, de tipos correspondentes ao retorno do SELECT; ou utilizar uma
variável de um tipo registro adequado ao SELECT. Para o cursor declarado acima, é possível:
FETCH c1 INTO my_empno, my_ename, my_deptno;
ou
FETCH c1 INTO my_record;
Todos cursores possuem quatro propriedades que podem ser úteis em um programa:
%FOUND: indica se a última operação FETCH encontrou uma linha. Seu valor é
verdadeiro até a última linha.
%NOTFOUND: indica se a última operação FETCH não encontrou uma linha. Seu
valor é falso até a última linha.
%ISOPEN: indica se o cursor está aberto ou não.
15
%ROWCOUNT: retorna o número de linhas do cursor.
Para testar se o cursor retornou algum valor válido ou já atingiu o final do conjunto,
pode-se testar a propriedade FOUND do cursor. Por exemplo,
FETCH C1 INTO ....
WHILE C1%FOUND LOOP
Um cursor pode, ainda, ter parâmetros. Deste modo, a definição do cursor fica
alterada:
Quadro 5 – Cursor com Parâmetros
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
A inicialização do cursor, que pode ser por um comando OPEN ou FOR-LOOP é feita
passando o valor do parâmetro. Por exemplo:
Quadro 6 – Exemplo de Inicialização de um Cursor
FOR emp_record IN emp_cursor(20) LOOP
END LOOP;
3.3.1 LAÇOS FOR EM CURSORES
Em muitas situações, percorrer todas as linhas resultantes de uma tabela pode ser
realizado com o comando FOR, e não com OPEN-FETCH-CLOSE. Um laço FOR em
cursores exige uma variável, que é declarada implicitamente dentro do comando. No exemplo
abaixo, a variável emp_rec é do tipo registro. Para referir campos individuais da variável, é
utilizada a notação ponto (.).
16
Quadro 7 – Referência Individual de Campos do Cursor
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
FOR emp_rec IN c1 LOOP
...
salary_total := salary_total + emp_rec.sal;
END LOOP;
3.3.2 CURSORES DINÂMICOS
Embora a maioria dos programas em PL/SQL execute comandos previsíveis, que
permitem uma declaração estática de um cursor, em algumas situações é necessário alterar
dinamicamente, em tempo de execução, o comando SELECT que define o cursor. As
seguintes situações são típicas:
a) É necessário utilizar um comando de definição de dados (DDL), como o CREATE,
um comando de atribuição ou revogação de direitos (GRANT e REVOKE) ou um comando
de controle de sessão (ALTER SESSION). Em PL/SQL, estes comandos são necessariamente
dinâmicos.
b) É necessário compor a clausula WHERE de acordo com condições que o usuário
escolhe ou não na interface.
O comando adequado é o EXECUTE IMMEDIATE, cuja sintaxe é:
EXECUTE IMMEDIATE comando_sql
[INTO {variável1, variável2,
. ]... | variável_registro}]
[USING parâmetro1, parâmetro2, ...};
O comando SQL é especificado na primeira. Na clausula INTO, indica-se as variáveis
que receberão o retorno do comando, ou apenas uma variável do tipo registro. Na clausula
USING indica-se os parâmetros do comando. Abaixo há um conjunto de exemplos:
17
a) EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; executa o comando ALTER SESSION.
b) EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt
NUMBER)'; - executa o comando de criação de uma tabela.
c) sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
d) EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; executa o comando INSERT, colocando os valores passados por parâmetro na
clausula USING.
e) sql_stmt := 'SELECT * FROM emp WHERE empno = :1';
f) EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; - executa
o commando SELECT, atribuindoa resposta à variável emp_rec. A identificação do
empregado é passada por parâmetro.
g) EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
h) USING dept_id; - retira uma tupla da tabela dept.
3.3.3 MANIPULAÇÃO DE CURSORES DINÂMICOS
No caso de comandos SELECT que retornem mais de uma linha no conjunto resposta,
é necessário utilizar os comandos OPEN-FETCH-CLOSE para percorrer, linha a linha, o
conjunto resposta.
Para o comando OPEN utiliza-se a seguinte variação sintática:
OPEN {variável_cursor } FOR string_sql
[USING argumento1[, argumento2]...];
Uma variável do tipo cursor deve ser declarada, a priori. O comando SQL é montado
em uma variável do tipo string, conforme a necessidade do programa, e colocado na clausula
18
FOR. Se houverem parâmetros no comando SQL, os argumentos são passados na clausula
USING. O exemplo abaixo ilustra este caso. Veja que a variável emp_cv é declarada como
sendo do tipo cursor e no comando SELECT há um parâmetro, cujo valor é atribuído na
clausula USING.
Quadro 8 – Variável do Tipo Cursor
DECLARE
TYPE EmpCurTyp IS REF CURSOR; -- define como tipo REF CURSOR
emp_cv EmpCurTyp; -- declara a variável cursor
my_ename VARCHAR2(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR – abre a variável cursor
'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
...
END;
Os comandos FETCH e CLOSE permanecem semelhantes, como mostra o trecho de
código a seguir:
Quadro 9 – Comandos Fetch e Close
FETCH emp_cv INTO my_ename, my_sal;
END LOOP;
CLOSE emp_cv; -- fecha a variável cursor
19
4 DESENVOLVIMENTO DO SOFTWARE
Neste tópico será abordado a especificação, recursos e restrições do software.
Apresentar-se-á também um caso de uso, comum para desenvolvedores.
4.1 REQUISITOS PRINCIPAIS DO PROBLEMA
O software irá realizar cópia de tabelas entre duas instâncias inclusive as restrições
caso não existam na instância de destino. Todas as tabelas que foram copiadas e a quantidade
de linhas serão armazenadas para possíveis auditorias.
As consistências que o software irá fazer para executar a cópia são:
a) verificação da existência da tabela na instância de origem;
b) verificação da exatidão da cláusula de cópia;
c) verificação da estrutura das tabelas entre as duas instâncias.
Alguns avisos serão gerados a respeito de problemas contornáveis pelo software. São
eles:
a) verificação da existência da tabela de destino, sendo que, caso não exista, será
criada;
b) verificação da falta de registros pai/filho, sendo que neste caso, o sistema será
responsável por realizar a cópia.
O protótipo gera também um resumo após toda a cópia estar realizada.
4.2 ESPECIFICAÇÃO
A especificação é apresentada através do diagrama de contexto, diagrama de fluxo de
dados, modelo entidade-relacionamento e o dicionário de dados.
Através da engenharia reversa do dicionário de dados do SGBD Oracle, extraiu-se o
modelo entidade-relacionamento e o dicionário de dados. Isto faz-se necessário para
identificar os locais onde os dados relativos aos objetos encontram-se armazenados.
Para as especificações utilizou-se a ferramenta Case Power Designer.
20
4.2.1 DIAGRAMA DE CONTEXTO
Este diagrama de contexto representa o fluxo de dados, que representa as tarefas que
podem ser executadas na ferramenta. A Figura 1 mostra o diagrama de contexto.
Figura 1 : Diagrama de contexto
1
Parâmetro
DBA
Cópia
Desenvolvedor
IADG2000
Estatística
Log da Cópia
4.2.2 DIAGRAMA DE FLUXO DE DADOS (DFD)
O DFD é utilizado para particionar o sistema, mostrando os processos de cada entrada
e saída da ferramenta, apresentado na Figura 2.
21
Figura 2 : Diagrama de fluxo de dados
1
DBA
Parâmetro
Manter
Parâmetro
Parâmetro_Ok
PRM_COPA_TABELA
2
Estatística
DBA
Extrair
Estatística
Estatística
LOG_COPIA_TABELA
3
Cópia
Desenvolvedor
Executar
Cópia
Copia_Ok
TABELA_INFORMADA
4
Desenvolvedor
Log_Copia
Log_Copia_Ok
Extrair Log
LOG_COPIA_TABELA_
4.2.3 DIAGRAMA DE ENTIDADE/RELACIONAMENTO
O Modelo Entidade/Relacionamento é utilizado para exemplificar quais as tabelas
internas do SGBD foram utilizadas e quais tabelas são próprias do sistema. As tabelas do
Metadados do Oracle não possui relacionamento, tendo em vista que a presença de chaves
primárias é inexistente.
22
Figura 3 : Diagrama entidade/relacionamento
ALL_CONSTRAINTS
OWNER
CONSTRAINT_NAME
CONSTRAINT_TYPE
TABLE_NAME
SEARCH_CONDITION
R_OWNER
R_CONSTRAINT_NAME
DELETE_RULE
STATUS
DEFERRABLE
DEFERRED
VALIDATED
GENERATED
BAD
RELY
LAST_CHANGE
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(1)
VARCHAR2(30)
LONG
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(9)
VARCHAR2(8)
VARCHAR2(14)
VARCHAR2(9)
VARCHAR2(13)
VARCHAR2(14)
VARCHAR2(3)
VARCHAR2(4)
DATE
ALL_CONS_COLUMNS
OWNER
CONSTRAINT_NAME
TABLE_NAME
COLUMN_NAME
POSITION
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(4000)
NUMBER
ALL_TAB_COLUMNS
OWNER
TABLE_NAME
COLUMN_NAME
DATA_TYPE
DATA_TYPE_MOD
DATA_TYPE_OWNER
DATA_LENGTH
DATA_PRECISION
DATA_SCALE
NULLABLE
COLUMN_ID
DEFAULT_LENGTH
DATA_DEFAULT
NUM_DISTINCT
LOW_VALUE
HIGH_VALUE
DENSITY
NUM_NULLS
NUM_BUCKETS
LAST_ANALYZED
SAMPLE_SIZE
CHARACTER_SET_NAME
CHAR_COL_DECL_LENGTH
GLOBAL_STATS
USER_STATS
AVG_COL_LEN
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(106)
VARCHAR2(3)
VARCHAR2(30)
NUMBER
NUMBER
NUMBER
VARCHAR2(1)
NUMBER
NUMBER
LONG
NUMBER
RAW(32)
RAW(32)
NUMBER
NUMBER
NUMBER
DATE
NUMBER
VARCHAR2(44)
NUMBER
VARCHAR2(3)
VARCHAR2(3)
NUMBER
ALL_TABLES
OWNER
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
FREELISTS
FREELIST_GROUPS
LOGGING
BACKED_UP
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT
AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
DEGREE
INSTANCES
CACHE
TABLE_LOCK
SAMPLE_SIZE
LAST_ANALYZED
PARTITIONED
IOT_TYPE
TEMPORARY
SECONDARY
NESTED
BUFFER_POOL
ROW_MOVEMENT
GLOBAL_STATS
USER_STATS
DURATION
SKIP_CORRUPT
MONITORING
CLUSTER_OWNER
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(3)
VARCHAR2(1)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(10)
VARCHAR2(10)
VARCHAR2(5)
VARCHAR2(8)
NUMBER
DATE
VARCHAR2(3)
VARCHAR2(12)
VARCHAR2(1)
VARCHAR2(1)
VARCHAR2(3)
VARCHAR2(7)
VARCHAR2(8)
VARCHAR2(3)
VARCHAR2(3)
VARCHAR2(15)
VARCHAR2(8)
VARCHAR2(3)
VARCHAR2(30)
ALL_DB_LINKS
OWNER
DB_LINK
USERNAME
HOST
CREATED
VARCHAR2(30)
VARCHAR2(128)
VARCHAR2(30)
VARCHAR2(2000)
DATE
log_copia_tabela
prm_copia_tabela
cd_instance_origem
cd_instance_destino
nm_owner_origem
nm_owner_destino
varchar(10)
varchar(10)
varchar2(30)
varchar2(30)
cd_tabela_oracle
cd_usuario
dt_copia_tabela
ds_clausula_where
cd_instance_origem
cd_instance_destino
nm_owner_origem
nm_owner_destino
qt_registros_copiados
varchar2(30)
varchar2(12)
date
varchar2(2000)
varchar(50)
varchar(50)
varchar2(30)
varchar2(30)
number(09)
23
As tabelas constantes no diagrama entidade/relacionamento são:
a) All_Tables Dados das tabelas do SGBD Oracle.
b) All_Constraints Restrições das tabelas.
c) All_Cons_Colums Restrições das linhas das tabelas.
d) All_Db_Links Instâncias pertencentes ao SGBD.
e) Prm_Copia_Tabela Parâmetro utilizado para executar a cópia.
f) Log_Copia_Tabela Resultado após a execução da cópia.
4.3 IMPLEMENTAÇÃO
4.3.1 TÉCNICAS E FERRAMENTAS UTILIZADAS
Inicialmente seria utilizada a ferramenta Oracle Designer versão 6.0, mas tendo em
vista que a mesma não possuía suporte a todos os diagramas desejados (diagrama de fluxo de
dados), optou-se pela utilização da ferramenta Power Designer.
Para realizar a implementação do protótipo, a partir da especificação realizada, foi
utilizada a ferramenta Forms Developer 6i. Esta ferramenta foi escolhida por sua natividade
ao SGBD Oracle, além de ser o padrão da Bunge Alimentos, empresa onde o software irá ser
utilizado.
Esta ferramenta não suporta comandos dinâmicos, portanto, foi desenvolvido uma
subrotina que será chamada pelo programa para realizar todo o processo da cópia. O quadro
4 mostra o cabeçalho desta subrotina.
Quadro 10 – Cabeçalho da Subrotina
CREATE OR REPLACE PACKAGE IADG2001 IS
PROCEDURE CONTROLE(P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_CD_TABELA_ORACLE_ORG
P_CD_TABELA_ORACLE_DST
P_DS_CLAUSULA
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2);
PROCEDURE ORGANIZA_WHERE(P_NM_OWNER
IN
VARCHAR2,
P_CD_TABELA_ORACLE IN
VARCHAR2,
P_DS_CLAUSULA
IN OUT VARCHAR2 );
PROCEDURE RECRIA_WHERE(P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
VARCHAR2,
VARCHAR2,
VARCHAR2,
24
P_NM_OWNER_DST
P_CD_TABELA_ORACLE_ORG
P_NM_CONSTRAINT
P_DS_CLAUSULA
PROCEDURE GRAVA_LOG(P_DS_INSERT
P_CD_TABELA_ORACLE
P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_QT_GRAVADOS
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2);
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
NUMBER);
END IADG2001;
O bloco principal que é chamado pelo Forms é a procedure Controle. É recebido como
parâmetro as instâncias de origem e destino, os owners de origem e de destino, a tabela de
origem (de onde será retirado os dados) e a de destino (para onde os dados serão gravados) e a
cláusula de cópia. Na primeira chamada da subrotina, as tabelas de origem e destino serão
iguais, mas quando é utilizada a recursividade interna no programa, as mesmas se tornam
diferentes.
O primeiro passo feito pelo bloco controle é colocar os apelidos nos campos da
cláusula de cópia. Para tanto, a rotina Organiza_Where se encarrega de tal tarefa, conforme
quadro 5. Todos os campos da tabela são pesquisados dentro da tabela interna do Oracle
chamada ALL_TAB_COLUMNS.
Quadro 11 – Procedimento Organiza_Where
PROCEDURE
ORGANIZA_WHERE(P_NM_OWNER
IN
VARCHAR2,
P_CD_TABELA_ORACLE IN
VARCHAR2,
P_DS_CLAUSULA
IN OUT VARCHAR2)
IS
CURSOR C1 IS
SELECT *
FROM ALL_TAB_COLUMNS
WHERE OWNER = P_NM_OWNER
AND TABLE_NAME = P_CD_TABELA_ORACLE;
R1 C1%ROWTYPE;
NR_POS NUMBER(05);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO R1;
EXIT WHEN C1%NOTFOUND;
NR_POS := INSTR(P_DS_CLAUSULA,R1.COLUMN_NAME);
IF NR_POS <> 0 THEN
IF SUBSTR(P_DS_CLAUSULA,NR_POS+LENGTH(R1.COLUMN_NAME),1) IN
(' ','.',')','*','+','-','/',',') THEN -- Verifica se o campo tem complemento
IF NR_POS <> 1 THEN
IF SUBSTR(P_DS_CLAUSULA,NR_POS-1,1) <> '.' THEN
P_DS_CLAUSULA := SUBSTR(P_DS_CLAUSULA,1,NR_POS-1)||
P_CD_TABELA_ORACLE ||'.'||
SUBSTR(P_DS_CLAUSULA,NR_POS,LENGTH(P_DS_CLAUSULA)-NR_POS+1);
END IF;
ELSE
P_DS_CLAUSULA := P_CD_TABELA_ORACLE ||'.'||
P_DS_CLAUSULA;
END IF;
25
END IF;
END IF;
END LOOP;
CLOSE C1;
END;
Em seguida, é voltado para a rotina principal, que tem por objetivo neste momento de
fazer a junção da sintaxe do comando insert com a tabela mais a cláusula de cópia. Após feito
isto, é executado o insert dinamicamente através do comando Execute Immediate. Caso
ocorrer erro de restrição na cópia, é identificado qual o nome da constraint em que o erro
ocorreu, e é passado para a rotina Recria_Where.
A rotina Recria_Where executa o ligamento entre as tabelas e inclui mais sua própria
restrição para copiar somente o que não possui na instância de origem e também somente o
que for necessário para a cópia anterior se realizar com sucesso, conforme quadro 6. Para a
criação do cursor dinâmico foi utilizado o comando Ref Cursor, que neste exemplo está sendo
utilizado para a busca de todas as colunas da restrição na instância de destino.
Quadro 12 – Procedimento Recria_Where
PROCEDURE
RECRIA_WHERE(P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_CD_TABELA_ORACLE_ORG
P_NM_CONSTRAINT
P_DS_CLAUSULA
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2)
IS
TYPE CUR_COLUNA IS REF CURSOR;
C1
CUR_COLUNA;
COMANDO_SQL
COMANDO_SQL_2
VARCHAR2(1000);
VARCHAR2(1000);
NM_TABELA_ORACLE_FK
NM_CAMPO_FK
DS_CLAUSULA_TMP
DS_CLAUSULA_EXISTS
VARCHAR2(30);
VARCHAR2(40);
VARCHAR2(200);
VARCHAR2(200);
NM_TABELA_ORACLE
DS_CLAUSULA
NM_CAMPO
DS_ERRO
DS_ULTIMO_INSERT
VARCHAR2(500);
VARCHAR2(4000);
VARCHAR2(50);
VARCHAR2(500);
VARCHAR2(1000);
W_TABLE_NAME
W_COLUMN_NAME
W_POSITION
BEGIN
VARCHAR2(30);
VARCHAR2(50);
NUMBER(05);
DS_CLAUSULA := P_DS_CLAUSULA;
NM_TABELA_ORACLE := P_CD_TABELA_ORACLE_ORG;
COMANDO_SQL :=
'SELECT
'
'
' FROM
' WHERE
' AND
TABLE_NAME, ' ||
COLUMN_NAME, ' ||
POSITION ' ||
ALL_CONS_COLUMNS@'||P_CD_INSTANCE_DST||
OWNER = :1 ' ||
CONSTRAINT_NAME = :2';
26
OPEN C1 FOR COMANDO_SQL USING P_NM_OWNER_DST, P_NM_CONSTRAINT;
LOOP
FETCH C1 INTO W_TABLE_NAME,
W_COLUMN_NAME,
W_POSITION;
EXIT WHEN C1%NOTFOUND;
BEGIN
COMANDO_SQL_2 := 'SELECT B.COLUMN_NAME , ' ||
' B.TABLE_NAME ' ||
' FROM ALL_CONS_COLUMNS@'||P_CD_INSTANCE_DST||' B, '||
' ALL_CONSTRAINTS@' ||P_CD_INSTANCE_DST||' A '||
' WHERE A.OWNER = :1 '||
' AND A.CONSTRAINT_NAME = :2 ' ||
' AND B.OWNER = A.OWNER ' ||
' AND B.CONSTRAINT_NAME = A.R_CONSTRAINT_NAME '||
' AND B.POSITION = :3';
EXECUTE IMMEDIATE COMANDO_SQL_2 INTO NM_CAMPO_FK, NM_TABELA_ORACLE_FK
USING P_NM_OWNER_DST, P_NM_CONSTRAINT, W_POSITION;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20200,'Erro na busca de campos da constraint '||
P_NM_CONSTRAINT||': '||SQLERRM);
END;
DS_CLAUSULA_TMP := W_TABLE_NAME
|| '.' || W_COLUMN_NAME || ' = ' ||
NM_TABELA_ORACLE_FK || '.' || NM_CAMPO_FK;
IF DS_CLAUSULA IS NOT NULL THEN
DS_CLAUSULA := DS_CLAUSULA_TMP || ' AND ' || DS_CLAUSULA;
ELSE
DS_CLAUSULA := DS_CLAUSULA_TMP;
END IF;
-- Verificação do Not Exists
IF DS_CLAUSULA_EXISTS IS NOT NULL THEN
DS_CLAUSULA_EXISTS := DS_CLAUSULA_EXISTS || ' AND ';
END IF;
DS_CLAUSULA_EXISTS := DS_CLAUSULA_EXISTS ||
NM_TABELA_ORACLE_FK||'.'||NM_CAMPO_FK||' = A.'||
NM_CAMPO_FK;
-END LOOP;
CLOSE C1;
NM_TABELA_ORACLE := NM_TABELA_ORACLE_FK || ', '|| NM_TABELA_ORACLE;
DS_CLAUSULA_EXISTS := ' AND NOT EXISTS (SELECT * FROM '||
NM_TABELA_ORACLE_FK||'@'||P_CD_INSTANCE_DST||
' A WHERE '||DS_CLAUSULA_EXISTS||')';
DS_CLAUSULA := DS_CLAUSULA || DS_CLAUSULA_EXISTS;
IADG2001.CONTROLE(P_CD_INSTANCE_ORG,
P_CD_INSTANCE_DST,
P_NM_OWNER_ORG,
P_NM_OWNER_DST,
NM_TABELA_ORACLE,
NM_TABELA_ORACLE_FK,
DS_CLAUSULA);
END;
Por fim, esta subrotina chama dinamicamente a própria rotina principal do programa,
sendo que a tabela de origem será a tabela anterior mais a tabela que ocorreu o erro de
restrição, e a tabela de destino será somente a tabela em que ocorreu o erro de restrição.
Por fim, após as cópias serem realizadas com sucesso, é gerado o log informando a
quantidade de linhas copiadas e o comando executado. A rotina encarregada desta função é a
27
Grava_Log. O funcionamento do procedimento é simples: são recebidos todos os parâmetros
necessários e é feito um simples insert na tabela LOG_COPIA_TABELA. O quadro 7 mostra
o funcionamento deste procedimento.
Quadro 13 – Procedimento Grava_Log
PROCEDURE GRAVA_LOG(P_DS_INSERT
VARCHAR2,
P_CD_TABELA_ORACLE
VARCHAR2,
P_CD_INSTANCE_ORG
VARCHAR2,
P_CD_INSTANCE_DST
VARCHAR2,
P_NM_OWNER_ORG
VARCHAR2,
P_NM_OWNER_DST
VARCHAR2,
P_QT_GRAVADOS
NUMBER)
is
BEGIN
insert into log_copia_tabela
(CD_TABELA_ORACLE
,CD_USUARIO
,DT_COPIA_TABELA
,DS_CLAUSULA_WHERE
,CD_INSTANCE_ORIGEM
,CD_INSTANCE_DESTINO
,NM_OWNER_ORIGEM
,NM_OWNER_DESTINO
,QT_REGISTROS_COPIADOS)
values
(P_CD_TABELA_ORACLE
,USER
,SYSDATE
,P_DS_INSERT
,P_CD_INSTANCE_ORG
,P_CD_INSTANCE_DST
,P_NM_OWNER_ORG
,P_NM_OWNER_DST
,P_QT_GRAVADOS);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20300,'Erro ao inserir LOG: '||sqlerrm);
END;
A rotina completa se encontra no anexo deste trabalho.
4.3.2 APRESENTAÇÃO DA IMPLEMENTAÇÃO
Para demonstrar a operacionalidade da implementação foi realizado um estudo de
caso, com uma execução passo-a-passo do processo de cópia, utilizando o software
construído.
O primeiro passo é feito pelo DBA, que tem como objetivo parametrizar o sistema,
informando qual será a instância de produção (origem) e a instância de desenvolvimento
(destino). É também de sua responsabilidade informar qual o usuário (owner) das duas
instâncias. Ao executar o aplicativo, a tela inicial é apresentada, conforme a figura 3.
28
Figura 3 : Tela de parametrização
Nesta tela o DBA pode solicitar uma lista de valores com as instâncias disponíveis,
conforme figura 4.
Figura 4 : Lista de instâncias
Após parametrizado o sistema, o processo passa a ser de responsabilidade do
desenvolvedor, que agora possui permissão de executar a cópia. A figura 5 mostra a interface
do desenvoldedor.
29
Figura 5 : Tela principal do software
Os usuários de origem/destino e as instâncias de origem/destino são buscadas
automaticamente pelos parâmetros definidos pelo DBA. O software consiste para que a
instância em que está sendo executado o programa é a mesma que a instância de origem. Isto
se faz necessário para a performance não ficar prejudicada. Caso isto não ocorra, é exibido
uma mensagem conforme a figura 6, que impedirá o usuário de continuar, informando qual a
instância que o software deve executar.
Figura 6 : Erro de instância incorreta
Caso o desenvolvedor não souber o nome completo da tabela, é disponibilizado uma
lista com todas as tabelas pertencentes à instância, conforme figura 7.
30
Figura 7 : Instâncias disponíveis
Caso a tabela escolhida não existir na instância de destino, será aberto uma janela
perguntando se deve-se criar tal tabela, conforme figura 8.
Figura 8 : Tabela inexistente no destino
31
Na figura 9, é mostrado o problema que ocorre quando tenta-se copiar uma tabela de
produção para o ambiente de desenvolvimento, onde a mesma possui integridades faltantes no
destino. Pode-se observar que a constraint T0011_SERIE não está completa, sendo necessário
consultar qual tabela a mesma referencia e quais os atributos referenciados, para então fazer a
carga, correndo o risco desta nova tabela também ocorrer erro na integridade.
Figura 9 : Erro cópia via SQL Plus
Com a utilização do software, basta apenas informar a tabela e a cláusula where, que o
sistema se responsabilizará de buscar os dados, conforme figura 10.
32
Figura 10 : Cópia via software
Após pressionar o botão de executar a cópia, o sistema executará a package IADG2001,
que conterá toda a lógica para a cópia, sendo a mesma implementada através de cursores
dinâmicos e recursividade (ver capítulo 4.3.1).
A figura 11 mostra o resultado da cópia, onde foram copiadas as tabelas
SERIE_CNH_FILIAL_CGC_TESTE, que possuia integridade de dados com 9 (nove)
registros da tabela FILIAL_TESTE, que esta por sua vez possuia integridade de dados com 1
(um) registro da tabela empresa. O comando insert é informado a título de curiosidade para o
desenvolvedor.
33
Figura 11 : Resultado cópia via software
Para acompanhamento por parte do DBA, todos os dados copiados são gravados na
tabela LOG_COPIA_TABELA, onde o mesmo pode fazer a consulta através da tela
demonstrada na figura 12.
34
Figura 12 : Consulta logs de cópias
As estatítica da quantidade de registros copiados se encontram no campo registros
copiados. Como o comando insert é normalmente muito maior que o campo disponibilizado,
basta o DBA dar um clique duplo no campo para abrir uma janela maior, conforme figura 13.
Figura 13 : Campo comando insert
35
5 CONSIDERAÇÕES FINAIS
5.1 CONCLUSÃO
A carência de bibliografias relatando o processo de migração de estruturas no ambiente
produção para o ambiente de desenvolvimento dificultou a contextualização e o
enriquecimento literário deste trabalho, haja vista que tais mecanismos não são explorados e
documentados pelas empresas de informática.
O trabalho procurou apresentar os aspectos relevantes no processo de replicação de
objetos entre dois servidores, em questão, banco de dados Oracle. Procurou-se estabelecer
critérios para gerar documentação das cópias, com criação de históricos, para preservar a
segurança dos dados da empresa.
Durante a implementação, encontrou-se grande dificuldade no gerenciamento de
cursores dinâmicos, tendo em vista que não foi possível fazer a criação de variáveis em tempo
de execução pelo PL/SQL (linguagem procedural imbutida - extensão da SQL). A literatura
nesta área foi restrita devido a este gerenciamento ser presente apenas na última versão da
ferramenta.
A ferramenta foi colocada em testes na empresa Bunge Alimentos SA, sendo que a
meta esperada foi atingida. Atualmente, cerca de 25% dos analistas estão utilizando esta
ferramenta para executar suas cópias. A consulta dos logs das cópias foram disponibilizadas
para todos os analistas e não somente para o DBA.
Acredita-se que este trabalho possa servir de alicerce para novas aplicações e
sobretudo para a geração de documentos que relatam as dificuldades encontradas pelos
analistas de sistemas e programadores na compatibilização de ambientes de desenvolvimento
e produção.
5.2 LIMITAÇÕES
O software possui como limitação a regravação de dados que já existem na instância
de destino. Optou-se em não se fazer esta regravação pois poderia estar excluindo dados que
foram modificados propositadamente pelo desenvolvedor.
36
Outra limitação existente refere-se à cópia de views, pois este tipo de estrutura pode
agregar vária tabelas conjuntas, onde sua implementação ficaria complexa demais e sujeita a
gerar problemas.
5.3 EXTENSÕES
Como sugestão para futuros trabalhos, a inclusão de parametrização deste protótipos,
tais como gerenciamento de chaves duplicadas, cópias de restrições e cópias de view seriam
de grande valia para futuros projetos.
Outro projeto que seria de muita importância seria a geração de concorrência para
tabelas informadas, facilitando assim, uma outra parte de testes de software.
37
6 ANEXO 1 – CÓDIGO FONTE DA ROTINA
-- =======================================================================
-- PROGRAMA
IADG2001
-- FUNCAO
EXECUTAR COPIA ENTRE TABELA
-- ANALISTA
ANDRINO CARLOS DE SOUZA JUNIOR
-- PROGRAMADOR ANDRINO CARLOS DE SOUZA JUNIOR
-- CODIFICACAO /NOV/02
-- =======================================================================
CREATE OR REPLACE PACKAGE IADG2001 IS
PROCEDURE CONTROLE(P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_CD_TABELA_ORACLE_ORG
P_CD_TABELA_ORACLE_DST
P_DS_CLAUSULA
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2);
PROCEDURE ORGANIZA_WHERE(P_NM_OWNER
IN
VARCHAR2,
P_CD_TABELA_ORACLE IN
VARCHAR2,
P_DS_CLAUSULA
IN OUT VARCHAR2 );
PROCEDURE RECRIA_WHERE(P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_CD_TABELA_ORACLE_ORG
P_NM_CONSTRAINT
P_DS_CLAUSULA
PROCEDURE GRAVA_LOG(P_DS_INSERT
P_CD_TABELA_ORACLE
P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_QT_GRAVADOS
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2);
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
VARCHAR2,
NUMBER);
END IADG2001;
/
CREATE OR REPLACE PACKAGE BODY IADG2001 IS
PROCEDURE
VALORES
CONTROLE(P_CD_INSTANCE_ORG
P_CD_INSTANCE_DST
VARCHAR2,
VARCHAR2,
--> INSTANCIA QUE SERA BUSCADO OS
--> INSTANCIA QUE SERA GRAVADO OS
VALORES
P_NM_OWNER_ORG
P_NM_OWNER_DST
P_CD_TABELA_ORACLE_ORG
VALORES A SEREM GRAVADOS
P_CD_TABELA_ORACLE_DST
P_DS_CLAUSULA
DE GRAVACAO
IS
DS_SELECT
DS_INSERT
VARCHAR2(4000);
VARCHAR2(4000);
CD_INSTANCE_ORG
CD_INSTANCE_DST
NM_OWNER_ORG
NM_OWNER_DST
CD_TABELA_ORACLE_ORG
CD_TABELA_ORACLE_DST
DS_CLAUSULA
VARCHAR2(050);
VARCHAR2(050);
VARCHAR2(050);
VARCHAR2(050);
VARCHAR2(500);
VARCHAR2(050);
VARCHAR2(4000);
NR_POS_INICIO NUMBER(05);
VARCHAR2, --> OWNER ORIGEM
VARCHAR2, --> OWNER DESTINO
VARCHAR2,
--> TABELA(S) DE ONDE SERA BUSCADO OS
VARCHAR2, --> TABELA ONDE SERA GRAVADO OS VALORES
VARCHAR2) --> CLAUSULA WHERE UTILIZADA NO SELECT
38
NR_POS_FIM NUMBER(05);
NM_CONSTRAINT VARCHAR2(100);
W_INDEX
EXCEPTION;
PRAGMA EXCEPTION_INIT(W_INDEX,-2291);
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE('','');
DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME => ''||'IADG2001'||'', ACTION_NAME => ' ');
CD_INSTANCE_ORG
CD_INSTANCE_DST
NM_OWNER_ORG
NM_OWNER_DST
CD_TABELA_ORACLE_ORG
CD_TABELA_ORACLE_DST
DS_CLAUSULA
:=
:=
:=
:=
:=
:=
:=
P_CD_INSTANCE_ORG;
P_CD_INSTANCE_DST;
P_NM_OWNER_ORG;
P_NM_OWNER_DST;
P_CD_TABELA_ORACLE_ORG;
P_CD_TABELA_ORACLE_DST;
P_DS_CLAUSULA;
DS_SELECT := 'SELECT DISTINCT '||P_CD_TABELA_ORACLE_DST||'.* FROM ' ||
CD_TABELA_ORACLE_ORG;
ORGANIZA_WHERE(NM_OWNER_ORG,
CD_TABELA_ORACLE_DST,
DS_CLAUSULA);
IF DS_CLAUSULA IS NOT NULL THEN
DS_SELECT := DS_SELECT || ' WHERE ' ||
DS_CLAUSULA;
END IF;
LOOP
BEGIN
DS_INSERT := 'INSERT INTO ' ||
CD_TABELA_ORACLE_DST||
'@'||CD_INSTANCE_DST||
' ('||DS_SELECT||')';
EXECUTE IMMEDIATE DS_INSERT;
GRAVA_LOG(DS_INSERT,
CD_TABELA_ORACLE_DST,
P_CD_INSTANCE_ORG
,P_CD_INSTANCE_DST
,P_NM_OWNER_ORG
,P_NM_OWNER_DST
,SQL%ROWCOUNT);
EXIT;
EXCEPTION
WHEN W_INDEX THEN
NR_POS_INICIO := INSTR(SQLERRM,'CONSTRAINT')+12;
NR_POS_FIM := INSTR(SUBSTR(SQLERRM,NR_POS_INICIO,100),'.')-1;
-- ACHAR NOME DA CONSTRAINT
NR_POS_INICIO := NR_POS_FIM + 1 + NR_POS_INICIO;
NR_POS_FIM := INSTR(SUBSTR(SQLERRM,NR_POS_INICIO,100),')')-1;
NM_CONSTRAINT := SUBSTR(SQLERRM,NR_POS_INICIO,NR_POS_FIM);
RECRIA_WHERE(CD_INSTANCE_ORG,
CD_INSTANCE_DST,
NM_OWNER_ORG,
NM_OWNER_DST,
CD_TABELA_ORACLE_ORG,
NM_CONSTRAINT,
DS_CLAUSULA);
WHEN OTHERS THEN
ROLLBACK;
GRAVA_LOG(DS_INSERT,
CD_TABELA_ORACLE_DST,
P_CD_INSTANCE_ORG
,P_CD_INSTANCE_DST
,P_NM_OWNER_ORG
,P_NM_OWNER_DST
39
,0);
COMMIT;
RAISE_APPLICATION_ERROR(-20100,'ERRO AO INSERIR '|| CD_TABELA_ORACLE_DST ||
': '||SQLERRM);
END;
END LOOP;
COMMIT;
END;
--------------------------------------------------------------- PROCEDURE PARA COLOCAR O ALIAS NA FRENTE DO CAMPO DA TABELA
PROCEDURE ORGANIZA_WHERE(P_NM_OWNER
IN
VARCHAR2,
P_CD_TABELA_ORACLE IN
VARCHAR2,
P_DS_CLAUSULA
IN OUT VARCHAR2)
IS
CURSOR C1 IS
SELECT *
FROM ALL_TAB_COLUMNS
WHERE OWNER = P_NM_OWNER
AND TABLE_NAME = P_CD_TABELA_ORACLE;
R1 C1%ROWTYPE;
NR_POS NUMBER(05);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO R1;
EXIT WHEN C1%NOTFOUND;
NR_POS := INSTR(UPPER(P_DS_CLAUSULA),R1.COLUMN_NAME);
IF NR_POS <> 0 THEN
IF SUBSTR(P_DS_CLAUSULA,NR_POS+LENGTH(R1.COLUMN_NAME),1) IN
(' ','.',')','*','+','-','/',',') THEN -- VERIFICA SE O CAMPO TEM COMPLEMENTO
IF NR_POS <> 1 THEN
IF SUBSTR(P_DS_CLAUSULA,NR_POS-1,1) <> '.' THEN
P_DS_CLAUSULA := SUBSTR(P_DS_CLAUSULA,1,NR_POS-1)||
P_CD_TABELA_ORACLE ||'.'||
SUBSTR(P_DS_CLAUSULA,NR_POS,LENGTH(P_DS_CLAUSULA)-NR_POS+1);
END IF;
ELSE
P_DS_CLAUSULA := P_CD_TABELA_ORACLE ||'.'||
P_DS_CLAUSULA;
END IF;
END IF;
END IF;
END LOOP;
CLOSE C1;
COMMIT;
END;
--------------------------------------------------------------- RECRIAR CLAUSULA WHERE E CHAMAR PROGRAMA IADG2001 NOVAMENTE
PROCEDURE RECRIA_WHERE(P_CD_INSTANCE_ORG
VARCHAR2,
P_CD_INSTANCE_DST
VARCHAR2,
P_NM_OWNER_ORG
VARCHAR2,
P_NM_OWNER_DST
VARCHAR2,
P_CD_TABELA_ORACLE_ORG VARCHAR2,
P_NM_CONSTRAINT
VARCHAR2,
P_DS_CLAUSULA
VARCHAR2)
IS
TYPE CUR_COLUNA IS REF CURSOR;
C1
CUR_COLUNA;
COMANDO_SQL
COMANDO_SQL_2
VARCHAR2(1000);
VARCHAR2(1000);
NM_TABELA_ORACLE_FK
NM_CAMPO_FK
DS_CLAUSULA_TMP
DS_CLAUSULA_EXISTS
VARCHAR2(30);
VARCHAR2(40);
VARCHAR2(200);
VARCHAR2(200);
NM_TABELA_ORACLE
DS_CLAUSULA
VARCHAR2(500);
VARCHAR2(4000);
40
NM_CAMPO
DS_ERRO
DS_ULTIMO_INSERT
W_TABLE_NAME
W_COLUMN_NAME
W_POSITION
BEGIN
VARCHAR2(50);
VARCHAR2(500);
VARCHAR2(1000);
VARCHAR2(30);
VARCHAR2(50);
NUMBER(05);
DS_CLAUSULA := P_DS_CLAUSULA;
NM_TABELA_ORACLE := P_CD_TABELA_ORACLE_ORG;
COMANDO_SQL :=
'SELECT
'
'
' FROM
' WHERE
' AND
TABLE_NAME, ' ||
COLUMN_NAME, ' ||
POSITION ' ||
ALL_CONS_COLUMNS@'||P_CD_INSTANCE_DST||
OWNER = :1 ' ||
CONSTRAINT_NAME = :2';
OPEN C1 FOR COMANDO_SQL USING P_NM_OWNER_DST, P_NM_CONSTRAINT;
LOOP
FETCH C1 INTO W_TABLE_NAME,
W_COLUMN_NAME,
W_POSITION;
EXIT WHEN C1%NOTFOUND;
BEGIN
COMANDO_SQL_2 := 'SELECT B.COLUMN_NAME , ' ||
' B.TABLE_NAME ' ||
' FROM ALL_CONS_COLUMNS@'||P_CD_INSTANCE_DST||' B, '||
' ALL_CONSTRAINTS@' ||P_CD_INSTANCE_DST||' A '||
' WHERE A.OWNER = :1 '||
' AND A.CONSTRAINT_NAME = :2 ' ||
' AND B.OWNER = A.OWNER ' ||
' AND B.CONSTRAINT_NAME = A.R_CONSTRAINT_NAME '||
' AND B.POSITION = :3';
EXECUTE IMMEDIATE COMANDO_SQL_2 INTO NM_CAMPO_FK, NM_TABELA_ORACLE_FK
USING P_NM_OWNER_DST, P_NM_CONSTRAINT, W_POSITION;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20200,'ERRO NA BUSCA DE CAMPOS DA CONSTRAINT '||
P_NM_CONSTRAINT||': '||SQLERRM);
END;
DS_CLAUSULA_TMP := W_TABLE_NAME
|| '.' || W_COLUMN_NAME || ' = ' ||
NM_TABELA_ORACLE_FK || '.' || NM_CAMPO_FK;
IF DS_CLAUSULA IS NOT NULL THEN
DS_CLAUSULA := DS_CLAUSULA_TMP || ' AND ' || DS_CLAUSULA;
ELSE
DS_CLAUSULA := DS_CLAUSULA_TMP;
END IF;
-- VERIFICAÇÃO DO NOT EXISTS
IF DS_CLAUSULA_EXISTS IS NOT NULL THEN
DS_CLAUSULA_EXISTS := DS_CLAUSULA_EXISTS || ' AND ';
END IF;
DS_CLAUSULA_EXISTS := DS_CLAUSULA_EXISTS ||
NM_TABELA_ORACLE_FK||'.'||NM_CAMPO_FK||' = A.'||
NM_CAMPO_FK;
-END LOOP;
CLOSE C1;
NM_TABELA_ORACLE := NM_TABELA_ORACLE_FK || ', '|| NM_TABELA_ORACLE;
DS_CLAUSULA_EXISTS := ' AND NOT EXISTS (SELECT * FROM '||
NM_TABELA_ORACLE_FK||'@'||P_CD_INSTANCE_DST||
' A WHERE '||DS_CLAUSULA_EXISTS||')';
DS_CLAUSULA := DS_CLAUSULA || DS_CLAUSULA_EXISTS;
IADG2001.CONTROLE(P_CD_INSTANCE_ORG,
P_CD_INSTANCE_DST,
P_NM_OWNER_ORG,
P_NM_OWNER_DST,
NM_TABELA_ORACLE,
41
NM_TABELA_ORACLE_FK,
DS_CLAUSULA);
END;
PROCEDURE GRAVA_LOG(P_DS_INSERT
VARCHAR2,
P_CD_TABELA_ORACLE
VARCHAR2,
P_CD_INSTANCE_ORG
VARCHAR2,
P_CD_INSTANCE_DST
VARCHAR2,
P_NM_OWNER_ORG
VARCHAR2,
P_NM_OWNER_DST
VARCHAR2,
P_QT_GRAVADOS
NUMBER)
IS
BEGIN
INSERT INTO LOG_COPIA_TABELA
(CD_TABELA_ORACLE
,CD_USUARIO
,DT_COPIA_TABELA
,DS_CLAUSULA_WHERE
,CD_INSTANCE_ORIGEM
,CD_INSTANCE_DESTINO
,NM_OWNER_ORIGEM
,NM_OWNER_DESTINO
,QT_REGISTROS_COPIADOS)
VALUES
(P_CD_TABELA_ORACLE
,USER
,SYSDATE
,P_DS_INSERT
,P_CD_INSTANCE_ORG
,P_CD_INSTANCE_DST
,P_NM_OWNER_ORG
,P_NM_OWNER_DST
,P_QT_GRAVADOS);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20300,'ERRO AO INSERIR LOG: '||SQLERRM);
END;
END IADG2001;
42
REFERÊNCIAS BIBLIOGRÁFICAS
AULT, Michael R. Oracle 7 - Adminitração & Gerenciamento. Rio de Janeiro: Infobook,
1995.
DATE, C. J. Guia para o padrão SQL. Rio de Janeiro: Campus, 1989.
HERBERT, Juliana Silva e PRICE, Ana Maria de Alencar. Estratégia de geração de dados
de teste baseada na análise simbólica e dinâmica do programa. Fortaleza : XI SBES –
Simpósio Brasileiro de Engenharia de Software. Universidade Federal do Ceara, 1997.
HETZEL, William. Guia Completo ao Teste de Software. Rio de Janeiro: Campus, 1987.
LOWELL, Jay Arthur. Produtividade do programador – um guia para gerentes, analistas e
programadores. Rio de Janeiro: Campus, 1985.
MORAIS, Rinaldo de Oliveira. Oracle 7 Server – Conceitos Básicos. São Paulo: Érica,
1995.
ORACLE CORPORATION. Oracle 7 – Concepts Manual. Estados Unidos da América,
1992.
ORACLE CORPORATION. ch1.htm. Oracle8 SQL reference - release 8.0. 1997.
Arquivo.(15,9 kbytes). CD-Rom. Quadralay WebWorks Publisher 3.5.0.
PRESSMAN, Roger S. Engenharia de software. São Paulo: Makron Books, 1995.
RAMALHO, José Antônio. Oracle 8i. São Paulo: Berkeley Brasil, 1999.