[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3434
  • Last Modified:

is ftp.login part of the standard package ?

is ftp.login part of the standard package ?

I got this example to send a file. Can I generate my data that I require from the query into a buffer and then send it to the ftp server instead of generating a file ?

My problem if I generate a file , the oracle server is in unix environment , the destination where I need to ftp the server is a windows server ! If I cannot generate a buffer is there a way around for this ?


-- Send an ASCII file to a remote FTP server.


DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');
  ftp.ascii(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'test_get.txt',
          p_to_file   => '/u01/app/oracle/test_put.txt');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;
/
0
tech_question
Asked:
tech_question
  • 17
  • 16
  • 4
1 Solution
 
actonwangCommented:
where do you get this example? As far as I know, there is a package UTL_TCP not ftp.login.
ftp.login must be a third party package and not a standard package.


0
 
actonwangCommented:
normally it is not recommend to use UTL_TCP to ftp, see this:

see this:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7078430885640

>>Can I generate my data that I require from the query into a buffer and then send it to the ftp server instead of generating a file ?
I am a little confused by this. The end result on remote windows box should be a file or like, isn't it? So only ways to do I can think of are:
(1) generate a file local and ftp to remote box
(2) mount window box to your local and UTL_FILE directly to it.

or better off, if you know java, write a java store procedure to handle this would be easier. PL/SQL has its region.

Acton
0
 
RCorfmanCommented:
I suspect that this is using the ftp package from oracle-base.
http://www.oracle-base.com/dba/miscellaneous/ftp.pks
http://www.oracle-base.com/dba/miscellaneous/ftp.pkb

You need to load that package first... then you can use it.
Here is an example of it's use:
http://www.oracle-base.com/articles/9i/FTPFromPLSQL9i.php

You should be able to use utl_file (which is a standard Oracle Package) to save the file on your unix box, then use the ftp package to send it off. This is a possible way of doing it if you are a strictly pl/sql shop... if you have Java knowledgable people, then I agree that is a better alternative, but not necessarily so if you are going to be introducing a new technology and that is all that would be in Java, then the pl/sql route is better (because of the longer-term maintenance costs).

The ftp package does not support direct streams, you have to store the data in a file first, then you can send it.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
actonwangCommented:
Hi, is this the one you got :)

http://www.oracle-base.com/articles/9i/FTPFromPLSQL9i.php

In all events, as I said, you can not avoid a file creation or tranfer.
0
 
tech_questionAuthor Commented:
actonwang :

Can I write this function using UTL_TCP  Package  ? to generate a file and ftp it to the remote server using UTL_TCP package  ? Can I call a java class or vbcomponent that has been written to ftp to be called from Oracle?

I need to generate some data from oracle and send it to the remote server via  a file without user intervention!


0
 
RCorfmanCommented:
You certainly CAN do this.
The link that I provided, then actionwang gave the same link shows how to use the ftp package that you had. This link shows how to use utl_file to create a file from a query...

http://www.experts-exchange.com/Databases/Oracle/Q_21813475.html

Between the two, you can create the file, then FTP it.  You'll need to determine exactly what you want the file to look like based on the query.. the example in the above link is a comma seperated.

You can use a trigger to produce the file, or dbms_job to do it... whatever is an appropriate mechanism for when/how you know you need to send it. I suspect dbms_job.
0
 
actonwangCommented:
hi, tech_question,

        RCorfman is right. Also I just gave you the link which, I belive, you were looking at. You can use that package.

        Just do:
        1.use UTL_FILE to create a file. You just got answer on it , didn't you? :)
        2.use ftp package to send it to your target location

        Basically, I think you literally find the solution by yourself already :)

Acton
0
 
tech_questionAuthor Commented:
>>Just do:
     >>   1.use UTL_FILE to create a file. You just got answer on it , didn't you? :)
       >>  2.use ftp package to send it to your target location

for the second part of the questions let me just remind you again that the oracle server is on a unix box and the destination server where I need the file to be sent is a windows server. Will this solution work ?

Also, is UTL_FTP package a free package or paid one from oracle ?

thanks,

tech

0
 
tech_questionAuthor Commented:
BTW , I do not have ftp.login installed on our server, I got to get it installed by my DBA , I do not know if it is free or not !
0
 
RCorfmanCommented:
It is free and posted publicly as shown.  You need an FTP login on the windows box for it to work, you'll need to talk to your windows admin for that.
0
 
tech_questionAuthor Commented:
let me just remind you again that the oracle server is on a unix box and the destination server where I need the file to be sent is a windows server. Will this solution work ? I want to make it sure before I make a request to my DBA !

thanks a bunch for all your postings !
0
 
RCorfmanCommented:
As long as the system admins have ftp enabled on the windows box... sending the ftp out is not a problem it is whether windows is enabled to act as an ftp server... to find that out, you need to ask the administrators. FTP is MADE to work between systems... the fact of what server type you are coming from doesn't matter, it is if the destination supports ftp... windows is perfectly capable of doing this as long as the System administrators are not setting it us specifically to block this.
0
 
tech_questionAuthor Commented:
Great, thanks! I will try it out and let you guys know ! thanks again for all your help, really appreciate it.
0
 
actonwangCommented:
   
    second RCofrman,
     ftp is a tcp/ip protocol and platform independent. You should have no worry about this.

Acton
0
 
tech_questionAuthor Commented:

I have written this procedure to get what I wanted, but the program tends to execute but it takes time to run for ever and it does not create the file ! Where am I going wrong  here?  Baiscally this procedure will read data from the table and create file at the remote server and write it to it !



procedure  createandsendfile

(
c_rs out c_Cursor
)

is


       cursor sll_cur is

        SELECT
                * from employees  as OracleRow


sll_rec sll_cur%ROWTYPE ;


l_conn  UTL_TCP.connection;


 l_result            INTEGER;



Begin


begin

 l_conn := ftp.login('xxxxx', '21', 'xxx', 'xxxxx');
 

 l_datalength := length(sll_rec.OracleRow);
 
 
 l_conn := ftp.get_passive(l_conn);
 ftp.send_command(l_conn, 'STOR' || 'test.dat', TRUE);
 
 
 
     open sll_cur;

      
       LOOP
         fetch sll_cur into sll_rec;
         exit when sll_cur%NOTFOUND;

     
        
        l_result := UTL_TCP.write_text(l_conn, sll_rec.OracleRow, length(sll_rec.OracleRow));
        UTL_TCP.flush(l_conn);
        
       END LOOP;
      

  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
      
 


END createOracleFile;
0
 
actonwangCommented:
why don't you use the following :

declare
   l_conn UTL_TCP.connection;
begin
  -- use UTL_FILE create a test.txt under TEST_DIR directory

   -- transfer it
   l_conn := ftp.login('<ftp server>','21','username','password');
   ftp.binary(l_conn);
   ftp.put(l_conn,'TEST_DIR','test.txt','/Depuy/temp/test.txt');
   ftp.logout(l_conn);
   utl_tcp.close_all_connections;
end;
/
0
 
tech_questionAuthor Commented:
I want data from the database via a query and then this data to written to a file ! I do not know how the above scenario is achieving that ? Or am I wrong here ? Also I cannot create a file on the oracle server, I can only create a file on the remote windows server !

0
 
actonwangCommented:
ok.

>> ftp.send_command(l_conn, 'STOR' || 'test.dat', TRUE);
     shouldn't it be ftp.send_command(l_conn,'STOR '||'test.dat', TRUE);

you issue "STOR test.dat' not 'STORtest.dat', ftp might stuck there.
0
 
tech_questionAuthor Commented:
I get this error in my stored procedure. I corrected the stor command but still I get this error !

The following error has occurred:

ORA-20002: recv() failed, errno = 232
Connection reset by peer
ORA-06512: at "SYS.UTL_TCP", line 165
ORA-06512: at "SYS.UTL_TCP", line 474
ORA-06512: at "SYS.UTL_TCP", line 625
ORA-06512: at "BANK.FTP", line 118
ORA-06512: at "BANK.FTP", line 104
ORA-06512: at "BANK.BM_FILES1", line 81
ORA-06512: at line 7
0
 
tech_questionAuthor Commented:
l_conn := ftp.get_passive(l_conn); Once I comment this out the code runs fine, the problem is with this statement !
0
 
actonwangCommented:
you made run wrong use of passive connection.
0
 
actonwangCommented:
You mixed up passive mode connection and ftp session connection.
you'd declare two connections in your PL/SQL block


Below is a template you can base on:

declare
   l_conn UTL_TCP.connection; -- connection to ftp
   p_conn UTL_TCP.connection; -- passive connection
   l_buffer varchar2(300);
   l_result PLS_INTEGER;
   pfile varchar(100) := '/temp/test.txt'; -- file in remote ftp server
begin
   l_conn := ftp.login(xxxx);

   -- set up passive mode      
   p_conn := ftp.get_passive(l_conn);
   
   -- !! use ftp connection
   ftp.send_command(l_conn,'STOR '||pfile,TRUE);

   l_buffer := 'this is good!';  

   -- you can use loop here
   l_result := UTL_TCP.write_text(p_conn,l_buffer,length(l_buffer));
   UTL_TCP.flush(p_conn);

   -- close out passive mode
   UTL_TCP.close_connection(p_conn);
 
   ftp.logout(l_conn);
   utl_tcp.close_all_connections;

end;
/
0
 
actonwangCommented:
>>l_result := UTL_TCP.write_text(l_conn, sll_rec.OracleRow, length(sll_rec.OracleRow));

    I doubt this. You better off pull out data column by column then "write_text" into stream.
    As I posted in other question, Oracle WILL NOT cast %ROWTYPE into string type.
0
 
tech_questionAuthor Commented:
actonwang thanks that worked, but in the file only the last letter gets written . In the above example 'this is good!' only '!' gets written to the file ! Any ideas ! Is there a different function from write_text that I have to use ?
0
 
actonwangCommented:
Shouldn't be. all texts should be flushed into file specified.

try this:

>> l_result := UTL_TCP.write_text(p_conn,l_buffer,length(l_buffer));
      l_result := UTL_TCP.write_text(p_conn,'this is good!',length('this is good!'));
0
 
tech_questionAuthor Commented:
Sorry it did not work ! does it have to do with the port number specified in the get_passive connection ?
0
 
actonwangCommented:
>> Sorry it did not work ! does it have to do with the port number specified in the get_passive connection ?

    interesting. It works pefectly on my env.

    no. it has nothing do to with port number.
0
 
tech_questionAuthor Commented:
ha , should I check with my system admin ? may be it is to do with the permissions ?  

0
 
actonwangCommented:
after    l_conn := ftp.login(xxxx);

try add:
         ftp.ascii(l_conn);
0
 
tech_questionAuthor Commented:
sorry still does not do it ! only the last character is being added !
0
 
actonwangCommented:
try the following, binary mode:

declare
   l_conn UTL_TCP.connection; -- connection to ftp
   p_conn UTL_TCP.connection; -- passive connection
   l_buffer varchar2(300);
   l_result PLS_INTEGER;
   pfile varchar(100) := '/temp/test.txt'; -- file in remote ftp server
begin
   l_conn := ftp.login(xxxx);
   ftp.binary(l_conn);

   -- set up passive mode      
   p_conn := ftp.get_passive(l_conn);
   
   -- !! use ftp connection
   ftp.send_command(l_conn,'STOR '||pfile,TRUE);

   l_buffer := 'this is good!';  

   -- you can use loop here
     l_result := UTL_TCP.write_raw(p_conn,UTL_RAW.cast_to_raw(l_buffer),length(l_buffer));
   UTL_TCP.flush(p_conn);

   -- close out passive mode
   UTL_TCP.close_connection(p_conn);
 
   ftp.logout(l_conn);
   utl_tcp.close_all_connections;

end;
/
0
 
tech_questionAuthor Commented:
actonwag - thank you so much, everything works like a charm now - except that I have to get a line break between each rows added to the file. I have added chr(13) at the end of each row - what should I add in binary mode to get a carriage return at the end of each row !

0
 
actonwangCommented:
between line, tyr to add:

UTL_TCP.write_raw(p_conn,UTL_RAW.cast_to_raw(chr(13)),1);
0
 
tech_questionAuthor Commented:
I added that  but that did not do the trick. please be aware that I am writing to a windows server which runs off dos , would this change ?
0
 
actonwangCommented:
try this:

UTL_TCP.write_raw(p_conn,UTL_RAW.cast_to_raw(chr(13)||chr(10)),2);
0
 
tech_questionAuthor Commented:
that did the trick ! thank you so much ! Can I know why we need the passive mode and two connections to write to a remote server (file) !

0
 
actonwangCommented:
If you want to know all details, you'd pick up a TCP/IP book to get to ftp theory.

simply put, what passive mode  does is to open a secure connection from client side instead of server side. It is different from initial ftp connection.

The code here is quite complicated to most people. That is why I recommended you to use ftp.put(...).

Acton
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 17
  • 16
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now