SS64 Discussion Forum

junaidahsm

Just Registered

Registered: 2007-05-21

Posts: 1

How to spool file to ftp

Hi,
I've write a simple sql where the spool file is locate to my local hard disk.
Below is the simple sql:

spool C:\PBTW\210220080109.txt;
set serverout on size 1000000;
set linesize 1000;
set heading off;
set feedback off;
set newpage NONE;
set space 0;
set showmode off;
COL Item_Code FORMAT A25;
COL Item_Description FORMAT A30;   
SELECT
decode(substr(b.segment1,1,3),'IND',substr(b.segment1,8,25),substr(b.segment1,4,25)) "Item_Code",
substr(b.description,1,30) "Item_Description",
to_char(a.transaction_date,'yyyymmdd') "Transaction Date",
decode(sign(a.transaction_quantity), -1, '2', 0,'1', 1, '1') "Transaction Mark"
FROM mtl_material_transactions a,
mtl_system_items_b b
WHERE b.segment1 LIKE '%DE-%'
AND a.inventory_item_id = b.inventory_item_id
AND a.organization_id = b.organization_id
AND a.transaction_type_id IN (31,41) 
AND a.organization_id=410
AND TRUNC(a.transaction_date)=TRUNC(SYSDATE-1)
spool off;

Question:
What should I do if I want to spool the file result into ftp server?
Let say I want to spool the file into this URL ftp://oradisc/folder01, where got ID and password to enter to this folder (eg: ID: ME Password: ME01)

Please advise.
Thank you.

Simon Sheppard

Super Administrator

Registered: 2005-08-27

Posts: 506

Website

Re: How to spool file to ftp

All you need is a 2 line batch script like this

@echo off
START /WAIT iSQL -U user -P password -i spool.sql
FTP -s:PutFiles.ftp

put the script you have above into the file spool.sql
and put the FTP details into PutFiles.ftp  (see http://www.ss64.com/nt/ftp.html)
along with the filename to be posted C:\PBTW\210220080109.txt