Tom your response was useful. Thanks. one more doubt TOM.
i am dropping that file on remote server say
i want file on batch/xml folder.i do this using

v_bytes_written := UTL_TCP.write_line(con,'CWD /batch/xml');

v_bytes_written := UTL_TCP.write_line(con,'STOR file.xml');

now can i mention the file location from where i want to pick the file.

for ex: oracle server has F drive connected and i want to pick file from that particular drive(F) and and send it to /batch/xml folder of remote server.

How do i mention this using store command? Do i need to put that file on some default directory of local server and ftp process will know from where to pick up that file.
Thaks in advance TOM.

Thanks TOM for answer.

Tom,I thought STOR works as standard put command. Now how would i implement FTP process in oracle. Can you suggest me how can we actually transfer files using ftp in PL/SQL.

Thanks in advace.

Thanks Tom,
your response was very useful.Now i am thinking of doing this using Java stored procedure. Tom can you send me some sample code for FTP in java stored proc. I am new to orcale and java. How to call java stored proc from oracle and what is the archirecture.

Thanks in advance.

Tom,
I implemented the put method using sun.net.ftp.* and getting some exceptions.
some of them were for granting permissions and resolved using



begin
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'remote_server', 'resolve');
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'IP address:21', 'connect,resolve');
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'localhost:1024-', 'listen,resolve' );
exception
when others then
dbms_output.put_line(sqlerrm);
end;


still i am getting this exception
Java exception: sun.net.ftp.FtpProtocolException: PORT

my code is



create or replace and compile java source named "ftp_utility" as
import java.io.*;
import sun.net.ftp.*;
import oracle.sql.*;



public static void put(
String host,
NUMBER port,
String username,
String password,
String sourceFile,
String remoteDirectory ) throws Exception
{

FtpClient ftpClient = null;

try
{
int lastSlash = sourceFile.lastIndexOf('/');
String file = sourceFile.substring(lastSlash + 1);
String directory = sourceFile.substring(0, lastSlash);
ftpClient = new FtpClient(host, port.intValue());
ftpClient.login(username, password);
ftpClient.cd(remoteDirectory);
ftpClient.put(sourceFile);
}
finally
{
if (ftpClient != null)
{
ftpClient.closeServer();
}
}
}




sourcefile variable has local path in it as
'F:/ftp/sourcefile.txt'

could you please help me in this...

am i using the right class...

how can i set the mode(active or passive) using this class....

thanks in advance

sorry Tom for incomplete information

here is complete code...



create or replace and compile java source named "ftp_utility" as
import java.io.*;
import sun.net.ftp.*;
import oracle.sql.*;

public class ftp_utility {

public static void put(
String host,
NUMBER port,
String username,
String password,
String sourceFile,
String remoteDirectory ) throws Exception
{

FtpClient ftpClient = null;

try
{
int lastSlash = sourceFile.lastIndexOf('/');
String file = sourceFile.substring(lastSlash + 1);
String directory = sourceFile.substring(0, lastSlash);
ftpClient = new FtpClient(host, port.intValue());
ftpClient.login(username, password);
ftpClient.cd(remoteDirectory);
ftpClient.put(sourceFile);
}
finally
{
if (ftpClient != null)
{
ftpClient.closeServer();
}
}
}
}
/

Procedure PRC_FTP_PUT (
p_host in varchar2,
p_port in number,
p_username in varchar2,
p_password in varchar2,
p_source_file in varchar2,
p_remoteDirectory in varchar2)
as language java name 'ftp_utility.put(
java.lang.String,
oracle.sql.NUMBER,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String)';
/

PRC_FTP_PUT(p_ftp_server,21,p_userid,p_pwd,'F:/xml/test.xml','/batchxml/init');


actual error message i am getting is

ORA-29532: Java call terminated by uncaught Java exception: sun.net.ftp.FtpProtocolException: PORT

Thanks

Tom,
Happy new year.

I have checked the server. It seems there is no firewall problem.

Could you please help me further finding out what could be wrong.I wasn't able to find out FTPCliet which supports passive connections.

Thanks in advance Tom for your support.

happy new year
Nisheeth

Tom,
There is no firewall i am sure and one more thing i ran that java progaram (not from otacle) form my machine and i ma able to FTP to the server.

Does this mean that oracle srevr is having some access permission probelm to remote server.?


Yesterday i granted these permission to server.

Can you tell me if there is any thing else i need to grant to get rid of PORT exception.

begin
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'test-mdsftp.humana.com', 'resolve');
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'test-mdsftp.humana.com:20', 'connect,resolve');
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'test-mdsftp.humana.com:21', 'connect,resolve');
dbms_java.grant_permission( 'ENROLL', 'SYS:java.net.SocketPermission', 'localhost:1024-', 'listen,resolve' );
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Thanks in advance.
Nisheeth

Thanks Tom it worked...

you have been very helpful Tom.

Thanks again
Nisheeth

I used nisheet mitharwal complete code in this document and
set the dbms_java as mentioned, but I am receiving the following error: Java exception: sun.net.ftp.FtpProtocolException: STOR. Can you assist me in resolving this error. We are useing a 8i database.

Thanks,

Ted

Hi Tom,

I have looked at the code you wrote using the sun ftp classes and re-implemented using the jakarta commons net libraries [www.jakarta.org] for the FTPClient.

The following works fine if called outside the database using the main method, but hangs the procedure when called from inside the database [never terminates].

Any ideas?

Code follows
JAVA CLASS
----------
import org.apache.commons.net.ftp.*;
import java.io.*;

public class FtpHelper{
private static FTPClient ftp = new FTPClient();

public static final void main(String[] args){
try{
getFile();
}catch(Exception e){
e.printStackTrace(System.err);
}
}


public static void getFile() throws Exception{
try{
int reply;

//connect to the server
ftp.connect("ftp.yourname.co.uk");

//get the reply code
reply = ftp.getReplyCode();

//if we connected successfully
if(FTPReply.isPositiveCompletion(reply)){

//login
if(ftp.login("username","password")){

//text file download
ftp.setFileType(FTP.ASCII_FILE_TYPE);

//behind firewall so use passive connection
ftp.enterLocalPassiveMode();

//create a new file
File file = new File("c:\\downloaded.html");

//get an outputstream
FileOutputStream out = new FileOutputStream(file);

//read the file
if(!ftp.retrieveFile("file.txt",out)){
throw(new Exception("Didn't get file"));
}

//flush the stream
out.flush();
out.close();

//logout and disconnect
ftp.logout();
ftp.disconnect();

}else{
throw(new Exception("invalid logon"));
}
}else{
throw(new Exception("unable to connect"));
}
}catch(Exception e){
try{
ftp.logout();
ftp.disconnect();
}catch(Exception logoutexception){ throw(new Exception("Couldn't logout");}
}
}
}

PLSQL Wrapper
-------------
create or replace package utlFtp IS

PROCEDURE getFile;

end utlFtp;
/
create or replace package body utlFtp IS

PROCEDURE getFile
AS LANGUAGE JAVA
NAME 'FtpHelper.getFile()';

end utlFtp;

Tom i created my_ftp java procedure as you mentioned in your code its created successfully. but when i run the procedure it give me following error.

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
ORA-06512: at "PSDS.FTP_GET", line 0
ORA-06512: at line 8

here is the block code:

set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
declare
file_data blob;
file_name varchar2(255) := '\ftp-java.txt';
begin
insert into lob_test(id, filename, title, data)
values(2, file_name, 'ftp test', empty_blob)
returning data into file_data;
ftp_get('myftpsite.co.uk',21,'username','pswd',
file_name, file_data, '', 0);
end;
/

please can you help me.

Thanks TOM its work. Thanks for help.

Hi all,

Metalink has a pretty good article (it's a 3-part article) on FTP'ing from PL/SQL using the sun.net.ftp.FtpClient class. The details:

Note:159034.1: </code> http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=159034.1

Note:159061.1: 

http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=159061.1

Note:159062.1: 

http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=159062.1 <code>

Hope it helps...

Kashif

I cannot find the articles that Kashif mentioned from the Metalink. Does anyone have copy of them, and why they were pulled from the Metalink?

Thanks.

Thomas

Hi Tom,

Even after going through this page I am not very sure I grasped it. Is it possible to transfer a file from server to client using some built-in package of Plsql ( maybe through utl_tcp... I am not too sure) ? If yes, could you please demonstrate this through a small piece of code?

Thanks in Advance
Pretaish

Tom,

Currently via cron job setup I am doing our RMAN backup every day at mid night, I am also export one of our main schema every morning at 1.00 am via cron job.


After that every morning, I need to manually transfer two weeks old files from our database server to the backup server through FTP.
Then I remove old file from database server.

Now I want to do this FTP process also automatically via cron job.


Please note:
we need transfer only two weeks old files from the database server and delete after successful ftp from database server.

Each time 6 files created by Backup process.
And plus one .dmp file.

Do I need to use any plsql for it?

I need your comments. thanks

cheers,
Dawar

My OS is AIX 5.2 --IBM

Here what I get

>man netrc
>Manual entry for netrc not found or not installed.

Man has installed on the system.
I can use for other stuff such as passwd, domain etc.

As stated there is a lot to using UTL_TCP for FTP. In the package solution that I have put together it has a built timeout feature to prevent hanging sessions when the server either doesn't respond or a path is invalid. I would be happy to post here but its fairly large package. For thus interested feel free to email me at bsc7080mqc@mylxhq.com and I can email it to you.

Supports

- PUT, GET, REMOVE, RENAME
- SERVER UP VERIFICATION
- BINARY and ASCII SUPPORT
- USES UTL_TCP
- REQUIRES 9iR2+
- BUILT-IN TIMEOUT of 4 MINUTES
- PL/SQL BASED

Tom, here is what i did,

1) I compiled this. It went fine

create or replace and compile java source named "ftp_utility" as
import java.io.*;
import sun.net.ftp.*;
import oracle.sql.*;

public class ftp_utility {

public static void put(
String host,
NUMBER port,
String username,
String password,
String sourceFile,
String remoteDirectory ) throws Exception
{

FtpClient ftpClient = null;

try
{
int lastSlash = sourceFile.lastIndexOf('/');
String file = sourceFile.substring(lastSlash + 1);
String directory = sourceFile.substring(0, lastSlash);
ftpClient = new FtpClient(host, port.intValue());
ftpClient.login(username, password);
ftpClient.cd(remoteDirectory);
ftpClient.put(sourceFile);
}
finally
{
if (ftpClient != null)
{
ftpClient.closeServer();
}
}
}
}
/

2) I compiled the following. it went fine

CREATE OR REPLACE procedure sp_ftp_utility_PUT (
p_host in varchar2,
p_port in number,
p_username in varchar2,
p_password in varchar2,
p_source_file in varchar2,
p_remoteDirectory in varchar2)
as language java name 'ftp_utility.put(
java.lang.String,
oracle.sql.NUMBER,
java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String)';
/


3) did grant...it went fine

begin
dbms_java.grant_permission( 'DEV', 'SYS:java.net.SocketPermission', '169.2.2.12:21', 'connect,resolve' );
exception
when others then
dbms_output.put_line(sqlerrm);
end;

4) I get the following error when I run the following plsql block

DEV>declare
2 file_data varchar2(255) := '/data/home/whited';
3 file_name varchar2(255) := 'c:/list.txt';
4 begin
5 --insert into lob_test(id, filename, title, data)
6 --values(2, file_name, 'Terry Ford debug file', empty_blob)
7 --returning data into file_data;
8
9 sp_ftp_utility_PUT(
10 '169.2.2.12',
11 21,
12 'whited',
13 'password',
14 file_name,
15 file_data
16 );
17 end;
18 /
java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(PlainSocketImpl.java)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java)
at java.net.Socket.connect(Socket.java)
at java.net.Socket.connect(Socket.java)
at sun.net.NetworkClient.doConnect(NetworkClient.java)
at sun.net.NetworkClient.openServer(NetworkClient.java)
at sun.net.ftp.FtpClient.openServer(FtpClient.java)
at sun.net.ftp.FtpClient.<init>(FtpClient.java)
at ftp_utility.put(ftp_utility:23)
declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.net.ConnectException: Connection refused
ORA-06512: at "DEV.SP_FTP_UTILITY_PUT", line 1
ORA-06512: at line 9




Please help.
Thanks in advance.

Hi Tom,

I have a requirement like this:

Using a html interface, we will specify the
folder name and attach a file to be created
in that folder. Assuming that the folder is already created,
how can we do this using pl/sql?

Thanks in advance.

It would actually be quite simple to implement.

USER tkyte
331 Password required for tkyte.
PASS xxxx
230 User tkyte logged in.
PASV
227 Entering Passive Mode (138,2,5,51,179,70)

Once you get the pasv reply from the server, all you need to do is open a connection using the details the server gives you. In the above example, the first 4 digits are the ip address (138.2.5.51) and 45715 would be the port (4th digit times 255 + 5th digit; 179 * 255 + 70). Then just use utl_file to open the file locally (in raw), and utl_tcp to write it to the new connection.

Stefan

Dear TOM,

While doing FTP using the "ftp_utility" i am getting following error

Error Code :-29532, Error Msg : ORA-29532: Java call terminated by uncaught Java exception: java.io.FileNotFoundException: CWD D:\share: 550 D:\share: The filename, directory name, or volume label syntax is incorrect.

we are issuing the following command to call ftp_utility from a procdure on windows environment

PRC_FTP_PUT('102.086.106.157',21,'in002/ab0098675','abc@1234','C:\\sample.xls','D\\share');

Plz help.

In oracle 11d , is it possible to put / get a file into an SFTP site using native packages ?

In oracle 11g , is it possible to put / get a file into an SFTP site using native packages ?

Hi Tom,

I am on oracle 11gr2, i granted all the required java privileges. I am getting an error, when I excute the following code,

declare
file_data blob;
file_name varchar2(255) := 'home/ftpuser/2.sql';
begin
insert into lob_test(id,filename,title,data)
values (2,file_name,'Terry Ford debug file',empty_blob)
returning data into file_data;

ftp_get('127.0.0.1',21,'ftpuser','ftpuser',file_name,file_data);
end;
/

Error at line 1:
ORA-29532: Java call terminated by uncaught java exception:
java.io.filenotfoundexception: CWD /home/ftpuser: 550 failed to change directory.

I am ftping it to my self, and that directory exists on the server. I am also able to ftp file from the terminal window.

Do i need to grant any other privilege? or am I missing something..?
Could you throw me some pointer, what the issue might be please.

Thanks