Insert de varios registros em varias colunas ORA-01438

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Rafael_Santos
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 72
Registrado em: Sex, 18 Nov 2011 4:51 pm

Olá senhores(as),
estou realizando um insert em uma tabela no Data Warehouse que possui muitas colunas.
Quando realizo o insert confronto com o erro ORA-01438.
Estou utilizando o Oracle 9i.

Gostaria de saber se tem algo que possa me mostrar o erro especificando o nome da coluna que não possui espaço.
Encontrei este post(segue o link a baixo) que é exatamente o que quero, mas é para Oracle 10+.

http://www.orafaq.com/node/76


Agradeço a atenção
carloscsilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 22
Registrado em: Qui, 26 Jul 2012 8:55 am
" Pense diferente! - "

Ola,
Não conheço, mas você pode fazer um describe na tabela, para ver se há diferenças de campos, e são valores compatíveis. E depois um select dos valores que você quer passar para ver se não esta estourando os campos aonde esta tentando alocá los.
Rafael_Santos
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 72
Registrado em: Sex, 18 Nov 2011 4:51 pm

Estou trabalhando com ETL, onde tive que transformar dados de t-sql pra oracle sql.
Os campos da tabela origem estão em conformidade com os campos do oracle, mas deve ter algum dado divino que está fora do tamanho.

O problema do select sugerido e que existem algo perto de 60.000 registros não teria como saber qual esta estourando ou teria?

Ideias!?
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Rafael,

Uma "solução lusitana" para você tentar descobrir onde ocorre o problema:

PASSO 1:
- Crie uma tabela com a mesma estrutura da tabela onde ocorre o erro (ex: TAB_COPIA_01);
- Aumente o tamanho de todos os campos que a compoe;
- Faça o ETL nesta tabela;
- Depois, faça uma querie com MAX em todas as colunas existentes nesta tabela.
- No caso de campos STRING (CHAR/VARCHAR2) você teria que verificar o que tem a maior extensão;
- Ao final desta atividade, você deve ter uma unica linha com os campos mais extensos/maiores;

PASSO 2:
- Crie uma nova tabela com a mesma estrutura da tabela onde ocorre o erro. Todos os campos devem ser NULL (ex: TAB_COPIA_02);
- Verifique o tamanho de cada um deles e veja se bate com o tamanho da tabela OFICIAL.
- Faça inserts parciais na tabela para testar as colunas que você tem dúvida:
exemplo:

Selecionar tudo

             INSERT INTO TAB_COPIA (COLUNA_1) VALUES (10); 
             INSERT INTO TAB_COPIA (COLUNA_40) VALUES ('ABACAXI'); 
- Se alguma campo for estiver pequeno, você vai detectar o erro.

Uma recomendação: você mencionou T-SQL, o que indica que o banco de ORIGEM é um SYBASE ou um SQLSERVER. Existem diferenças importantes entre os tipos do ORACLE e estes dois bancos. Por exemplo, acho que os campos DATETIME padrão do SYBASE incorporam milisegundos.

Veja também os campos numéricos com casas decimais. Desconfio que a precisão de um campo NUMBER(5,2) no SYBASE seja diferente de um NUMBER(5,2) do ORACLE (posso estar enganado).

Abraços,

Sergio Coutinho
Rafael_Santos
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 72
Registrado em: Sex, 18 Nov 2011 4:51 pm

Obrigado Coutinho pela "solução lusitana".
Mas não acredito que não possa ter uma situação mais viável para o problema.

Estou começando a achar que o problema deve ser na ETL uma vez que os campos se correspondem.
Acho dificil que um campo ex.: Decimal(15,4) não seja suportado por um Number(15,4).

Alguém tem mais ideias!?

PS: Estou lendo algo sobre bulk insert para ver se é possível contornar este problema.
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Rafael

Por acaso eu dispunha de uma base 9i, e fiz alguns testes.

Consultando os foruns, notei que no 9i o erro ORA-01438 ocorre exclusivamente com campos numéricos. Segue um exemplo:

Selecionar tudo

SQL> CREATE TABLE LIXO (COLUNA CHAR(1));
Tabela criada.

SQL> INSERT INTO LIXO VALUES ('AA');
INSERT INTO LIXO VALUES ('AA')
                         *
ERRO na linha 1:
ORA-01401: inserted value too large for column

SQL> DROP TABLE LIXO;
Tabela eliminada.

SQL> CREATE TABLE LIXO (COLUNA NUMBER(1));
Tabela criada.

SQL> INSERT INTO LIXO VALUES (10);
INSERT INTO LIXO VALUES (10)
                         *
ERRO na linha 1:
ORA-01438: value larger than specified precision allows for this column


Repeti estes testes com os mesmos resultados para VARCHAR2 e NUMBER(i,d). Só campos numéricos retornam este código de erro.

Acho que você conseguiria identificar rapidamente a coluna executando o comando de INSERT no SQL*PLUS.

Informando a coluna ou não, ele aponta com um asterisco (*) justamente o ponto onde o comando deu pau:

Selecionar tudo

SQL> CREATE TABLE LIXO (COLUNA_A NUMBER(1), COLUNA_B NUMBER(2));
Tabela criada.

SQL> INSERT INTO LIXO (COLUNA_A,COLUNA_B) VALUES (1,100);
INSERT INTO LIXO (COLUNA_A,COLUNA_B) VALUES (1,100)
                                               *
ERRO na linha 1:
ORA-01438: value larger than specified precision allows for this column
SQL> INSERT INTO LIXO  VALUES (1,100);
INSERT INTO LIXO  VALUES (1,100)
                                             *
ERRO na linha 1:
ORA-01438: value larger than specified precision allows for this column
O que você então poderia fazer neste caso? Não sei se o seu ETL executa comandos de INSERT na base de dados ORACLE.

Se for este o caso, e você não sabe exatamente qual INSERT deu pau, você pode criar uma TRIGGER que capture erros de banco de dados.

Siga os passos abaixo para detectar este erro:

Selecionar tudo

-- Criando uma tabela para capturar erros
CREATE TABLE TB_ERRO
(DT_ERRO DATE,
 CM_SQL CLOB);
 
-- Criando uma trigger para capturar erros em um determinado schema
-- Substitua SYSTEM pelo schema onde a tabela a ser carregada se encontra
CREATE OR REPLACE TRIGGER TGR_CPTA_ERRO
AFTER SERVERERROR  ON SYSTEM.SCHEMA
-----------------------------------------------------
--- TRIGGER   : TGR_CPTA_ERRO
--- DESCRICAO : Esta trigger captura a maioria dos
--              erros "ORA" (nem todos) que evetualmente
--              ocorram no banco de dados
-----------------------------------------------------
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   SQL_TEXT ORA_NAME_LIST_T;
   V_STMT CLOB;
   N NUMBER;
BEGIN
  --
  N := ORA_SQL_TXT(SQL_TEXT);
  --
  IF N >= 1 THEN
     FOR I IN 1..N LOOP
         V_STMT := V_STMT || SQL_TEXT(I);
     END LOOP;
  END IF;
  --
  FOR N IN 1..ORA_SERVER_ERROR_DEPTH LOOP
      --
      INSERT 
        INTO TB_ERRO
            (DT_ERRO,
             CM_SQL)
        VALUES
            (SYSDATE,
             V_STMT);
      COMMIT;
  END LOOP;
END TGR_CPTA_ERRO;
/
As operações de ETL devem ser executadas com o usuário dono do schema da tabela. Você não precisa executar ela até o fim. Basta que ocorra o primeiro errp ORA-01438.

Feito isso, você pode consultar a TB_ERRO para identificar o comando capturado no campo CLOB.

Copie o comando SQL executado, abra uma sessão SQL*PLUS e faça um "paste" do comando. Por mais colunas que tenha a tabela, ela vai indicar com um asterisco o ponto onde o problema ocorre. Se forem geradas muitas linhas de comando, tente alterar a sessão com um SET LINESIZE 5000 e SET TRIMSPOOL ON, para ver se o asterisco pode ser visualizado exatamente na posição que ocorre o erro.

Pode ser que exista algum problema com os seus campos núméricos. Não exatamente os NUMBER(I.D). Pode ser que uma coluna SQLSERVER do tipo INT não esteja corretamente dimensionada no ORACLE (ex: NUMBER(38)).

Eu localizei um documento da Microsoft que faz uma comparação entre os tipos do SQLSERVER x ORACLE. O link se situa em: http://msdn.microsoft.com/en-us/library/ms151817.aspx

Espero que consiga resolver os seus problemas.

Boa sorte,

Sergio Coutinho
Rafael_Santos
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 72
Registrado em: Sex, 18 Nov 2011 4:51 pm

Coutinho, irei testar a sua ideia e mais tarde irei postar aqui o resultado, creio que este post poderá ajudar mais pessoas que utilizam ETL, BI entre outros.

Quanto ao link passado, muito obrigado poupará no futuro a busca por tipo possíveis para conversão de dados.

Obrigado Coutinho, até breve.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes