This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Nov 22, 2012 6:10 AM by OraclePSP RSS

Using FTP via PL/SQL

OraclePSP Newbie
Currently Being Moderated
Hi all,
i have the need to implement an FTP procedure and accordingly to what i found here : http://www.oracle-base.com/articles/misc/ftp-from-plsql.php i try this code:
DECLARE
     l_conn  UTL_TCP.connection;
     l_result  PLS_INTEGER;
BEGIN
     l_conn := UTL_TCP.open_connection('remotehost', '21');
     dbms_output.put_line('1 '||UTL_TCP.get_line(l_conn, TRUE));

     l_result := UTL_TCP.write_line(l_conn, 'USER user');
     dbms_output.put_line('2 '||UTL_TCP.get_line(l_conn, TRUE));

     l_result := UTL_TCP.write_line(l_conn, 'PASS passord');
     dbms_output.put_line('3 '||UTL_TCP.get_line(l_conn, TRUE));

     l_result := UTL_TCP.write_line(l_conn, 'cwd /some/remote/path');
     begin
          loop
              dbms_output.put_line('4 '||UTL_TCP.get_line(l_conn, TRUE));
          end loop;
     exception
            when UTL_TCP.END_OF_INPUT then
              null;
    end;
     l_result := UTL_TCP.write_line(l_conn, 'bye');
     UTL_TCP.close_connection(l_conn);
END;     
in order to intercept all the returning messages ... but the loop never raise exception.

Any suggestion?
Thanks

Alex
  • 1. Re: Using FTP via PL/SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    Are you saying it's stuck in the loop and never completes? Or you're just not seeing any output?

    What is happening exactly?
  • 2. Re: Using FTP via PL/SQL
    sb92075 Guru
    Currently Being Moderated
    OraclePSP wrote:
    Hi all,
    i have the need to implement an FTP procedure and accordingly to what i found here : http://www.oracle-base.com/articles/misc/ftp-from-plsql.php i try this code:
    No, no such NEED exists.

    You suffer from NIH syndrome.

    http://en.wikipedia.org/wiki/Not_invented_here

    It will be faster & simpler to use existing OS ftp, than re-invent the wheel (poorly).
  • 3. Re: Using FTP via PL/SQL
    OraclePSP Newbie
    Currently Being Moderated
    Hi BluShadow,
    what its happening is that it stuck in the loop and when i stop the execution the result is
    1 220 remotehost FTP server (Version 4.2 Tue Jun 24 11:26:40 CDT 2008) ready.
    2 331 Password required for oracle.
    3 230-Last unsuccessful login: Fri Nov  9 11:32:01 CET 2012 on ...
    4 230-Last login: Wed Nov 14 15:29:40 CET 2012 on ftp from ...
    4 230 User oracle logged in.
    4 250 CWD command successful.
    In the future i would to extract the string and analize it to find error or success messages, for example in checking if a directory exists and then mkdir.

    Thanks.
  • 4. Re: Using FTP via PL/SQL
    OraclePSP Newbie
    Currently Being Moderated
    sb92075 wrote:
    OraclePSP wrote:
    Hi all,
    i have the need to implement an FTP procedure and accordingly to what i found here : http://www.oracle-base.com/articles/misc/ftp-from-plsql.php i try this code:
    No, no such NEED exists.

    You suffer from NIH syndrome.

    http://en.wikipedia.org/wiki/Not_invented_here

    It will be faster & simpler to use existing OS ftp, than re-invent the wheel (poorly).
    My NEED is not only a FTP client.

    I NEED to do ftp in a procedure, if possible.

    Thanks for the help ... oops ... no help from you.
  • 5. Re: Using FTP via PL/SQL
    Paul Horth Expert
    Currently Being Moderated
    "I NEED to do ftp in a procedure, if possible"

    What business function are you trying to satisfy?
  • 6. Re: Using FTP via PL/SQL
    sb92075 Guru
    Currently Being Moderated
    OraclePSP wrote:
    sb92075 wrote:
    OraclePSP wrote:
    Hi all,
    i have the need to implement an FTP procedure and accordingly to what i found here : http://www.oracle-base.com/articles/misc/ftp-from-plsql.php i try this code:
    No, no such NEED exists.

    You suffer from NIH syndrome.

    http://en.wikipedia.org/wiki/Not_invented_here

    It will be faster & simpler to use existing OS ftp, than re-invent the wheel (poorly).
    My NEED is not only a FTP client.

    I NEED to do ftp in a procedure, if possible.
    which metric at what value measures this "need".
    How or why does data care whether it gets moved via PL/SQL procedure or via OS utility?

    >
    Thanks for the help ... oops ... no help from you.
  • 7. Re: Using FTP via PL/SQL
    OraclePSP Newbie
    Currently Being Moderated
    Paul Horth wrote:
    "I NEED to do ftp in a procedure, if possible"

    What business function are you trying to satisfy?
    Hi Paul,

    in a higher vision the all process have to :
    - transfer files in directory and subdirectory (eg. /2012/Nov , /2012/Dec, /2013/Jan, etc)
    - "check and make" the path
    - the success or errors of the entire flow has to be reported to the user

    I know that all this can be done with os command, and i already does, but i have an apex app that manage the files i would like to export.

    Is easier to report os commands result in apex vs. reproducing the command in apex directly?

    Any suggestion is welcome and appreciated.
    Alex
  • 8. Re: Using FTP via PL/SQL
    Paul Horth Expert
    Currently Being Moderated
    OraclePSP wrote:
    Paul Horth wrote:
    "I NEED to do ftp in a procedure, if possible"

    What business function are you trying to satisfy?
    Hi Paul,

    in a higher vision the all process have to :
    - transfer files in directory and subdirectory (eg. /2012/Nov , /2012/Dec, /2013/Jan, etc)
    - "check and make" the path
    - the success or errors of the entire flow has to be reported to the user

    I know that all this can be done with os command, and i already does, but i have an apex app that manage the files i would like to export.

    Is easier to report os commands result in apex vs. reproducing the command in apex directly?

    Any suggestion is welcome and appreciated.
    Alex
    Without knowing more it is difficult to say what would be the best fit to your requirements but two
    approaches spring to mind.

    One is to use Unix scripts to do the ftp and write the results to a table whcih can then be interrogated by an Apex
    report as to what the result was. When you say the Apex app manages the files you want to export, do you mean
    the user picks the files to transfer? One way of doing that would be for the Unix script to read a table to see what needs
    ftping (the app would obviously set the required data up in the table).

    The second approach might be to write a Java procedure that sits in the DB and invokes the OS commands required. This procedure
    can be invoked like a normal PL/SQL procedure stored on the DB.

    Others may have other ideas.
  • 9. Re: Using FTP via PL/SQL
    odie_63 Guru
    Currently Being Moderated
    Paul Horth wrote:
    The second approach might be to write a Java procedure that sits in the DB and invokes the OS commands required. This procedure
    can be invoked like a normal PL/SQL procedure stored on the DB.
    Yes, or DBMS_SCHEDULER to execute an external shell script.
    If the script redirects the output to a file, it can then be read via an external table.
  • 10. Re: Using FTP via PL/SQL
    OraclePSP Newbie
    Currently Being Moderated
    Hi Paul,
    Paul Horth wrote:

    One is to use Unix scripts to do the ftp and write the results to a table whcih can then be interrogated by an Apex
    report as to what the result was. When you say the Apex app manages the files you want to export, do you mean
    the user picks the files to transfer? One way of doing that would be for the Unix script to read a table to see what needs
    ftping (the app would obviously set the required data up in the table).
    Unix can read/write db table? If yes i have to learn about it.
    With the apex app one user upload files, another user dowload files and works on it. Then upload the new version of file and export to remote.
    What i have done right now is :
    - transfer files from db table to oracle directory via utl_file
    - use dbms_scheduler to launch the ftp unix script

    I am looking for a better(easier) way to trace the process and your solutions are good enough to work on it because the ftp via plsql seems to be a not used way.

    Thanks again
    Alex
  • 11. Re: Using FTP via PL/SQL
    OraclePSP Newbie
    Currently Being Moderated
    Hi odie_63,
    odie_63 wrote:
    If the script redirects the output to a file, it can then be read via an external table.
    I create a log file for the unix script but i dont know how to use it into apex app.

    Regards,
    Alex
  • 12. Re: Using FTP via PL/SQL
    AlbertoFaenza Expert
    Currently Being Moderated
    OraclePSP wrote:
    Unix can read/write db table? If yes i have to learn about it.
    Well, Oracle client in Unix have sqlplus which you can use to execute PL/SQL or SQL statements and spool the output to a file.
    It's not a problem creating an ascii file that you can ftp with your shell script later on.
    To load the incoming files you use SQL*Loader sqlldr.

    Regards.
    Al

    Edited by: Alberto Faenza on Nov 14, 2012 5:28 PM
  • 13. Re: Using FTP via PL/SQL
    sb92075 Guru
    Currently Being Moderated
    OraclePSP wrote:
    Hi odie_63,
    odie_63 wrote:
    If the script redirects the output to a file, it can then be read via an external table.
    I create a log file for the unix script but i dont know how to use it into apex app.

    Regards,
    Alex
    When all else fails Read The Fine Manual

    http://www.oracle.com/pls/db112/search?remark=quick_search&word=external+table&partno=
  • 14. Re: Using FTP via PL/SQL
    Robert Angel Pro
    Currently Being Moderated
    Have you seen; -


    http://www.dba-oracle.com/t_invoke_ftp_from_pl_sql.htm

    BTW - I have had mixed results with FTP from pl/sql - sometimes it works without issues, on certain OS it doesn't... with certain security they recognise your pl/sql as an attack....

    My advice, steer clear and use alternatives if possible, as the suggestions say, unix scripts that post results in sql tables on a timed reference can save you a lot of pain.



    regards,

    Robert.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points