Is Cron Jobs an option 1

[iamtrying]

Hello,

I do not know if I am posting in the correct forum. I am facing a task of extracting data from an Oracle 9i database every 6 hours and afterwards sending the same data by FTP to a remote site.

Is it feasible to accomplished all the task in Oracle as a job? Or, should I run the script in Oracle, place the data in a directory, and have an auto FTP client (FTPAuto Pro) pull the data every hours and transfer? I am still facing a problem of having the date in the SQL script roll to the current_day-1. Can the script be written to roll the date?

Here is the script:

set pagesize 0;
set heading off;
set trimspool on;
set feedback off;
alter session set nls_date_format='YYYYMMDD';
spool DailyPiece_RTO.log

select
distinct(b."badge")||','||
a."opDate"||','||
ltrim(rtrim(a."blockRoute"))||','||
a."actDrivBegTime"||','||
a."actDrivEndTime"||','||
c."lastName"||','||
c."firstName"||','||
ltrim(rtrim(a."vehicleID"))||','||
ltrim(rtrim(a."blockID"))||','||
ltrim(rtrim(a."runNumber"))||','||
a."division"
from "dailyPiece" a,"employeeStatus" b,"employee" c
where a."opDate" between '20040118' and '20040119'
and a."vehicleID" is not null
and a."emp_SID"=b."emp_SID"
and b."emp_SID"=c."emp_SID";

spool off;
;

Thanks

 

[mdwyer]

Is it feasible to accomplished all the task in Oracle as a job?

The Job Scheduler could certainly run the query every 6 hours. A pl/sql procedure could use UTL_FILE to put the output on the file system. I'm not familiar with any Oracle built-in utilities for FTP, so you might still need FTPAutoPro.

Or, should I run the script in Oracle, place the data in a directory, and have an auto FTP client (FTPAuto Pro) pull the data every hours and transfer?

You can run your script as shown using a cron. We do that on a 24-hour basis, and the shell script also kicks off the ftp process using the "here document" method.

I am still facing a problem of having the date in the SQL script roll to the current_day-1. Can the script be written to roll the date?

It certainly can. [Your example threw me off with the 2004 year. I assume you meant 2005.]

where a."opDate" between sysdate-1 and sysdate

Now, your post doesn't suggest it, but just in case: If the remote "site" is another Oracle database, you can accomplish this much better with a database link. Although, depending on the data volume and communications, I've seen file transfers-plus-sqlloader beat dblink performance hands down, but that was 6-8 years ago.

 

[iamtrying]

mdwyer

Thank you for the code. It works flawlessly. I added the code to the script and created a job in Oracle Management Server to run every 6 hours. The log (txt file) is spooled to a directory. The utility program AutoFTPPro_5 then upload the txt file to a remote FTP server every 6 hours. Problem Solved.

I chose the method above because a pl/sql procedure which use UTL_FILE would send the userid & password to the remote FTP server. Good Job