Create an Oracle Agent/Job for Postgresql

Question

I wonder if it is possible to create an agent/job in Oracle to query the data of a given table and insert the data from this table to another table that is in Postgresql.

Or if I can through Postgresql Pgagent create a job to perform the query in the Oracle table and insert the data in the Postgresql table.

Answer 1

In Postgres, you can write a stored Procedure using the language PL/Python able to connect in a bank Oracle, enabling the exchange of data, see only:

CREATE FUNCTION oracle_foobar()
RETURNS SETOF tb_foobar AS
$BODY$
  import cx_Oracle

  conn = cx_Oracle.connect('usuario/senha@servidor_oracle/database')
  cur = conn.cursor()

  cur.execute('select * from tb_foobar')
  ret = cur[:]

  cur.close()
  conn.close()

  return ret;
$BODY$
LANGUAGE plpythonu;

Once with the function oracle_foobar() created, you can use it to extract the data from a database Oracle (tb_foobar) and insert them into a database Postgres (tb_xpto), for example:

INSERT INTO tb_xpto ( a, b, c )
(SELECT a, b, c FROM oracle_foobar() WHERE c = 3); 

In the Postgres you can use the pg_cron to execute the function in a scheduled manner.

Every day, at 10:00am, for example:

SELECT cron.schedule('0 10 * * *', $$INSERT INTO tb_xpto ( a, b, c )(SELECT a, b, c FROM oracle_foobar() WHERE c = 3);$$ );