1 2 Previous Next 27 Replies Latest reply: Nov 22, 2012 8:10 AM by OraclePSP RSS

    Using FTP via PL/SQL

    OraclePSP
      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
          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
            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
              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
                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
                  "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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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