Oracle SQL*LOADER, UNICODE, export de dados via SQL DEVELOPER e outros bichos
Recentemente num grupo que participo surgiu uma dúvida que englobava esses assuntos todos, o que motivou este artigo – de forma alguma esses itens citados aqui são mal documentados (nos manuais Oracle encontramos os detalhes TODOS necessários), mas vou tentar resumir e concentrar neste artigo. Nesse caso, vou fazer meus testes em um notebook Windows 10 aonde roda um RDBMS Oracle XE 18c, MAS os conceitos são universais até certo ponto, valem também na maior parte para UNIX, Linux, AIX, Solaris, etc….
OBS: Nem seria necessário explicitar mas por segurança é cabível o seguinte comentário: De forma alguma o mecanismo de carregar arquivos com o SQL*Loader (que será o objeto deste artigo) é a única opção. Entre várias outras, nós temos por exemplo a figura do FILE WATCHER, que permite que o RDBMS fique monitorando uma pasta/diretório e automaticamente carregue para o banco os dados constantes nos arquivos nessa pasta/diretório assim que os arquivos chegam na pasta/diretório…
Vamos começar por UNICODE, Charactersets, Encodings e questões de Globalização em geral. Acredito que é de conhecimento mais ou menos geral, mas nos dias da pré-história da informática, tanto memória quanto armazenamento eram terrivelmente caros, bem como a capacidade do hardware era incrivelmente limitada – embora hoje seja impensável sequer cogitar isso, na época qualquer miníma economia de um bit aqui e outro ali no final era significativa…. Esse fato, além da circunstância de que as pesquisas e desenvolvimentos iniciais da tecnologia foram feitos em países falantes do inglês, que demanda menos caracteres no alfabeto, levaram a ser desenvolvida uma forma de representação numérica de caracteres que ocupava apenas 7 bits dos 8 possíveis num byte, cobrindo apenas os caracteres básicos usados nas palavras novas do inglês MAS ainda assim ‘poupando’ o 8º byte. Vide esse link para mais detalhes…
Com o tempo a capacidade do hardware foi crescendo e os computadores passaram a ser usados mais intensamente em países não-anglófonos. A solução encontrada inicialmente para representar os caracteres extras, não presentes no inglês e portanto ausentes do ASCII original foi o CODE PAGE, ie : Quando o sistema operacional/hardware fosse notificado que estava sendo usado na região X os códigos numéricos acima de 127 (que significa o 8º bit) são usados para representar os caracteres de um alfabeto A, quando eles forem notificados que estão sendo usados numa região Y essas posições acima vão representar caracteres não-ASCII básicos dessa outra região….OBVIAMENTE isso vale para arquivos de texto, pra databases, sistemas de envio e leitura de e-mails, etc, etc…..
Um exemplo: Tenho um arquivo de texto criado no codepage 1252, que conforme esse link nos mostra, é o CODEPAGE para línguas europeias ocidentais, e esse arquivo foi formatado com a codificação ASCII estabelecida pelo comitê ANSI :
Vamos exibir a codificação interna do conteúdo desse arquivo – para isso usarei um comando nativo do PowerShell no Windows 10 :
PS C:\USERS\USER 2AM> FORMAT-HEX TESTE.TXT
Caminho: C:\USERS\USER 2AM\TESTE.TXT
00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
00000000 4C 69 6E 68 61 20 31 20 3A 20 61 65 69 6F 75 E1 Linha 1 : aeiouá
00000010 E9 ED F3 FA 0D 0A 4C 69 6E 68 61 20 32 20 3A 20 éíóú..Linha 2 :
00000020 41 45 49 4F 55 C1 C9 CD D3 DA 0D 0A 0D 0A AEIOUÁÉÍÓÚ....
PS C:\USERS\USER 2AM> Realmente, na primeira linha o primeiro caracter com código hexa 4C (decimal=76) é o caracter L, o segundo caracter codificado como hexa 69 (decimal=105) é o caracter i , são os caracteres básicos codificados no ASCII original, todos abaixo de 127 decimal, numéro que se representa com 7 bits….
O que interessa para o artigo são os caracteres especiais. Por exemplo, veja que o 16º carácter na primeira linha, codificado com E1 hexa (225 decimal) , o próximo carácter está codificado como E9 hexa (233 decimal) , assim por diante, inclusive com a quebra de linha sendo representada por DOIS caracteres tal como o Windows estabelece, okdoc… Obviamente, para que o prompt de comando DOS possa exibir os diferentes caracteres dos diferentes codepages que usaremos, ele DEVE estar usando uma fonte que os possua, usarei no meu exemplo Lucida Console:
Muito bem: Vejamos que SE a sessão estiver usando o CODEPAGE 1252 esses caracteres VÃO ser interpretados como á é í , etc, assim:
Porém, DEMONSTRANDO O CONCEITO, esse MESMO EXATO ARQUIVO, sem alterar UM BYTE SEQUER, se for usado/exibido por exemplo numa sessão com CODEPAGE ativo 1253 (que conforme este link indica, é o CODEPAGE da região da Grécia) , esses mesmos códigos vão ser interpretados como Completamente Outros Caracteres:
Ok ? Tal como FOI dito antes, o mecanismo de CODEPAGE serve para isso, o 8º bit é usado para interpretar diferentes caracteres… E isso vale também para os SGBDs, desde muito cedo eles já foram de uso globalizado, então já implementavam esse tipo de programação. No caso do SGBD Oracle, desde a versão 7 ele já apresenta uma evolução do conceito, mantendo como propriedade de qualquer database que ele gerencie o CHARACTERSET, que (para este uso básico que estou exemplificando) pode ser entendido como o conjunto dos caracteres ASCII ANSI básicos mais os caracteres de uma dada região definida para um CODEPAGE. Para fins de referência, Windows-1252, IEC-8859-1 e IEC-8859-15 demonstram o conjunto de caracteres e os respectivos códigos para alguns character sets de uso comum no database Oracle em versões prévias…
A situação continuou nesse pé até os primeiros anos da década de 90 do século XX , quando foi introduzido o UNICODE. Esse character set foi criado por um consórcio de empresas visando ser completamente universal e dar assim suporte às línguas/alfabetos que possuem mais de 255 caracteres (que é o máximo possível nos 8 bits do ASCII), como por exemplo as línguas europeias orientais e asiáticas, e sua principal característica é que cada carácter tem um máximo de representação variável, implementando se necessário até 4 bytes (32 bits) para representar um único carácter. Vide UNICODE , Unicode and character sets e Unicode History para histórico e referências do assunto…. Até hoje não é difícil encontrar empresas (principalmente de pequeno porte, que não costumam fazer negócios fora do país) aonde esse cenário está assim até os dias de hoje, OU mesmo (pior ainda) simplesmente não usa nenhum codepage, tendo todos seus sistemas de informação codificando seus dados em ASCII básico apenas….
Globalmente, porém, como o UNICODE representa uma solução tecnicamente factível e amplamente aceita, praticamente TODAS as Empresas produtoras de software globalizadas o adotaram, e a Oracle não foi exceção. Desde os primeiros anos do século XXI ela inclusive já recomendava fortemente que todos os databases criados/gerenciados com o SGBD Oracle sejam criado usando UNICODE como seu character set padrão….Neste artigo vamos demonstrar como proceder nesses casos, ie, o database Oracle em uso já está conforme recomendação da Oracle, usando character set UNICODE – a Oracle suporta praticamente todas as versões de UNICODE mas no banco em uso para este artigo será usado o characterset AL32UTF8, que é uma versão expandida do UNICODE UTF8 original….
Conceitos referentes à SQL*LOADER e carga de arquivos de texto dentro do database. Para que os dados externos possam ser utilizados num database qualquer, em princípio eles devem ser inseridos/gravados dentro do database – um database Oracle até possui a capacidade de fazer um SELECT, ie, de ler dados externos que estão gravados num arquivo fora do database através do uso da feature de EXTERNAL TABLES, mas essa opção não é usada em grandes volumes de dados, já que um database Oracle possui os mais diversos mecanismos de segurança, de replicação de dados, de CACHEs, etc, etc.
Sendo assim, é necessária alguma ferramenta capaz de ingerir grandes volumes de dados (normalmente em arquivos, via de regra é inviável a transferência por rede de grandes volumes) e inserir/gravar esses dados, a ferramenta nativa para isso, fornecida junto com o RDBMS Oracle e no software cliente Oracle (para utilização a partir de máquinas clientes) é o Oracle SQL*Loader. Para cargas de pequenos volumes a Oracle mesmo disponibiliza uma ferramenta de fácil utilização, o Oracle SQL DEVELOPER, com interface gráfica, mas para uso programado, num JOB periódico, em grandes volumes , o SQL*Loader é a opção comumente usada – existem outras, como um INSERT INTO tabela (SELECT … FROM externaltableapontandopara umarquivoexterno); mas aqui no artigo vamos discutir o Loader apenas.
A utilização básica é: Já havendo uma tabela já criada no database para onde os dados serão carregados e já existindo na máquina que executará o SQL*loader o arquivo de texto com os dados a serem carregados e inseridos/gravados na tabela, só é necessário que seja criado um arquivo de controle, ie, um arquivo texto menor com as especificações dos dados. Formato dos dados, caracteres de separação dos dados (ou posições de início/fim de cada dado, se for assim especificado, nome da tabela aonde inserir os dados e o nome do arquivo texto que contém os dados. Com isso tudo existindo, executa-se o SQL*loader indicando os parâmetros necessários.OBS : Não é obrigatório mas para maior clareza, e para evitar erros se houver múltiplos produtos Oracle na máquina em uso, eu vou setar manualmente as variáveis de ambiente ORACLE_HOME (no caso apontando para o MEU local de instalação do RDBMS Oracle, mudar se o HOME em uso não for esse) e também o PATH:
SID:XE::C:\Users\User 2am>SET ORACLE_HOME=d:\app\oracle\product\xe\18.0.0\dbhomexe
SID:XE::C:\Users\User 2am>SET PATH=%ORACLE_HOME%\BIN;%PATH%Tudo OK, como exemplo – eu já tenho uma tabela que receberá os dados :
SCOTT@xepdb1::CONTAINER=XEPDB1> desc EMPLOYEE
Nome Nulo? Tipo
---------------------- ------ -----------
ID NUMBER(38)
NAME VARCHAR2(10)
DEPT VARCHAR2(15)
SALARY NUMBER(10,2)
HIREDON DATE
SCOTT@xepdb1::CONTAINER=XEPDB1>E na máquina que executará o SQL*loader eu já possuo o software Oracle cliente completo instalado, o arquivo de controle:
SID:XE::C:\Users\User 2am>type EMPLOYEE.ctl
LOAD DATA
INFILE 'EMPLOYEE.txt'
APPEND INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ID INTEGER,
NAME CHAR(10),
DEPT CHAR(15),
SALARY "TO_NUMBER(:SALARY, '9999999.00')",
HIREDON DATE "DD/MM/YYYY HH24:MI:SS"
)
SID:XE::C:\Users\User 2am>E este é o arquivo-texto com os dados a serem carregados:
SID:XE::C:\Users\User 2am>type EMPLOYEE.txt
100,Thomas,Sales,5000.11,"01/10/2020 10:11:12"
200,Jason,Technology,5500.22,"01/10/2020 10:11:12"
300,Mayla,Technology,7000.33,"01/10/2020 10:11:12"
400,Nisha,Marketing,9500.44,"01/10/2020 10:11:12"
500,Randy,Technology,6000.55,"01/10/2020 10:11:12"
501,Ritu,Accounting,5400.66,"01/10/2020 10:11:12"
SID:XE::C:\Users\User 2am>NOTAR que eu descrevi EXATAMENTE o formato em que os dados estão no arquivo .CTL (ie, as colunas delimitadas por vírgula, opcionalmente estando dentro de aspas-duplas), indiquei o TIPO e o TAMANHO de cada coluna, e no caso como eu tinha caracteres não dígitos numéricos na coluna SALARY (os pontos decimais) usei a função de conversão TO_NUMBER , e para a coluna HIREDATE converto para DATE indicando uma máscara de data – há opções para indicar os mais diversos caracteres de separação e condições a serem cumpridas no arquivo de controle, vide manual Oracle Utilities para referência completa, é a versão 19c do manual.
Agora basta executar a tool : no caso vou indicar usuário e senha, indicar o arquivo de controle (que conforme mostrado já possui na linha com INFILE a indicação do nome do arquivo com dados) , o arquivo de LOG e um arquivo que manterá os registros com erros, se houver:
SID:XE::C:\Users\User 2am>sqlldr scott/tiger@xepdb1 control=employee.ctl log=employee.log bad=employee.bad
SQL*Loader: Release 18.0.0.0.0 - Production on Seg Jun 15 19:47:28 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Caminho usado: Convencional
Atingido o ponto de confirmação - contagem de registros lógicos 6
Tabela EMPLOYEE:
6 Linhas carregados com sucesso.
Verifique o arquivo log:
employee.log
para obter mais informações sobre a carga.O log gerado comprova que não houveram erros:
SID:XE::C:\Users\User 2am>type employee.log
SQL*Loader: Release 18.0.0.0.0 - Production on Seg Jun 15 19:47:28 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Arquivo de Controle: employee.ctl
Arquivo de Dados: EMPLOYEE.txt
Arquivo de Incorreções: employee.bad
Arquivo de Descarte: nenhum(a) foi especificado(a)
(Permite todos os descartes)
Número a ser carregado: ALL
Número a ser ignorado: 0
Erros permitidos: 50
Array de ligação: 250 linhas, máximo de 1048576 bytes
Continuação: nenhum(a) foi especificado(a)
Caminho usado: Convencional
Tabela EMPLOYEE, carregada a partir de cada registro lógico.
Opção de inserção em vigor para esta tabela: APPEND
Nome da Coluna Posição Tam Term Incl Tipo de Dados
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 4 INTEGER
NAME NEXT 10 , O(") CHARACTER
DEPT NEXT 15 , O(") CHARACTER
SALARY NEXT * , O(") CHARACTER
String SQL para coluna : "TO_NUMBER(:SALARY, '9999999.00')"
HIREDON NEXT * , O(") DATE DD/MM/YYYY HH24:MI:SS
Tabela EMPLOYEE:
6 Linhas carregados com sucesso.
0 Linhas não carregado devido a erros de dados.
0 Linhas não carregado porque todas as cláusulas WHEN falharam.
0 Linhas não carregado porque todos os campos eram nulos.
Espaço alocado para o array de ligação: 137500 bytes(250 linhas)
Bytes do buffer de leitura: 1048576
Total de registros lógicos ignorados: 0
Total de registros lógicos lidos: 6
Total de registros lógicos rejeitados: 0
Total de registros lógicos descartados: 0
A execução começou em Seg Jun 15 19:47:28 2020
A execução foi finalizada em Seg Jun 15 19:47:30 2020
O tempo decorrido foi: 00:00:02.20 O tempo de CPU foi: 00:00:00.09E o arquivo .BAD vazio também demonstra que nenhum registro foi rejeitado:
SID:XE::C:\Users\User 2am>type employee.badO sistema não pode encontrar o arquivo especificado.
Criação de uma tabela que conterá caracteres ‘especiais’ (acima de 127) em colunas string e CLOB, e posterior carga de um arquivo de dados formatado com codepage 1252 formato ANSI. Ao utilizarmos caracteres especiais/acima do ASCII 127, a primeira questão é checarmos como está a propriedade de CHARACTERSET do database – como dito, no RDBMS ORACLE isso é especificado a nível de database, e se uma sessão qualquer (via combinação de CODEPAGE + variável NLS_LANG) indicar um outro characterset, haverá uma CONVERSÃO para o character set do database, que sempre será o usado para o armazenamento. No meu database a propriedade está setada como UNICODE AL32UTF8, a versão mais recente do UNICODE :
SID:XE::C:\Users\User 2am>sqlplus system/oracle@xepdb1
SQL*Plus: Release 18.0.0.0.0 - Production Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Conectado a:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SYSTEM@xepdb1::CONTAINER=XEPDB1> select parameter, value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------ -------------------------------
NLS_CHARACTERSET AL32UTF8
1 linha selecionada.
SYSTEM@xepdb1::CONTAINER=XEPDB1> exit
Desconectado de Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SID:XE::C:\Users\User 2am>Segundo passo, confirmar que o Codepage está setado para um valor compatível com o character set OU que (é este o caso aqui) o character set do banco seja um SUPERSET (ie, contenha todos os caracteres necessários além de outros) – no meu caso UNICODE engloba os caracteres do alfabeto ocidental, então a conversão para UNICODE será automática com os settings:
SID:XE::C:\Users\User 2am>chcp 1252
Página de código ativa: 1252Terceiro ponto, quando usamos um character set que pode ocupar mais de um byte para representar um carácter (é o caso do UNICODE), na hora da criação da tabela TEMOS que especificar que queremos limitar as strings por quantidade de caracteres e não de bytes , assim:
SCOTT@xepdb1::CONTAINER=XEPDB1> CREATE TABLE LIVRO
(
LIV_TITULO VARCHAR2(50 CHAR),
LIV_DT_PUB DATE,
LIV_TEXTO CLOB
)
TABLESPACE USERS
LOB ("LIV_TEXTO") STORE AS SECUREFILE (
TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK 8192
) ;
Tabela criada.
SCOTT@xepdb1::CONTAINER=XEPDB1>No exemplo acima indiquei que a coluna LIV_TITULO vai armazenar até 50 CARACTERES, independente de quantos bytes o Characterset consumir para representar isso.
Outro ponto importante é que além de termos uma coluna string escalar (datatype VARCHAR2) teremos TAMBÉM uma coluna LOB(Large Object), no caso com o datatype CLOB, que é uma string larga. Esse datatype apresenta um desafio extra que é o fato de que o datatype em si aceita até 4 GB de dados , mas tanto a linguagem SQL quanto a linguagem PL/SQL não são programadas para permitirem uma string desse tamanho – sendo assim, nem nós, usuários, nem o SQL*Loader nem nenhum utilitário Oracle pode enviar para o banco um comando INSERT INTO tabela VALUES(‘… string com até 4 GB…’) … O procedimento para se trabalhar com strings largas num CLOB portanto é OU quebrar a string em múltiplos ‘pedaços’ menores de tamanho que a linguagem SQL ou PL/SQL (cfrme o caso) aceite, OU então ter esses dados num arquivo-texto, que então pode ser lido e carregado para o banco com a funcionalidade de BFILE – usarei essa opção no arquivo de controle abaixo que usarei para fazer a carga nessa tabela LIVRO recém-criada:
SID:XE::C:\Users\User 2am>type LIVRO_DATA_TABLE.ctl
LOAD DATA
INFILE 'LIVRO_DATA_TABLE.txt'
APPEND INTO TABLE LIVRO
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
LIV_TITULO CHAR (50),
LIV_DT_PUB DATE "DD/MM/YYYY HH24:MI:SS" ,
L_0 FILLER char,
LIV_TEXTO LOBFILE(L_0) TERMINATED BY EOF
)
SID:XE::C:\Users\User 2am>No control file acima, esse indicador L_0 é apenas um FILLER, ie, uma indicação que a coluna não contém dados, os dados a serem carregados/lidos para a tabela estarão num ARQUIVO à parte, um BFILE . na linha do INFILE o arquivo de dados indicado é este aqui:
SID:XE::C:\Users\User 2am>type livro_data_table.txt
"teste"|15/06/2020 20:49:23|LIVRO_DATA_TABLE_CLOB001.clob
SID:XE::C:\Users\User 2am>Esse arquivo LIVRO_DATA_TABLE_CLOB001.clob (que contém os dados a serem carregados no CLOB via leitura de arquivo externo com BFILE) está formatado em ASCII ANSI com codepage 1252, ao abrir ele num editor que mostra a codificação comprovamos isso:
O objetivo desta demonstração é mostrar que o armazenamento de strings no banco OBEDECE ao character set do banco, que neste primeiro exemplo é o UNICODE AL32UTF8 – o que vai acontecer é que o arquivo BFILE formatado com ANSI 1252 vai ser convertido automaticamente para UNICODE (já que UNICODE é um superste, contém TODOS os caracteres do codepage 1252), enquanto o arquivo UNICODE vai continuar como está. Assim:
SID:XE::C:\Users\User 2am>sqlldr scott/tiger@xepdb1 control=LIVRO_DATA_TABLE.ctl log=LIVRO_DATA_TABLE.log bad=LIVRO_DATA_TABLE.bad
SQL*Loader: Release 18.0.0.0.0 - Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Caminho usado: Convencional
Atingido o ponto de confirmação - contagem de registros lógicos 1
Tabela LIVRO:
1 Linha carregados com sucesso.
Verifique o arquivo log:
LIVRO_DATA_TABLE.log
para obter mais informações sobre a carga.
SID:XE::C:\Users\User 2am>type LIVRO_DATA_TABLE.log
SQL*Loader: Release 18.0.0.0.0 - Production on Qua Jun 17 20:22:13 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Arquivo de Controle: LIVRO_DATA_TABLE.ctl
Arquivo de Dados: LIVRO_DATA_TABLE.txt
Arquivo de Incorreções: LIVRO_+DATA_TABLE.bad
Arquivo de Descarte: nenhum(a) foi especificado(a)
(Permite todos os descartes)
Número a ser carregado: ALL
Número a ser ignorado: 0
Erros permitidos: 50
Array de ligação: 250 linhas, máximo de 1048576 bytes
Continuação: nenhum(a) foi especificado(a)
Caminho usado: Convencional
Tabela LIVRO, carregada a partir de cada registro lógico.
Opção de inserção em vigor para esta tabela: APPEND
Opção TRAILING NULLCOLS em efeito
Nome da Coluna Posição Tam Term Incl Tipo de Dados
------------------------------ ---------- ----- ---- ---- ---------------------
LIV_TITULO FIRST 50 | O(") CHARACTER
LIV_DT_PUB NEXT * | O(") DATE DD/MM/YYYY HH24:MI:SS
L_0 NEXT * | O(") CHARACTER
(CAMPO DE PREENCHEDOR)
LIV_TEXTO DERIVED * EOF CHARACTER
LOBFILE dinâmico. Nome do arquivo no campo L_0
Tabela LIVRO:
1 Linha carregados com sucesso.
0 Linhas não carregado devido a erros de dados.
0 Linhas não carregado porque todas as cláusulas WHEN falharam.
0 Linhas não carregado porque todos os campos eram nulos.
Espaço alocado para o array de ligação: 142000 bytes(250 linhas)
Bytes do buffer de leitura: 1048576
Total de registros lógicos ignorados: 0
Total de registros lógicos lidos: 1
Total de registros lógicos rejeitados: 0
Total de registros lógicos descartados: 0
A execução começou em Qua Jun 17 20:22:13 2020
A execução foi finalizada em Qua Jun 17 20:22:14 2020
O tempo decorrido foi: 00:00:00.43
O tempo de CPU foi: 00:00:00.09
SID:XE::C:\Users\User 2am>Apesar de que o CHARACTERSET do banco (e portanto o CHARACTERSET usado tanto pelo BLOB quanto pelas colunas string) set AL32UTF8, como o arquivo de entrada de dados estava formatado em ANSI e usando apenas códigos de 8 bits, esses dados PODEM ser consultados e exibidos por um SQL*Plus rodando num prompt de comando COM codepage setado :
SCOTT@xepdb1::CONTAINER=XEPDB1> select liv_titulo, liv_dt_pub, dbms_lob.substr(LIV_TEXTO, 900, 1) from livro;
LIV_TITULO LIV_DT_PUB
-------------------------------------------------- -------------------
DBMS_LOB.SUBSTR(LIV_TEXTO,900,1)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
teste 15/06/2020 20:49:23
Início do texto cheio de Lorem data#1
=>Duas linhas abaixo com caracteres acentuados
"À NOITE,VOVÔ MÜLLER VÊ O ÍMÃ CAIR NO PÉ E VOVÓ SÓ PÕE AÇÚCAR NO CHÁ SEM ÂNIMO"
"à noite,vovô müller vê o ímã cair no pé e vovó só põe açúcar no chá sem ânimo";
=>Caracteres extras que Existem tanto em UNICODE quanto em ANSI na região Europa-Ocidental 1252 :
• ‘single’ and “double” quotes
• Curly apostrophes: “We’ve been here”
• Latin-1 apostrophe and accents: '´`
• ‚deutsche‘ „Anführungszeichen“
• símbolos matemáticos, marca registrada, copyright, micra, função : †, ‡, ÷, ‰, •, 3–4, —, -5/+5, ™, ©, …, µ, ƒ,
• the euro symbol and pound : € e £
=> Continua .....
1 linha selecionada.
SCOTT@xepdb1::CONTAINER=XEPDB1>Veja a codificação interna : o DUMP reaforma que o CHARACTERSET default foi usado e que é AL32UTF8, mas veja que os Códigos hexa usados são os do CODEPAGE 1252, tal como determinado:
SCOTT@xepdb1::CONTAINER=XEPDB1> select liv_titulo, liv_dt_pub, DUMP(dbms_lob.substr(LIV_TEXTO, 900, 1),1016) from livro;
LIV_TITULO LIV_DT_PUB
-------------------------------------------------- -------------------
DUMP(DBMS_LOB.SUBSTR(LIV_TEXTO,900,1),1016)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
teste 15/06/2020 20:49:23
Typ=1 Len=993 CharacterSet=AL32UTF8: 49,6e,c3,ad,63,69,6f,20,64,6f,20,74,65,78,74,6f,20,63,68,65,69,6f,20,64,65,20,4c,6f,72,65,6d,20,64,61,74,61,23,31,d,a,3d,3e,44,75,61,73,20,6c,69,6e,68,61,73,20,61,62,61,69,78,6f,20,63,6f,6d,20,63,61,72,61,63,74,65,72,65,73,20,61,63,65,6e,74,75,61,64,6f,73,d,a,22,c3,80,20,4e,4f,49,54,45,2c,56,4f,56,c3,94,20,4d,c3,9c,4c,4c,45,52,20,56,c3,8a,20,4f,20,c3,8d,4d,c3,83,20,43,41,49,52,20,4e,4f,20,50,c3,89,20,45,20,56,4f,56,c3,93,20,53,c3,93,20,50,c3,95,45,20,41,c3,87,c3,9a,43,41,52,20,4e,4f,20,43,48,c3,81,20,53,45,4d,20,c3,82,4e,49,4d,4f,22,d,a,22,c3,a0,20,6e,6f,69,74,65,2c,76,6f,76,c3,b4,20,6d,c3,bc,6c,6c,65,72,20,76,c3,aa,20,6f,20,c3,ad,6d,c3,a3,20,63,61,69,72,20,6e,6f,20,70,c3,a9,20,65,20,76,6f,76,c3,b3,20,73,c3,b3,20,70,c3,b5,65,20,61,c3,a7,c3,ba,63,61,72,20,6e,6f,20,63,68,c3,a1,20,73,65,6d,20,c3,a2,6e,69,6d,6f,22,3b,d,a,3d,3e,43,61,72,61,63,74,65,72,65,73,20,65,78,74,72,61,73,20,71,75,65,20,45,78,69,73,74,65,6d,20,74,61,6e,74,6f,20,65,6d,20,55,4e,49,43
,4f,44,45,20,71,75,61,6e,74,6f,20,65,6d,20,41,4e,53,49,20,6e,61,20,72,65,67,69,c3,a3,6f,20,45,75,72,6f,70,61,2d,4f,63,69,64,65,6e,74,61,6c,20,31,32,35,32,20,3a,20,d,a,d,a,e2,80,a2,20,e2,80,98,73,69,6e,67,6c,65,e2,80,99,20,61,6e,64,20,e2,80,9c,64,6f,75,62,6c,65,e2,80,9d,20,71,75,6f,74,65,73,20,20,20,20,20,20,20,20,20,d,a,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d,a,e2,80,a2,20,43,75,72,6c,79,20,61,70,6f,73,74,72,6f,70,68,65,73,3a,20,e2,80,9c,57,65,e2,80,99,76,65,20,62,65,65,6e,20,68,65,72,65,e2,80,9d,20,d,a,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d,a,e2,80,a2,20,4c,61,74,69,6e,2d,31,20,61,70,6f,73,74,72,6f,70,68,65,20,61,6e,64,20,61,63,63,65,6e,74,73,3a,20,27,c2,b4,60,20,20,d,a,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d,a,e2,80,a2,20,e2,80,9a,64,65,75,74,73,63,6
8,65,e2,80,98,20,e2,80,9e,41,6e,66,c3,bc,68,72,75,6e,67,73,7a,65,69,63,68,65,6e,e2,80,9c,20,20,20,20,20,20,20,d,a,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d,a,e2,80,a2,20,73,c3,ad,6d,62,6f,6c,6f,73,20,6d,61,74,65,6d,c3,a1,74,69,63,6f,73,2c,20,6d,61,72,63,61,20,72,65,67,69,73,74,72,61,64,61,2c,20,63,6f,70,79,72,69,67,68,74,2c,20,6d,69,63,72,61,2c,20,66,75,6e,c3,a7,c3,a3,6f,20,3a,20,e2,80,a0,2c,20,e2,80,a1,2c,20,c3,b7,2c,20,e2,80,b0,2c,20,e2,80,a2,2c,20,33,e2,80,93,34,2c,20,e2,80,94,2c,20,2d,35,2f,2b,35,2c,20,e2,84,a2,2c,20,c2,a9,2c,20,e2,80,a6,2c,20,c2,b5,2c,20,c6,92,2c,20,d,a,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d,a,e2,80,a2,20,74,68,65,20,65,75,72,6f,20,73,79,6d,62,6f,6c,20,61,6e,64,20,70,6f,75,6e,64,20,3a,20,20,e2,82,ac,20,65,20,c2,a3,20,20,20,20,20,20,20,20,20,20,d,a,d,a,3d,3e,20,43,6f,6e,74,69,6e,75,61,20,6f
1 linha selecionada.
SCOTT@xepdb1::CONTAINER=XEPDB1>Agora, para finalizar a demonstração, eu tenho um segundo arquivo que CONTÉM caracteres não-europeus ocidentais, que INEXISTEM no codepage 1252. Para poder ser carregado corretamente, esse arquivo de dados TEM que estar formatado como UNICODE, carregando-o no Notepad++ eu vejo isso:
Agora vou utilizar esse segundo arquivo, indicando-o no arquivo de carga:
SID:XE::C:\Users\User 2am>type LIVRO_DATA_TABLE.txt
"outro"|16/06/2020 21:49:23|LIVRO_DATA_TABLE_CLOB002.clob
SID:XE::C:\Users\User 2am> E ** IMPORTANTE ** : eu TENHO que avisar o SQL*Loader que esse arquivo contém caracteres INEXISTENTES no characterset compatível com o CODEPAGE em uso, portanto NÂO deve ser tentada nenhum tipo de conversão E deve ser mantido o characterset AL32UTF8 :
SID:XE::C:\Users\User 2am>type LIVRO_DATA_TABLE.ctl
LOAD DATA
CHARACTERSET UTF8
INFILE 'LIVRO_DATA_TABLE.txt'
APPEND INTO TABLE LIVRO
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
LIV_TITULO CHAR (50),
LIV_DT_PUB DATE "DD/MM/YYYY HH24:MI:SS" ,
L_0 FILLER char,
LIV_TEXTO LOBFILE(L_0) TERMINATED BY EOF
)
SID:XE::C:\Users\User 2am>Agora sim faço a carga:
SID:XE::C:\Users\User 2am>sqlldr scott/tiger@xepdb1 control=LIVRO_DATA_TABLE.ctl log=LIVRO_DATA_TABLE.log bad=LIVRO_DATA_TABLE.bad
SQL*Loader: Release 18.0.0.0.0 - Production on Qua Jun 17 20:40:48 2020
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Caminho usado: Convencional
Atingido o ponto de confirmação - contagem de registros lógicos 1
Tabela LIVRO:
1 Linha carregados com sucesso.
Verifique o arquivo log:
LIVRO_DATA_TABLE.log
para obter mais informações sobre a carga.
SID:XE::C:\Users\User 2am>type LIVRO_DATA_TABLE.log
SQL*Loader: Release 18.0.0.0.0 - Production
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Arquivo de Controle: LIVRO_DATA_TABLE.ctl
Conjunto de Caracteres UTF8 especificada para todas as entradas.
Arquivo de Dados: LIVRO_DATA_TABLE.txt
Arquivo de Incorreções: LIVRO_+DATA_TABLE.bad
Arquivo de Descarte: nenhum(a) foi especificado(a)
(Permite todos os descartes)
Número a ser carregado: ALL
Número a ser ignorado: 0
Erros permitidos: 50
Array de ligação: 250 linhas, máximo de 1048576 bytes
Continuação: nenhum(a) foi especificado(a)
Caminho usado: Convencional
Tabela LIVRO, carregada a partir de cada registro lógico.
Opção de inserção em vigor para esta tabela: APPEND
Opção TRAILING NULLCOLS em efeito
Nome da Coluna Posição Tam Term Incl Tipo de Dados
------------------------------ ---------- ----- ---- ---- ---------------------
LIV_TITULO FIRST 50 | O(") CHARACTER
LIV_DT_PUB NEXT * | O(") DATE DD/MM/YYYY HH24:MI:SS
L_0 NEXT * | O(") CHARACTER
(CAMPO DE PREENCHEDOR)
LIV_TEXTO DERIVED * EOF CHARACTER
LOBFILE dinâmico. Nome do arquivo no campo L_0
Conjunto de Caracteres UTF8 especificada para todas as entradas.
Tabela LIVRO:
1 Linha carregados com sucesso.
0 Linhas não carregado devido a erros de dados.
0 Linhas não carregado porque todas as cláusulas WHEN falharam.
0 Linhas não carregado porque todos os campos eram nulos.
Espaço alocado para o array de ligação: 142000 bytes(250 linhas)
Bytes do buffer de leitura: 1048576
Total de registros lógicos ignorados: 0
Total de registros lógicos lidos: 1
Total de registros lógicos rejeitados: 0
Total de registros lógicos descartados: 0
A execução começou em Qua Jun 17 20:40:48 2020
A execução foi finalizada em Qua Jun 17 20:40:48 2020
O tempo decorrido foi: 00:00:00.30
O tempo de CPU foi: 00:00:00.06
SID:XE::C:\Users\User 2am>Aqui chegamos numa LIMITAÇÃO do prompt de comando do Windows. Ele não É completamente compatível com UNICODE… Essa limitação é um pouco aliviada usando-se codepages mais recentes (como 65001) mas ainda assim, não é perfeito – assim sendo, para podermos exibir esses dados com caracteres UNICODE aí necessitamos de uma GUI. Usarei aqui o Oracle SQL DEVELOPER, da própria Oracle:
Exibição dos dados do 1º registro codificado em ANSI 1252:
E abaixo a exibição do 2º registro, efetivamente codificado em UNICODE:
Abraços
