I would like to know if it is possible to create an agent / job in Oracle to query the data of a given table and insert the data of this table into another table that is in PostgreSQL.
Or you can get through the PostgreSQL PGAgent to create a job to query the Oracle table and insert the data into the PostgreSQL table.
In Postgres , you can write a stored procedure using the PL/Python able to connect to a Oracle database, enabling data exchange, see:
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 you have the oracle_foobar() function created, you can use it to extract the data from a Oracle ( tb_foobar ) database and insert it into a Postgres database ( tb_xpto ), for example:
INSERT INTO tb_xpto ( a, b, c )
(SELECT a, b, c FROM oracle_foobar() WHERE c = 3);
In Postgres you can use pg_cron to run the function in a scheduled way.
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);$$ );