Retrieve data from Oracle Database at specific datetime then post them to firebase in php

Question

Describe the project idea:

I am working on system that is running on Oracle database 8i version. where I would like to execute a query that is running automatically monthly (using php or java) to retrieve some data of due payments from some users of next month, then post these data to google Firebase.

Describe my question:

Instead of running the code manually by clicking the button or running the script manually, I would like to know how to execute the query automatically on specific day of each month. For both retrieving data from Oracle and posting data to Firebase. but not overusing the processor on the server by looping forever.

I don't know if it can be done within php code or I should use an external program or service to run my function automatically at the specific time

An example pseudocode:

a very simple example to explain my process, the actual code will be more organized . your ideas are very appreciated

// some Proramming language API to call myFunction() automatically 
/* this is what I need  */

// check date
if (today() = 20-currentMonth-currentYear)
(
   myFunction()
)

function myFunction()
{ 
  //conneting to database
  connect to oracle(connectionURL, username, password)
  // running connection
  run connection

  // executing updated query
  execute query(Select * from tableName where date 01-nextMonth-currentYear)

  // fetching query results
  Loop throw query result

  // prepare JSON format
  add result to json(object or array)

  // close database connection
  close oracle connection


  // sending json to firebase
  send json result to Firebase RestAPI
}

programming language:

PHP

Answer 1

Gosh, 8i? That's an ancient piece of software.

Anyway: you don't need PHP nor an external program - all you need is a job scheduled in the database. DBMS_JOB is supposed to do that. Here's 8i documentation for that package: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76956/jobq.htm#5727

Here's an example of how to do that:

SQL> set serveroutput on
SQL> -- creating a dummy procedure; yours would collect data
SQL> create or replace procedure p_test
  2  is
  3  begin
  4     null;
  5  end p_test;
  6  /

Procedure created.

SQL> -- schedule a job. NEXT_DATE says when it is executed for the first time,
SQL> -- while INTERVAL says when to re-execute it -> monthly, on 5th of every month,
SQL> -- at 14:30 
SQL> declare
  2     x   number;
  3  begin
  4     sys.dbms_job.submit (
  5        job         => x,
  6        what        => 'p_test;',
  7        next_date   => to_date ('05.12.2017 14:30:00', 'dd.mm.yyyy hh24:mi:ss'),
  8        interval    => 'add_months(to_date(''05.12.2017'', ''dd.mm.yyyy''), 1) + 14 / 24 + 30 / (24*60)',
  9        no_parse    => false);
 10     sys.dbms_output.put_line ('Job Number is: ' || to_char (x));
 11     commit;
 12  end;
 13  /
Job Number is: 2895612

PL/SQL procedure successfully completed.

SQL> -- check job info
SQL> select * from user_jobs where job = 2895612;

Now, that's how to collect data. Unfortunately, I have no idea how to transfer it into Firebase (if I had to, I'd mention the following keywords: database link, heterogeneous services. Or, Google for it, of course). Hopefully, someone else will be able to assist.