Usar variaveis de JOB para substituir dblink na PROC.

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

O que preciso.

tenho um monitoramento e para não criar N procedures com base nas N coletas que tenho, quero chamar 1 procedure com tudo, em N jobs que passo o parametro action
por exemplo

Selecionar tudo

  BEGIN PROC_REP_DEV_ALL_OBJ_ALTERED('15','COLETA_HML_DESENV'); END;

porém to travando numa questão de que, como estou passando o nome do dblink na chamada do job e consequentemente na procedure ocorre que não consigo fazer
funcionar como variavel a droga do dblink.

alguma sugestão?
seguindo a explicação ele precisa conectar para fazer o insert na tb central

Selecionar tudo

CREATE OR REPLACE NONEDITIONABLE PROCEDURE PROC_REP_DEV_ALL_OBJ_ALTERED(v_cod_db number, V_dblink varchar2)
as

v_recebe varchar2(200);

cursor c1 is  

      select * from tb_all_obj_altered@||'v_recebe'|| where trunc(tb_all_dt_captura) >= (sysdate) - 1;
           

begin

  for a in c1
  
   loop
    INSERT INTO REP_ALL_OBJ_ALTERED
      (ID,
       TB_REP_ALL_DT_CAPTURA,
       TB_REP_ALL_OWNER,
       TB_REP_ALL_OBJ_NAME,
       TB_REP_ALL_OBJ_TP,
       TB_REP_ALL_LAST_DDL,
       TB_REP_ALL_STATUS,
       FK_TB_REPDB)
    
    values
      (SEQ_REP_BI_ALL_OBJ_ALT.nextval,
       sysdate,
       a.tb_all_owner,
       a.tb_all_obj_name,
       a.tb_all_obj_tp,
       a.tb_all_last_ddl,
       a.tb_all_status,
       v_cod_db);
  
    EXIT WHEN c1%NOTFOUND;
  end loop;

  commit;
END;
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Você não pode concatenar dessa forma:

Selecionar tudo

select * from tb_all_obj_altered@||'v_recebe'
Mas pode usar um cursor dinâmico.
Veja esse exemplo:

Selecionar tudo

declare
  cursor template_cursor is select * from tb_all_obj_altered;
    
  l_rec    template_cursor%rowtype;
  l_cursor sys_refcursor;
  l_dblink varchar2(30) := 'SEU_DB_LINK_AQUI';
begin
  open l_cursor for q'|select * from tb_all_obj_altered@|' || l_dblink
                 || q'|where trunc(tb_all_dt_captura) >= (sysdate) - 1 |';
  loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    --seu INSERT OU OUTRAS COISAS AQUI usando l_rec.
    NULL;
  end loop;
  close l_cursor;
end;
Se precisar executar coisas, como um INSERT ou UPDATE, pode usar EXECUTE IMMEDIATE.

Pra entender o q' dá uma lida nisso:
https://livesql.oracle.com/apex/livesql ... KRF6P.html

:-o
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Eu lhe entendo meu caro mas é que imagina que a variavel vai vir do JOB do oracle

Depois ela segue para completar o após o @_variavel_do_db_link_vindo_do_JOB.


para com essa informação, completar o select que contém as informações que me possibilitarão fazer o insert la depois do begin.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

O meu exemplo acima mostra COMO fazer um DBLINK dinâmico.
Como você vai usar ele é por sua conta. Eu não coloquei ali a procedure pronta pra você executar.

Abaixo, eu mudei um pouco pra adaptar no código que você me passou.
É isso que você precisa?

Selecionar tudo

CREATE OR REPLACE NONEDITIONABLE PROCEDURE PROC_REP_DEV_ALL_OBJ_ALTERED(v_cod_db number, V_dblink varchar2)
as
  cursor template_cursor is select * from tb_all_obj_altered;   
  l_rec    template_cursor%rowtype;
  l_cursor sys_refcursor;
begin
  open l_cursor for q'|select * from tb_all_obj_altered@|' || V_dblink
                 || q'|where trunc(tb_all_dt_captura) >= (sysdate) - 1 |';
  loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    --
    INSERT INTO REP_ALL_OBJ_ALTERED
      (ID,
       TB_REP_ALL_DT_CAPTURA,
       TB_REP_ALL_OWNER,
       TB_REP_ALL_OBJ_NAME,
       TB_REP_ALL_OBJ_TP,
       TB_REP_ALL_LAST_DDL,
       TB_REP_ALL_STATUS,
       FK_TB_REPDB)
    values
      (SEQ_REP_BI_ALL_OBJ_ALT.nextval,
       sysdate,
       l_rec.tb_all_owner,
       l_rec.tb_all_obj_name,
       l_rec.tb_all_obj_tp,
       l_rec.tb_all_last_ddl,
       l_rec.tb_all_status,
       v_cod_db);
  end loop;
  close l_cursor;
  commit;
END;
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Pelo menos não deu erro de recompile.
para tanto ao executar via parametro

imagem em anexo

ocorre erro.

Selecionar tudo

ORA-00933: comando SQL não encerrado adequadamente
ORA-06512: em "PROC_REP_DEV_ALL_OBJ_ALTERED", line 11
ORA-06512: em line 1

o L_REC fica com null a impressão que passa e que a tb_all_obj_altered não tem nada, mesmo tendo valores.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

O que tem na linha 11 ?
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Quando eu vou pelo modo debug


esse item do cursor esta null quadno passo o mouse encima da linha

Selecionar tudo

 cursor   template_cursor is select * from tb_all_obj_altered;   
esse item do cursor esta null quando passo o mouse encima da linha

Selecionar tudo

  l_rec    template_cursor%rowtype;
  l_cursor sys_refcursor;

porém ao ao pesquisar na tabela tb_all_obj_altered

Selecionar tudo

1421	07/05/2019 14:39:15	SYS	WRH$_SERVICE_STAT_PK	INDEX PARTITION	06/05/2019 21:28:51	VALID
tem dados
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Eu sugiro você tentar entender o que esse código faz.

Este cursor aqui:

Selecionar tudo

cursor   template_cursor is select * from tb_all_obj_altered;
Só serve pra definir a ESTRUTURA de retorno da sua tabela.
Este cursor NUNCA vai ser executado!

Ele só serve pra poder criar isto:

Selecionar tudo

l_rec    template_cursor%rowtype;
Isto é a variável de retorno que vai ser usada depois. Pra você não ter que criar 200 variáveis separadas.

Esta variável de retorno vai ser usada aqui:

Selecionar tudo

  open l_cursor for q'|select * from tb_all_obj_altered@|' || V_dblink
                 || q'|where trunc(tb_all_dt_captura) >= (sysdate) - 1 |';
  loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    --
Em resumo: Aquele cursor declarado NÃO está sendo executado!
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Tudo bem meu caro moderador, se for tosto as minhas perguntas é porque obviamente se soubesse não teria buscado o forum...


mas é que achei engraçado que que não retorna linhas, seria.....


mas obrigado mesmo, já me deu um apoio de grande suporte. porque agora não esta dando erro no codigo apenas não retorna nada na variavel que tem que aparecer....
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Pra testar se o problema é falta de dados ou é outra coisa, executa:

Selecionar tudo

select * from tb_all_obj_altered@COLETA_HML_DESENV where trunc(tb_all_dt_captura) >= (sysdate) - 1;
Se essa query não retornar nada, então não existe nenhuma linha nessa tabela com TB_ALL_DT_CAPTURA nas últimas 24 horas. (ou seja, o programa ta funcionando).

Tenta executar a procedure direto, sem ser via JOB.
Dica 1:
coloca uns DBMS_OUTPUT ali:

Selecionar tudo

CREATE OR REPLACE NONEDITIONABLE PROCEDURE PROC_REP_DEV_ALL_OBJ_ALTERED(v_cod_db number, V_dblink varchar2)
as
  cursor template_cursor is select * from tb_all_obj_altered;   
  l_rec    template_cursor%rowtype;
  l_cursor sys_refcursor;
begin
  open l_cursor for q'|select * from tb_all_obj_altered@|' || V_dblink
                 || q'|where trunc(tb_all_dt_captura) >= (sysdate) - 1 |';
  loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    --
    DBMS_OUTPUT.PUT_LINE(  l_rec.tb_all_last_ddl );
    --
    INSERT INTO REP_ALL_OBJ_ALTERED
      (ID,
       TB_REP_ALL_DT_CAPTURA,
       TB_REP_ALL_OWNER,
       TB_REP_ALL_OBJ_NAME,
       TB_REP_ALL_OBJ_TP,
       TB_REP_ALL_LAST_DDL,
       TB_REP_ALL_STATUS,
       FK_TB_REPDB)
    values
      (SEQ_REP_BI_ALL_OBJ_ALT.nextval,
       sysdate,
       l_rec.tb_all_owner,
       l_rec.tb_all_obj_name,
       l_rec.tb_all_obj_tp,
       l_rec.tb_all_last_ddl,
       l_rec.tb_all_status,
       v_cod_db);
  end loop;
  close l_cursor;
  commit;
END;


Executa assim:

Selecionar tudo

set serveroutput ON;
BEGIN 
  PROC_REP_DEV_ALL_OBJ_ALTERED('15','COLETA_HML_DESENV'); 
END;
Pelo menos rodando assim, você vê se entrou no loop.
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Ta bem chefe.

Te agradeço mesmo, de coração o apoio.....a caminhada para desenvolver legal demora um pouco, estou nela mas vai dar tudo certo :)


quando vieres no sul do pais lhe pago uma cerveja bem gelada :) :-o
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

beleza. Pode mandar mais pergunta. Sem problema.

PS:
quando vieres no sul do pais lhe pago uma cerveja bem gelada
Eu morava em POA, trabalhava na AdvancedIT, na Rua 18 de Novembro.
Acho que eu conheço a região :-D
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Show de bola, pensa mundo pequeno.

Add ai chefe.

https://www.linkedin.com/in/marcoborgesrs/
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Cara descobri uma coisa.



Se eu faço assim, que não e o certo mas a fins de teste.

Selecionar tudo

  open l_cursor for  select * from tb_all_obj_altered@COLETA_HML_DESENV where trunc(tb_all_dt_captura) >= (sysdate) - 1 ;

Ele carrega o L_REC corretamente la dentro do loop.


Agora, depois de queimar a mufa, conclui que no fim da forma que ele faz abaixo:

Selecionar tudo

open l_cursor for q'|select * from tb_all_obj_altered@|' || V_dblink || q'|where trunc(tb_all_dt_captura) >= (sysdate) - 1 |';

a impressão que me passa é que não consegue montar o select....

porque dai não popula o L_REC e consequentemente não consegue realizar o insert porque não está trazendo nenhuma informação.
Anexos
implicito.PNG
msep
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 09 Mai 2019 10:36 am

Tentei esta linha de raciocinio tb

Selecionar tudo

v_sql varchar2(500);
v_open varchar2 (500);

  cursor template_cursor is  select * from tb_all_obj_altered;
  l_rec    template_cursor%rowtype;
  l_cursor sys_refcursor;
begin

      
  --open l_cursor for  select * from tb_all_obj_altered@COLETA_HML_DESENV where trunc(tb_all_dt_captura) >= (sysdate) - 1 ;
  --open l_cursor for q'|select * from tb_all_obj_altered@|' || V_dblink || q'|where trunc(tb_all_dt_captura) >= (sysdate) - 1 |';

v_sql := ' select *  from tb_all_obj_altered@' || V_dblink ||  ''||'  where trunc(tb_all_dt_captura) >= (sysdate) - 1 ';
v_open := ' open l_cursor for  '|| v_sql || ' ; '; 

      

     
      execute immediate v_open;

porém apesar de ele montar o v_open, ele da erro de execução.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

O Execute Immediate funciona pra quando você quer executar comandos.
Por exemplo, um UPDATE ou INSERT ou mesmo uma PROCEDURE ou bloco PLSQL.

Mas pra um SELECT, o objetivo é retornar linhas!
Por isso que você precisa fazer isso num Cursor Dinâmico.

Caso você só precise fazer um UPDATE, daí sim!
Cria uma variável com o comando UPDATE usando os DB LINKS, e roda com EXECUTE IMMEDIATE.
(Daí nem precisa de Cursor, nem select, nada)
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Testei aqui o EXECUTE IMMEDIATE com a cláusula INTO.
Funciona também:

Selecionar tudo

declare 
  va varchar2(100);
  vb varchar2(100):='TESTE';
begin
  execute immediate 'select '''||vb||''' from dual' INTO va;
  dbms_output.put_line(va);
end;
Mas isso é pra quando o SQL vai retornar apenas 1 linha.
Responder
  • Informação
  • Quem está online

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