Categoria: Oracle

Privilégio de INSERT | Oracle

Leave a comment

Privilégio de INSERT | Oracle

Será que quando se dá o privilégio de INSERT numa tabela a um role isso implicitamente atribui a esse role a possibilidade de “ler” (SELECT) linhas da tabela?

Para começar considere-se a tabela “Data” mostrada na Figura 1 (pertencente ao esquema IMDB) à qual se vai atribuir a autorização de inserção de linhas ao role sousa:

SQL> GRANT INSERT ON “Data” TO sousa;

Figura 1: Tabela Data.

Agora falta testar a hipótese. Para tal entramos como sousa, e:

SQL> SELECT * FROM “Data”;

O resultado, como seria de esperar, é uma mensagem de erro:

                              ORA-01031: insufficient privileges
                              01031. 00000 –  “insufficient privileges”

Apesar de em termos organizacionais não fazer grande sentido que alguém que pode registar dados não os consiga ler, de facto os privilégios nos sistemas de bases de dados relacionais cumprem com o objectivo com que foram pensados. O que pode ser facilmente demonstrado digitando o seguinte comando:

SQL> INSERT INTO “Data” (“Chave da Data”, “Descrição Completa da Data”,
          “Descrição Abreviada da Data”, “Século”, “Data-SQL”)
          VALUES (‘5’, ‘5 de Janeiro de 2017′, ’05/01/2017’, ‘XXI’,
          TO_DATE(‘2017-01-05’, ‘YYYY-MM-DD’));

Geração de Dados | Oracle

Leave a comment

Geração de Dados | Oracle

A geração de valores aleatórios em Oracle para valores numéricos e datas, por exemplo, e recorrendo ao utilitário DBMS_RANDOM é fácil e traduz-se em resultados que fazem sentido e que podem posteriormente ser utilizados em testes.
Já a geração de cadeias alfanuméricas recorrendo aquele utilitário resulta em valores sem utilização prática. Acresce ainda que muitas vezes os testes de performance a bases de dados têm que ser feitos utilizando tabelas interligadas entre si por chaves estrangeiras. Ora isso não fazível recorrendo a strings do tipo ‘wqT45dfreDSkutRR’.
Então a melhor forma de ultrapassar esta deficiência é utilizar tabelas de lookup em que inserimos listas com possíveis valores, por exemplo, para nomes de pessoas, ou denominação de localidades ou países.
Figura 1: A tabela Colaborador.
A Query 1 que nos vai servir de caso de estudo devolve três campos: “Número de Funcionário”, “Nome” e “Data de Entrada”, em que para o primeiro e último campo pode-se aplicar o pacote DBMS_RANDOM mas em que o “Nome” será gerado a partir da coluna “Nome” da tabela “Colaborador” (Figura 1).
Query 1: Geração de valores aleatórios
SELECT “Número de Funcionário”, “Nome”, “Data de Entrada”
  FROM (SELECT TRUNC(DBMS_RANDOM.VALUE (100, 1000)) “Número de Funcionário”,
          SYSDATE + DBMS_RANDOM.VALUE (-365* 15, -1) “Data de Entrada”
        FROM lamy
      CONNECT BY LEVEL <= 100) tabela1
    LEFT OUTER JOIN (SELECT DISTINCT “Nome” FROM “Colaborador”
        ORDER BY DBMS_RANDOM.VALUE) tabela2 ON 1 = 1
ORDER BY DBMS_RANDOM.VALUE;

Conjunto resultado:
Figura 2: Resultado da geração de valores aleatórios para a coluna Nome.

Oracle | Data aleatória

Leave a comment
Pretende-se gerar uma data aleatória entre 2006-12-01 e 2006-12-20 (um intervalo de 20 dias). Para fazer o cálculo transforma-se a data para o dia juliano em número: TO_CHAR(TO_DATE(‘2006-12-01’, ‘YYYY-MM-DD’), ‘J’):
SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(
       TO_CHAR(TO_DATE(‘2006-12-01’, ‘YYYY-MM-DD’), ‘J’),
       TO_CHAR(TO_DATE(‘2006-12-01’, ‘YYYY-MM-DD’), ‘J’)+20)), ‘J’)
FROM DUAL;

Trigger para validação de data | Oracle

Leave a comment

Considerem-se as seguintes tabelas:

  • Curso

  • Disciplina

  • Disciplina no Curso

Pretende-se impedir que sejam introduzidas na tabela “Disciplina no Curso” datas de cessação inferiores à data actual. Como em Oracle não se pode validar datas comprando-as com o SYSDATE através de CHECK, então uma outra possibilidade será o da utilização de um trigger insert/update que faça essa verificação.

CREATE OR REPLACE TRIGGER trg_check_data_cessacao
     BEFORE INSERT OR UPDATE ON “Disciplina no Curso”
FOR EACH ROW
     BEGIN
       IF( :new.”Data de Cessação” > SYSDATE )
         THEN
       RAISE_APPLICATION_ERROR( -20001,
           ‘Data de Cessação inválida: esta data tem que ser maior que o valor actual da data.’);
       END IF;
     END;

SYSDATE numa restrição CHECK em Oracle

Leave a comment
Como o valor de SYSDATE (data do sistema) é quase sempre diferente em cada momento de avaliação de uma cláusula CHECK então o Oracle não nos deixa fazer uma restrição desse tipo.

Imagine-se, por exemplo, que se quer inserir apenas valores que sejam maiores do que SYSDATE e considere-se que o valor de SYSDATE vai aumentado com o tempo:

[Data de Fim] > SYSDATE

30-JAN-2012 > 26-DEC-2011
………………………………………
30-JAN-2012 > 10-FEB-2013

O valor de SYSDATE vai aumentando até ultrapassar o valor inserido na coluna, tornando o CHECK inválido e, consequentemente, a base de dados inconsistente. Dai não ser possível utilizar este tipo de regra de (anti)-integridade.

A validação terá que ser feita por intermédio de um trigger do tipo BEFORE INSERT OR UPDATE.

Máquina do tempo…em Oracle

Leave a comment

Acontece por vezes que alguns dados podem inadvertidamente ser apagados ou modificados e não há tempo para os restaurar a partir de um dispositivo de backup. O Oracle possui uma tecnologia bastante útil denominada Flashback que permite que se façam queries “a um passado recente”. O “quanto” é permitido recuar depende do espaço disponível em UNDO.
Suponha-se que na última hora se apagou qualquer linha da tabela Curso e que agora é imperioso saber que dados foram removidos: 

SELECT * FROM  “Curso” AS OF TIMESTAMP SYSDATE-1/24;

Através desta característica é possível fazer o seguinte:

  • Pesquisar dados que já não existem (“do passado”); 
  • Analisar os metadados de modo a evidenciar o historial de alterações à base de dados; 
  • Recuperar tabelas ou linhas para um determinado ponto do tempo; 
  • Fazer rollback de transacções enquanto a base de dados de produção continua activa; 
  • Fazer a gestão de transacções.

Tabela dual ou dummy

Leave a comment

Este tipo especial de tabela, denominada em inglês de dummy, serve o propósito especial de fazer algumas operações sobre conjuntos sem aceder a tabelas de dados propriamente ditas. Isso alivia a carga do sistema e impede, por exemplo, que se façam utilizações indevidas a tabelas com muitas linhas.
Este tipo de tabelas pode ser utilizado para trabalhar com triggers ou, mais simplesmente, para obter uma marca temporal. Convém que a tabela dummy tenha uma única linha para evitar que o resultado se multiplique num output com o mesmo número de linhas da tabela.

Como fazer e utilizar?
Em primeiro lugar deve criar-se a tabela:
——————————————————–
–  DDL para a tabela LAMY
——————————————————–
CREATE TABLE lamy (
lam varchar(2)
);
Em segundo lugar insere-se uma linha na tabela:
INSERT INTO lamy (lam) VALUES (‘a’);
Aplicações:

  1. Obter a data actual do sistema:
    • select SYSDATE from lamy;
    • Resultado:
  2. Fazer um cálculo:
    • SELECT (2+4) / 2 FROM lamy;
  3. Criar uma tabela com 100000 linhas com dados aleatórios:
    • CREATE TABLE tabela_teste AS
      SELECT LEVEL id, SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) valor_data, DBMS_RANDOM.string(‘A’, 20) valor_texto
      FROM lamy
      CONNECT BY LEVEL <= 100000;
      • NOTA: O tempo necessário para inserir 100 000 linhas na tabela pode ser muito longo. Quem quiser pode experimentar com um número menor de linhas. O tempo de execução aumenta (quase) exponencialmente se a tabela lamy tiver mais do que uma linha.