Menu de navegação

[José Laurindo ChiappaModerador]

SCOTT@xepdb1::CONTAINER=XEPDB1> SELECT
2 A.CUS_FAM as FAM,
3 A.CUS_DTA as DMO_INI,
4 NVL( LEAD(A.CUS_DTA,1)
5 OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA)
6 ,
7 TRUNC(SYSDATE)
8 ) as DMO_FIN,
9 A.CUS_VLT_RTO as VLT_RTO,
10 A.CUS_VLU_RTO as VLU_RTO,
11 A.CUS_VLT_RMO as VLT_RMO,
12 A.CUS_VLU_RMO as VLU_RMO
13 FROM SGI5_TAB_CUS_REP_RAT A
14 WHERE A.CUS_DTA >= to_date(’01/01/1980′, ‘dd/mm/yyyy’) — eu não tenho a variável V_DTA, uso valor fixo
15 ;

   FAM DMO_INI             DMO_FIN                VLT_RTO    VLU_RTO    VLT_RMO    VLU_RMO

---

  3010 01/01/2000 00:00:00 01/01/2001 00:00:00      97890    ,382943      26311    ,102928
  3010 01/01/2001 00:00:00 01/01/2002 00:00:00     104293    ,137435      28032     ,03694
  3010 01/01/2002 00:00:00 01/01/2003 00:00:00     111046    ,173385      29847    ,046602
  .... veja abaixo que tenho SIM alguns casos de null, o NVL transformou no TRUCN de hoje, dia 29....
  3010 01/11/2022 00:00:00 29/07/2023 00:00:00     593364      ,6641     240038      ,2687
  3012 01/11/2022 00:00:00 29/07/2023 00:00:00      20699     2,3438       6230      ,7054
  3013 01/11/2022 00:00:00 29/07/2023 00:00:00       9793     2,3009       2662      ,6256
  ... muitas outras linha ....
  3070 01/07/2003 00:00:00 01/10/2003 00:00:00       6312    ,290581       1914    ,088113
  3070 01/10/2003 00:00:00 29/07/2023 00:00:00       6762    ,316989       2050      ,0961

381 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

Funcionou que é uma maravilha, ok ?

[José Laurindo ChiappaModerador]

=> agora sim, o MERGE :

SCOTT@xepdb1::CONTAINER=XEPDB1> MERGE INTO SGI5_TAB_CUS_RET_RAT X
2 USING(SELECT
3 A.CUS_FAM as FAM,
4 A.CUS_DTA as DMO_INI,
5 NVL( LEAD(A.CUS_DTA,1)
6 OVER (PARTITION BY A.CUS_FAM ORDER BY A.CUS_FAM, A.CUS_DTA)
7 ,
8 TRUNC(SYSDATE)
9 ) as DMO_FIN,
10 A.CUS_VLT_RTO as VLT_RTO,
11 A.CUS_VLU_RTO as VLU_RTO,
12 A.CUS_VLT_RMO as VLT_RMO,
13 A.CUS_VLU_RMO as VLU_RMO
14 FROM SGI5_TAB_CUS_REP_RAT A
15 WHERE A.CUS_DTA >= to_date(’01/01/1980′, ‘dd/mm/yyyy’) — eu não tenho a variável V_DTA, uso valor fixo
16 ) Y
17 ON
18 ( X.CUS_FAM = Y.FAM
19 AND X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN
20 )
21 WHEN MATCHED THEN
22 UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
23 X.CUS_VLU_RTO = Y.VLU_RTO,
24 X.CUS_VLT_RMO = Y.VLT_RMO,
25 X.CUS_VLU_RMO = Y.VLU_RMO;

48 linhas mescladas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

===>> PERFEITO, Suave como o éter, Absolutamente Erro nenhum, tá ok ???? Isso COMPROVA que OU é um erro no seu código real , não presente nessa versão simplificada, OU é pau/limitação da tua tool de front-end ao processar o MERGE, OU é essa variável V_DTA que eu já apontei acima, OU vc tem mais código “oculto” aí que vc não nos motrou E que vc TEM QUE DEBUGAR, tá certo ???
E só comentando, infelizmente o WordPress vai bagunçar TUDO, mas na hora de analisar/testar, eu coloquei SIM meu código SQL (tanto a query isolada QUANTO o MERGE) Alinhado nas colunas certas, com o fecha parêntesis na mesma posição do abre parêntesis, dei uns espaços para que cada coluna e cada ALIAS de coluna começassem na mesma coluna – isso AJUDA DEMAIS a se compreender qual função tá recebendo quais argumentos, EM ESPECIAL no caso desse NVL que englobla parêntesis do LEAD e do OVER , é MUITO FÁCIL se perder se não fizermos isso…

Abraços,

Chiappa

[ESSanchesParticipante]

Bom dia Chiappa! Muito obrigado pela atenção!.

Vamos lá, parte pro parte:

>>>Então: PRIMEIRO os dois SELECTs absolutamente não são os mesmos, no SQL que roda ok isolado vc escreveu :

SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
NVL(LEAD(A.CUS_DTA,1) OVER ….

ENQUANTO QUE no SQL do MERGE , vc escreveu :

SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER…..<<<<

Fiz dessa maneira pq assim, o código roda!!! Só por isso. Sei que não tem o menor sentido o que fiz. Eu converti o retorno do LEAD em string, tratei o nulo e reconverti em data. Sei que é burro, que é desnecessário, mas foi dessa maneira que o código funcionou!!!

>>>*** PERCEBA ** que esses identificador V_DTA ao que parece (julgando pelos CREATEs) ** não existe ** dentro de nenhuma das tabelas, ENTÃO o SQL vai considerar que isso é uma VARIÁVEL… Como essa variável NÂO FOI CRIADA antes de rodar o SQL isoladamente ao que vejo, COMO É que vc está conseguindo rodar SEM receber um erro :

Erro de SQL: ORA-00904: “V_DATA”: identificador inválido<<<<

Esse V_DATA esta declarado na procedure que passei no pastebin, e definida como data.

>>>insert into ….
insert into SGI5_TAB_CUS_REP_R”

OU SEJA, pelo jeito vc NÂO SEGUIU a minha recomendação de passar só uns poucos MÍNIMOS INSERTs e quis enfiar TODOS OS DADOS que vc tinha, aí vc ULTRAPASSOU o limite do site… Mas sem prob, vou rodar esses INSERTs que couberam e vamos ver….<<<<

Desculpe, nunca tinha usado o site, pelo que vc disse na indicação, achei que não havia limite de tamanho.

>>>OBSERVAÇÃO : vc não respondeu MAS ESPERO que tenha ficado CLARO na minha resposta anterior, que vc tenha ENXERGADO que no meu segundo SELECT eu usei SIM o NVL no LEAD em cima de uma data, E ESSE NVL retornou SIM o valor data : seja qual for o problema, SE VC ESTIVER usando o NVL apenas em cima de uma coluna data (ou da função LEAD em cima de coluda DATE, que retorna DATE também) , o NVL RESPEITA SIM o datatype da função….<<<<

Outras desculpas, estava meio corrido por aqui e não reparei nessa parte do seu código.

>>>”Erro na Linha de Comandos : 45 Coluna : 159
Relatório de erros –
Erro de SQL: ORA-54013: A operação INSERT não é permitida em colunas virtuais
54013. 0000 – “INSERT operation disallowed on virtual columns”
*Cause: Attempted to insert values into a virtual column
*Action: Re-issue the statment without providing values for a virtual column”<<<<

Esqueci de excluir as colunas virtuais dos resultado do insert…foi mau!

>>>”Erro a partir da linha : 1.414 no comando –
insert into SGI5_TAB_CUS_REP_RAT (CUS_FAM, CUS_DTA, CUS_DIA, CUS_PRO, CUS_PPO, CUS_VLT_RTO, CUS_VLU_RTO, CUS_VLT_RMO, CUS_VLU_RMO, CUS_USU, CUS_DUA, CUS_DBA)
values (3060, to_date(’01-11-2021′, ‘dd-mm-yyyy’), 0, 0, 0.000000, 640503.000000, 3.889800, 311624.000000, 1.892500, 9001, to_date(’22-11-2021 15:04:25′, ‘dd-mm-yyyy hh24:mi:ss’), 30/12/1899)
Erro na Linha de Comandos : 1.415 Coluna : 186
Relatório de erros –
Erro de SQL: ORA-00932: tipos de dados inconsistentes: esperava DATE obteve NUMBER
00932. 00000 – “inconsistent datatypes: expected %s got %s”
*Cause:
*Action:==> OU SEJA, a tool que vc usou pra extrair os INSERTs falhou, pelo jeito, e trouxe um valor 30/12/1899 que é completamente Absurdo, Tanto por não ter aspas quanto pela tool não ter reconhecido a coluna CUS_DBA como DATE, que ela é :”<<<<

O 30/12/1899 é uma data que o delphi automaticamente manda para os nulos da aplicação. No inicio de carreira, não os tratava como nulo.

>>>”===>> PERFEITO, Suave como o éter, Absolutamente Erro nenhum, tá ok ???? Isso COMPROVA que OU é um erro no seu código real , não presente nessa versão simplificada, OU é pau/limitação da tua tool de front-end ao processar o MERGE, OU é essa variável V_DTA que eu já apontei acima, OU vc tem mais código “oculto” aí que vc não nos motrou E que vc TEM QUE DEBUGAR, tá certo ???”<<<<

Uso como front-end o PLSQL developer. Será um bug no SW?

Vou tentar fazer uns testes SQLDeveloper da própria Oracle, vamos ver no que dá!

Chiappa, muito obrigado pelo seu interesse no caso.

[José Laurindo ChiappaModerador]

Respostas :

“”

>>Então: PRIMEIRO os dois SELECTs absolutamente não são os mesmos, no SQL que roda ok isolado vc escreveu :

SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
NVL(LEAD(A.CUS_DTA,1) OVER ….

ENQUANTO QUE no SQL do MERGE , vc escreveu :

SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
TO_DATE(NVL(TO_CHAR(LEAD(A.CUS_DTA,1) OVER…..<<<<

Fiz dessa maneira pq assim, o código roda!!! Só por isso. Sei que não tem o menor sentido o que fiz. Eu converti o retorno do LEAD em string, tratei o nulo e reconverti em data. Sei que é burro, que é desnecessário, mas foi dessa maneira que o código funcionou!!!
“”

-> ESTE é o meu ponto PRINCIPAL da minha Resposta, e o Núcleo da Orientação que posso te dar : **** PERCEBA ** e *** COMPROVE *** que na minha resposta, com os SEUS DADOS e com o SEU SELECT, apenas limpo das conversões doidas E daquele -1 E daquela variável V_DTA que vc *** Não Especificou em lugra NENHUM no comando MERGE nem no SELECT direto *** , FUNCIONOU ABSOLUTAMENTE CORRETO, PROVANDO que é issue aí do teu lado, que vc PRECISA DEBUGAR….

“”
>>>*** PERCEBA ** que esses identificador V_DTA ao que parece (julgando pelos CREATEs) ** não existe ** dentro de nenhuma das tabelas, ENTÃO o SQL vai considerar que isso é uma VARIÁVEL… Como essa variável NÂO FOI CRIADA antes de rodar o SQL isoladamente ao que vejo, COMO É que vc está conseguindo rodar SEM receber um erro :

Erro de SQL: ORA-00904: “V_DATA”: identificador inválido<<<<

Esse V_DATA esta declarado na procedure que passei no pastebin, e definida como data.
”

-> Então, colega : SE a V_DATA só está DEFINIDA NO BLOCO PL/SQL apenas, *** como é *** que vc diz que consegue rodar o SQL isoladamente com sucesso e não recebe msg de identificador inválido ??? Será que na verdade , quando vc diz que “consegue rodar o SELECT isoladamente com sucesso”, na verdade vc está rodando o BLOCO PL/SQL que contém o SELECT mas sem o MERGE ?? ALGUMA COISA vc não está mostrando pra gente, OU de repente a sua tool, uma vez que vc já tinha rodado o bloco PL/SQL antes, manteve na memória a variável V_DATA da execução anterior…. Não sei…

”
>>>”===>> PERFEITO, Suave como o éter, Absolutamente Erro nenhum, tá ok ???? Isso COMPROVA que OU é um erro no seu código real , não presente nessa versão simplificada, OU é pau/limitação da tua tool de front-end ao processar o MERGE, OU é essa variável V_DTA que eu já apontei acima, OU vc tem mais código “oculto” aí que vc não nos motrou E que vc TEM QUE DEBUGAR, tá certo ???”<<<<

Uso como front-end o PLSQL developer. Será um bug no SW?
”

-> Não faço idéia,não uso esse software aí : primeira coisa, é tentar reproduzir o meu exemplo (que, repito, É igual ao seu apenas SEM o -1 e SEM as conversões doidas E com os parêntesis confirmados todos de abrirem/fecharem nas posições corretas) com os seus dados TANTO no sqlplus QUANTO no Oracle SQL Developer e veja lá – funcionando, como ACREDITO que VAI FUNCIONAR, pois os dados que usei foram os MESMOS que vc passou (só em quantidade menor, por causa da limitação do site) , tá PROVADO que OU era bug na sua front-end, OU era erro mesmo no seu código…

IMPORTANTE : SE meu código não funcionar com os SEUS dados completos, como eu tinha dito E REPITO, preste TOTAL ATENÇÃO nó código INTERNO que possa estar sendo disparado nesse database e/ou nessa tabela, como TRIGGERs, CONSTRAINTS, DEFAULTs… OBRIGATORIAMENTE quando vc faz um INSERT ou UPDATE numa tabela que possua triggers de DML a triggers É DISPARADA, as CONSTRAINTs são checadas, o DEFAULT é aplicado se a coluna não for especificada no INSERT, E isso seja INSERT ou UPDATE enviado diretamente, SEJA gerado pelo MERGE , PODE SER que esses dados a mais que vc tem aí e não estavam presentes no meu teste estejam com valores que conflitem com as constraints/defaults/triggers, digamos…
E igualmente, se a tabela possuir view materializada com REFRESH automático, quando o MERGE comitar, o código da VM vai ser executado….

SE eu tivesse que apostar, eu Apostaria um sorvete de limão (meu limite máximo para apostas) que na verdade é é teu código, mas estes pontos que eu disse acima TEM que ser indicados, são coisas Possíveis….

Agora é POR SUA CONTA o debug, já tendo sido mostrado e comprovado que o SGBD ORACLE tá fazendo o que ele TEM que fazer, Certinho – em princípio tá DESCARTADA a chance de ser problema/bug/erro no software SGBD ORACLE, INCLUSIVE estando também COMPLETAMENTE DESCARTADA a Possibilidade que vc tinha aventado no início da thread do NVL e/ou do LEAD estar retornando algum datatype diferente do datatype do valor de substituição (no caso do NVL) ou do valor da coluna/expressão do LEAD…

Abraços,

Chiappa

[ESSanchesParticipante]

Obrigado Chiappa. Vou debugar aqui. Se encontrar algo, retorno!!!

[]s

[ESSanchesParticipante]

Bom dia Senhores.

Rodei no Oracle SQLDeveloper é acontece exatamente o mesmo erro. Rodei o seguinte condigo em um bloco:

MERGE INTO SGI5_TAB_CUS_RET_RAT X
USING(
SELECT A.CUS_FAM FAM,
A.CUS_DTA DMO_INI,
NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM
ORDER BY A.CUS_FAM,
A.CUS_DTA),TRUNC(SYSDATE)) DMO_FIN,
A.CUS_VLT_RTO VLT_RTO,
A.CUS_VLU_RTO VLU_RTO,
A.CUS_VLT_RMO VLT_RMO,
A.CUS_VLU_RMO VLU_RMO
FROM SGI5_TAB_CUS_REP_RAT A
WHERE A.CUS_DTA >= TO_DATE(’01/01/2003′,’DD/MM/YYYY’)
) Y ON (X.CUS_FAM = Y.FAM AND
X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN)
WHEN MATCHED THEN
UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
X.CUS_VLU_RTO = Y.VLU_RTO,
X.CUS_VLT_RMO = Y.VLT_RMO,
X.CUS_VLU_RMO = Y.VLU_RMO;

Veja que NÃO tem conversões de data, não tem mais variáveis, e o erro persiste. Troquei o front end, e o erro persiste.

To achando que pode ser algum coisa no meu cadastro…….mas não tenho a menor ideia do que possa ser.

Passando somente pra dar um posicionamento.

ATT

[José Laurindo ChiappaModerador]

Confirmadíssimo que é dado incorreto/problema no seu cadastro, OU algum código “interno”, tipo default ou trigger ou view materializada, que está disparando e causando erro, olha aqui no meu database, onde (claro) não tenho nenhum código “interno” E só tenho os poucos dados que vc passou, onde todos estavam com valores ok :

SCOTT@xepdb1::CONTAINER=XEPDB1> ed
Gravou file afiedt.buf

1 MERGE INTO SGI5_TAB_CUS_RET_RAT X
2 USING(
3 SELECT A.CUS_FAM FAM,
4 A.CUS_DTA DMO_INI,
5 NVL(LEAD(A.CUS_DTA,1) OVER (PARTITION BY A.CUS_FAM
6 ORDER BY A.CUS_FAM,
7 A.CUS_DTA),TRUNC(SYSDATE)) DMO_FIN,
8 A.CUS_VLT_RTO VLT_RTO,
9 A.CUS_VLU_RTO VLU_RTO,
10 A.CUS_VLT_RMO VLT_RMO,
11 A.CUS_VLU_RMO VLU_RMO
12 FROM SGI5_TAB_CUS_REP_RAT A
13 WHERE A.CUS_DTA >= TO_DATE(’01/01/2003′,’DD/MM/YYYY’)
14 ) Y ON (X.CUS_FAM = Y.FAM AND
15 X.CUS_DMO BETWEEN Y.DMO_INI AND Y.DMO_FIN)
16 WHEN MATCHED THEN
17 UPDATE SET X.CUS_VLT_RTO = Y.VLT_RTO,
18 X.CUS_VLU_RTO = Y.VLU_RTO,
19 X.CUS_VLT_RMO = Y.VLT_RMO,
20* X.CUS_VLU_RMO = Y.VLU_RMO
SCOTT@xepdb1::CONTAINER=XEPDB1> /

48 linhas mescladas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> Rodou blz teu código, sem eu fazer alteração alguma… Num caso assim, se eu fosse vc eu Certamente acionaria o DBA, que é quem é capaz de fazer TRACEs (tipo cfrme mostrado em https://www.fabioprado.net/2013/09/analisando-traces-em-bancos-de-dados.html) e validações internas mais profundas nesse banco, por exemplo setando um evento de dump quando o erro é encontrado (https://forums.oracle.com/ords/apexds/post/how-to-set-events-3538 mostra para um erro de ORA-01652, mas funciona para qquer um) e ele pode te ajudar extrair dados do error stack, tipo mostrado em https://stackoverflow.com/questions/41373379/how-to-check-which-value-is-causing-sql-error-ora-01722-invalid-number …

Abraços,

Chiappa

[ESSanchesParticipante]

Boa tarde. Desculpe a demora.

Chiappa, vou fazer isso. Falarei com o DBA pra ver se ele consegue debugar alguma coisa. Realmente não tenho conhecimento para essa operação.

Quando ele achar algo (se ele conseguir achar, rs), retorno.

Realmente muito obrigado pelo tempo dispendido.

Emerson

[Autor]

Posts