page under construction

revision required, a duplicate article might exist

Question about file transfer

How can I transfer files from a database server to an external server and then return them to the database using a middle server?

Solution Outline

  1. Start a database procedure to generate an exchange file and transfer this file to a "middle" server.
  2. Determine the size of the transfer file on the middle server and use a script to transfer this file to an external server.
  3. Process data on the external server and confirm that the result file have been generated before starting a script on the middle server to transfer the file from external server to the middle one.
  4. After the script in step 3 has finished transferring the file from the external server, start a database procedure to import the transferred file to the database.

Schema of File Movements

Question about scripts and database procedures

In particular, I want to know how to invoke remote script and database procedure? I checked the document in your official website but didn’t get any clue about this. Can JobScheduler fulfill our requirement?

Solution Outline

There are several ways to implement such a scenario.

A typical workflow would be to use a single JobScheduler installed on the middle server with a job chain with several steps:

  • Step (1) start the database report. You can use the SQL*Plus, PL/SQL or the ManagedDatabase jobs from the JITL package.

  • Step (2) check the size/existence/content of the output from step 1 using the existsFile job from the JITL package.
  • Step (3) transfer the file from the middle server to the external one using, for example, SFTP and the YADE Job from the JITL package.
  • Step (4) start the processing on the external server using SSH.
  • Step (5) Use SFTP and the YADE job to transfer the result of step (4) to the middle server.

  • Step (6) Start the processing of the result file using, for example, SQL*Plus or PL/SQL.