Use Job variables to replace DBLink on PROC.

[msep]

What I need.

I have a monitoring and not to create n procedures based on the non collections that I have, I want to call 1 procedure with everything, in n Jobs, which steps the action parameter
for example

Select all

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

But I'm locking in a matter that, since I'm passing the name of DBLink in the Job call and consequently in the procedure it happens that I can not do
Run as variable DBLink drug.

Any suggestion?
Following the explanation he needs to connect to make the insert in the central TB

Select all

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;

[dr_gori]

You can not concatenate this way:

Select all

select * from tb_all_obj_altered@||'v_recebe'

But you can use a dynamic cursor.
See this example:

Select all

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;

If you need to perform things, such as an INSERT or UPDATE, you can use Execute Immediate.

to understand Q 'gives a read in this: https://livesql.oracle.com/apex/livesql ... KRF6P.html
: -O

[msep]

I understand you my dear but it is that you can imagine that variable will come from the oracle job

then it follows to complete the after @_variavel_do_db_link_vindo_do_JOB


For this information, complete the SELECT that contains the information that will enable me to make the insert after BEGIN.

[dr_gori]

My example above shows how to make a dynamic dblink.
How you will use it is on your own. I did not put it there ready for you to run.

Below, I changed a little to adapt in the code you passed me.
Is this what you need?

Select all

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]

At least he did not give a repair error.
For both running via parameter

Annex image

occurs error.

Select all

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

The L_REC is with null the impression that passes and that tb_all_obj_altered has nothing, even having values.

[dr_gori]

What's in line 11?

[msep]

When I go by Debug mode


this cursor item This null quette step the mouse on the line

Select all

 cursor   template_cursor is select * from tb_all_obj_altered;   

cursor item This null when the mouse on the line

Select all

  l_rec    template_cursor%rowtype; 
  l_cursor sys_refcursor;

but when the table tb_all_obj_altered
3]]
has data

[dr_gori]

I suggest you try to understand what this code does.

This cursor here:

Select all

cursor   template_cursor is select * from tb_all_obj_altered;

only serves to set the return structure of your table.
This cursor will never run!

It only serves to be able to create this:

Select all

l_rec    template_cursor%rowtype;

This is the return variable that will be used later. For you not have to create 200 separate variables.

This return variable will be used here:

Select all

  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; 
    --

In summary: That declared cursor is not running!

[msep]

All right, my dear moderator, if it is to the , it would be .....


But thank you very much, already gave me a support of great support. Because now it is not giving error in the code just does not return anything in the variable that has to appear ....

[dr_gori]

To test whether the problem is lack of data or is something else, performs:

Select all

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

If this query does not return anything, then there is no row in this table with tb_all_dt_capture in the last 24 hours. (That is, the program is working).

attempts to execute the direct procedure, without being via job.
Tip 1:
puts a dbms_output there:

Select all

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;

executes:

Select all

set serveroutput ON; 
BEGIN  
  PROC_REP_DEV_ALL_OBJ_ALTERED('15','COLETA_HML_DESENV');  
END; 

]

At least turning thus, you see if you entered the loop.

[msep]

Good boss.

Thank you very much, from heart the support ..... The walk to develop cool takes a while, I am in it but it will be all right


When you come to the south of the country I pay you a very cold beer : -The

[dr_gori]

OK. You can send more questions. No problem.
PS:

When it comes to the south of the country I pay a very cold beer

I lived in Poa, worked in Advancedit, On November 18th.
I think I know the region :-d

[msep]

Ball show, think small world.

Add ai boss.
https://www.linkedin.com/in/marcoborgesrs/

[msep]

Dude I discovered one thing.



If I do so, that is not right but the test ends.

Select all

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

He carries the L_REC correctly inside the loop.


Now, after burning the muff, he concludes that at the end of the way he does below:

Select all

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

The impression that passes to me is that you can not mount the SELECT ....

Because DAI does not populate the L_REC and consequently can not perform the insert because not is bringing no information.

[msep]

I tried this line of TB raciocinium

Select all

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;

However, although it reassembling the V_Open, it from the execution error.

[dr_gori]

The Execute Immediate works for when you want to run commands.
For example, an update or insert or even a PLSQL procedure or block.

But for a SELECT, the goal is to return lines!
That's why you need to do this in a dynamic cursor.

If you just need to make an update, then yes!
Creates a variable with the Update command using the DB Links, and runs with EXECUTE IMMEDIATE
(hence does not need a cursor or select, nothing)

[dr_gori]

I tested here EXECUTE IMMEDIATE with clause inlet.
works also:

Select all

declare  
  va varchar2(100); 
  vb varchar2(100):='TESTE'; 
begin 
  execute immediate 'select '''||vb||''' from dual' INTO va; 
  dbms_output.put_line(va); 
end;

But this is for when SQL will return only 1 line.