Tabelas externas contendo tipos de dados LOB

Neste artigo será demonstrado como permitir que  tabelas externas possam ler colunas contendo dados do tipo CLOB e BLOB para as operações de carga.

[Conceito]

Tabelas externas: Dispositivo no banco de dados Oracle que permite acessar fontes externas (arquivos textos) e carregá-las para dentro do banco de dados.

Para a demonstração deve-se criar um diretório, apontando para a “filesystem” no sistema operacional, usar o comando abaixo:

SQL> CREATE OR REPLACE DIRECTORY table_load AS '/tmp/';

Directory created.

Nota: Não se esquecer de conceder os privilégios de “READ” e “WRITE” para o usuário manipular os arquivos no diretório criado, usar:

SQL> GRANT READ, WRITE ON DIRECTORY ON table_load TO [USUARIO];

Abaixo estão os arquivos que serão utilizados, estes, deverão ser colocados no caminho indicado na “filesystem” onde o diretório foi criado.

•   teste1clob.txt
•   teste2clob.txt
•   teste1blob.doc
•   teste2blob.doc

Os dois primeiros arquivos (*.txt) estão no formato texto simples, foram criados utilizando o editor de texto VI ou mesmo o Notepad. Os dois arquivos seguintes (*.doc) foram criado utilizando o editor de texto, utilizando o Microsoft Word Documents. Os documentos contêm dados do tipo CLOB e BLOB para serem lidos utilizando uma tabela externa “external table”.

A seguir deve-se criar o arquivo “teste_lobs.txt” que terá o conteúdo indicado abaixo. Informações regulares, datas e referências de arquivos contendo os tipos CLOB e BLOB.

1,um,15-FEB-2013,teste1clob.txt,teste1blob.doc

2,dois,16-FEB-2013,teste2clob.txt,teste2blob.doc

Agora que os todos os arquivos estão no diretório indicado, a tabela externa será criada para realizar a leitura dos dados.

SQL> DROP TABLE teste_external_lob_tab;
DROP TABLE teste_external_lob_tab
*
ERROR at line 1:
ORA-00942: table or view does not existSQL> CREATE TABLE teste_external_lob_tab(
2 number_content NUMBER(10),
3 varchar2_content VARCHAR2(100),
4 date_content DATE,
5 clob_content CLOB,
6 blob_content BLOB
7 )
8 ORGANIZATION EXTERNAL
9 (TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY table_load
11 ACCESS PARAMETERS
12 (RECORDS DELIMITED BY NEWLINE
13 BADFILE table_load:'teste_external_lob_tab_%a_%p.bad'
14 LOGFILE table_load:'teste_external_lob_tab_%a_%p.log'
15 FIELDS TERMINATED BY ','
16 MISSING FIELD VALUES ARE NULL
17 (number_content CHAR(10),
18 varchar2_content CHAR(100),
19 date_content CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
20 clob_filename CHAR(100),
21 blob_filename CHAR(100)
   )
22 COLUMN TRANSFORMS (clob_content FROM LOBFILE (clob_filename) FROM (teste_external_lob_tab) CLOB, blob_content FROM LOBFILE (blob_filename) FROM (teste_external_lob_tab) BLOB)
23 )
24 LOCATION ('teste_lobs.txt')
25 )
26 PARALLEL 2
27 REJECT LIMIT UNLIMITED
28 /

Table created.

Algumas notas importantes sobre tabelas externas utilizando colunas CLOB e BLOB’s:

§  Os nomes das colunas dos tipos de dados LOB (blob_filename e clob_filename), não devem coincidir com os nomes das colunas (blob_content e clob_content). Este dispositivo é utilizado para acessar os arquivos de dados que contém os dados reais (, para apenas nomes de arquivos onde os dados podem ser encontrados.

§  A cláusula COLUMN TRANSFORMS, indicará que as colunas contendo os tipos de dados CLOB e BLOB’s serão carregadas apartir dos arquivos indicados. Para este exemplo será utilizado uma coluna do arquivo para identificar o nome do arquivo e uma constante para o nome do diretório. Para carregar dados a partir de vários diretórios usar um campo adicional no arquivo de dados para identificar objeto de diretório.

A consulta abaixo irá mostrar os dados da  tabela externa estão corretamente referenciados tanto os dados regulares quanto os dados do tipo LOB.

COLUMN varchar2_content FORMAT A16
COLUMN date_content     FORMAT A12
COLUMN clob_content     FORMAT A25

SELECT number_content,varchar2_content,
TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,
clob_content, DBMS_LOB.getlength(blob_content) AS blob_length
FROM teste_external_lob_tab;

NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT             BLOB_LENGTH
-------------- ---------------- ------------ ------------------------ -----------
1              um               15-FEB-2013  Teste 01 coluna CLOB *** 22016
2              dois             16-FEB-2013  Teste 02 coluna CLOB *** 22016

2 rows selected.

Para mais informações sobre tabelas externas acessar, tabelas externas no Oracle. (2011).

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com

O que vem por ai, Oracle 12c

Neste artigo serão abordadas as novas características da nova versão do banco de dados Oracle, prevista para ser lançada no segundo trimestre deste ano.

Depois de seis anos, a primeira mudança é a versão do produto, indicada como 12c, onde o “c” indica um banco de dados voltado para as nuvens (cloud). A Oracle está anunciando aproximadamente 500 novas características, neste artigo serão apresentadas algumas já divulgadas para o mercado.

§  Pluggable Database: A partir da versão 12c, será possível criar um recipiente único banco de dados, chamado contêiner, onde pode-se conectar “plugar” vários bancos de dados neste recipiente. Com essa nova característica, todos os bancos de dados “plugados” poderão compartilhar os mesmos processos do servidor Oracle (chamado de AKA background process) e também a memória. Nas versões anteriores, cada banco de dados tinha seu próprio conjunto de processos e alocação de memória. No Oracle até versões 11gR2, para utilizar a gestão de recursos de banco de dados, era necessário configurar planos de recursos por banco de dados e cada um dos bancos de dados não compartilhava a utilização de recursos de outros bancos de dados no mesmo servidor. No Oracle 12c, todos os bancos de dados poderão usar o mesmo contêiner, que irá gerenciar a utilização de recursos de todos os bancos de dados de forma eficiente.

§  Multi-tenancy Database: O significa? Significa que será possível ter vários bancos de dados em execução no mesmo servidor compartilhando a mesma memória e gestão de processos, essa característica é complementar ao “Pluggable Database”.

§  Map Reduce no banco de dados: O MapReduce^(*) pode ser executado a partir de um código PL/SQL diretamente no banco de dados.

(*)  O Map-Reduce é uma forma utilizada em programação para descrever e implementar programas paralelos. Estes programas são muitas vezes usados ​​para processar um grande conjunto de dados em paralelo.

§  Melhorias no gerenciamento de valores “defaults”: Essa nova característica permitirá alocar um valor ou calcular um valor como “default” para uma coluna.

Exemplo: Se uma variável “x”, que está definida como inteira e como uma chave primária. Será possível, inserir em uma tabela e não será necessário calcular um valor de chave primária. Também será possível especificar um valor “default” quando nenhum valor for incluindo em uma coluna.

§  Maior capacidade de armazenamento para VARCHAR2, NVARCHAR2 e RAW: Aumento de tamanho e armazenamento nos tipos de dados VARCHAR2, NVARCHAR2 e RAW até 32K. Porém será necessário configurar a variável MAX_SQL_STRING_SIZE no init.ora ou no spfile. Mas os novos tamanhos  não serão suportados em tabelas e índices clusterizados e também em tabelas do tipo “index-organized table” (IOT).

    

§  Melhorias no gerenciamento do particionamento: A nova versão permitirá a manutenção assíncrona de índices globais em operações de DROP e TRUNCATE PARTITION. Além da inclusão da funcionalidade CASCADE para ações de TRUNCATE e EXCHANGE. Essas melhorias permitirão executar operações de várias partições com um único comando DDL.

§  Melhoria nas estatísticas do banco de dados: Serão incluidos os métodos “Hybrid”, “Historgrams” e “Session Private” e as estatísticas serão geradas durante as cargas de dados automaticamente.

§  Alterações na área de UNDO: Objetos temporários na área de UNDO serão manipulados na tablespace TEMP. Criação de um novo parâmetro TEMP_UNDO_ENABLED, utilizando o comando ALTER SESSION (ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE). Esta funcionalidade reduzirá o conteúdo da área de UNDO, permitindo um melhor funcionamento. E também reduzirá o tamanho dos “redologs” associados ao UNDO.

§  Recursos para o APEX 4.2 (Oracle Application Express): Serão incluídos novos recursos específicos para a criação de aplicações móveis, incluindo transições, gestão, mudanças e suporte ao HTML 5. Na versão 12c, o APEX suportará o ambiente de banco de dados “Pluggable“. Também suportará os tamanhos de VARCHAR2 estendidos.

§  Novas funcionalidades do SQL Developer: O “datapump” será incluindo para permitir o movimento rápido de dados e também permitirá o planejamento da movimentação dos dados (via scheduler). A ferramenta de comparação de bancos de dados será redesenhada permitindo mais opções e comparações mais confiáveis. O dispositivo “copy database” também será redesenhada (novas opções), a ferramenta de migrações será melhorada, permitindo a geração de relatórios utilizando o formato PDF e DOC. Melhorias na interface incluem (drag and drop), conceder permissões a objetos no banco de dados (GRANT/REVOKE).

§  Criação do tipo BOOLEAN, mas apenas na utilização de códigos PL/SQL.

§  Será possível conceder permissões via ROLES para unidades de programas PL/SQL. Será possível especificar uma lista em que unidades de código poderão chamar um código.

§  Permissão de criação de índices com mesmo conjunto de colunas, porém com tipos diferentes. Será possível, por exemplo, criar um índice bit-map e um índice b-tree.

§  Os resultados dos cursores poderão ser devolvidos implicitamente, sem a necessidade de explicitá-los.

§  As mensagens de aviso ao compilar o código PL/SQL poderão ser filtradas com base na severidade, que serão classificadas em : SEVERE (severa), PERFORMANCE RELATED (relacionado a performance) ou INFORMATIONAL (informacional). As mensagens poderão ser definidas em um nível de sistema ou de sessão, conforme o exemplo abaixo:

alter session set plsql_warnings=’enable:severe’;

alter session set plsql_warnings=’enable:performance’;

alter session set plsql_warnings=’enable:informational’;

Referências:  Oracle OpenWorld 2012.

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com