1 2 Previous Next 27 Replies Latest reply: Nov 22, 2012 8:10 AM by OraclePSP Go to original post RSS
      • 15. Re: Using FTP via PL/SQL
        Billy~Verreynne
        sb92075 wrote:
        >

        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).
        Disagree. Totally.

        The wheel is simple and round - and works very well inside PL/SQL as UTL_TCP exists for exactly that purpose. Writing a tcp socket client. For ftp, imap, smtp, pop3, http, etc.

        Ftp inside the database means that you do not need to step outside the database (writing or reading LOBs to the o/s file system). Means fewer moving parts. No external redundancies. No risky security holes that need to be punched between Oracle and the o/s to allow Oracle access to an external ftp client process. And full and complete control over the ftp conversation down to individual ftp commands.

        Writing a ftp client is easy, provided that you a) understand the basics of tcp socket programming, and b) the ftp protocol specifications. Do not mistake ignorance of these as a complexity that needs to be avoided.
        • 16. Re: Using FTP via PL/SQL
          sb92075
          Nothing is impossible for the person who doesn't have to actually do it.
          You & OP are free to implement what ever you desire for your DB.
          With free advice, you get what you paid for it.
          • 17. Re: Using FTP via PL/SQL
            Billy~Verreynne
            I have my own custom written FTP PL/SQL package - that I wrote back in 2009 for pushing XML (stored as CLOBs) to a 3rd party server's ftp server as files.

            So. Been There. Done That.
            • 18. Re: Using FTP via PL/SQL
              sb92075
              OP should Thank You for sharing.
              • 19. Re: Using FTP via PL/SQL
                Billy~Verreynne
                sb92075 wrote:
                OP should Thank You for sharing.
                That was not the intent - it was to point out to you, that you are too critical of what can be done inside the database. Like FTP.

                A RDBMS product like Oracle can be used 80's and 90's style - as just a database. Tables mostly.

                Or it can be used, as it often is today, as a data processing platform. This includes a full blown application layer. Which means tables and lots of PL/SQL code.

                In that respect, telling one that FTP needs to be done outside the database, is like telling a C/C++ or Java developer that FTP needs to be done externally. Ignoring the fact the standalone ftp client referred to is likely written in a language like C/C++ or even Java.

                PL/SQL is every bit as good as C/C++ or Java - within the context of a database application programming environment. Things like FTP is not too complex to be done in PL/SQL. PL/SQL is not an "inferior" language. Despite many treating it subconsciously as being a lessor language... applying what would be considered idiotic concepts and standards if applied in C/C++, Java and .Net.
                • 20. Re: Using FTP via PL/SQL
                  BluShadow
                  I agree with Billy, it's not a stupid idea to be handling FTP from within the database. Likewise I wrote us an FTP package back in 2006 which is now widely used throughout various applications and processes we have here, such as one process where we have to generate XML data to be supplied externally to an FTP site; and as Billy says, it keeps all the control and security of the connection details inside the database so no open scripts lying around for people to pry into or break. It provides pipeline functionality as well, so that you can literally query a remote files contents in SQL... something we use for monitoring application/error logs that are not stored in databases directly (the ones outside our control from 3rd party suppliers), without the need to actually move files anywhere or manually go to each server to check etc.

                  Our main driver for doing it was to keep the control and functionality of our processes all within the database, without the need to shell out to the operating system to run scripts or have o/s scripts scheduled to do things and then call database stuff. All nice and tidy in one place instead.

                  I'm not sure what happend to Chris Poole (who used to visit here occasionally himself), though he wrote a similar package which he made available for free on his website, but that site no longer seems to be up.

                  There's certainly more to using the Oracle database than just creating tables and storing data.
                  • 21. Re: Using FTP via PL/SQL
                    OraclePSP
                    Alberto Faenza wrote:
                    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
                    Hi,
                    so unix cant read/write db table but as i know can use sqlplus to do so.

                    Going back to initial request i have 2 alternatives:
                    - apex app that call dbms_scheduler that launch shell script then execute commands and log into text file
                    - sqlplus or sqlloader that eat* the log file and populate table
                    - apex repor the log table

                    or

                    resolve my loop and intercept the +550 $1: A file or directory in the path name does not exist.+ after mkdir via ftp because i cant use mkdir -p /path/path/subdir with ftp

                    I think is valuable working on it for a little more.

                    Thanks to all for the help.
                    • 22. Re: Using FTP via PL/SQL
                      OraclePSP
                      Robert Angel wrote:
                      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.
                      Hi robert,
                      it seems to be the same of Tim Hall pkg ftp.

                      Regards
                      Alex
                      • 23. Re: Using FTP via PL/SQL
                        OraclePSP
                        Billy  Verreynne  wrote:
                        sb92075 wrote:
                        >

                        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).
                        Disagree. Totally.

                        The wheel is simple and round - and works very well inside PL/SQL as UTL_TCP exists for exactly that purpose. Writing a tcp socket client. For ftp, imap, smtp, pop3, http, etc.

                        Ftp inside the database means that you do not need to step outside the database (writing or reading LOBs to the o/s file system). Means fewer moving parts. No external redundancies. No risky security holes that need to be punched between Oracle and the o/s to allow Oracle access to an external ftp client process. And full and complete control over the ftp conversation down to individual ftp commands.

                        Writing a ftp client is easy, provided that you a) understand the basics of tcp socket programming, and b) the ftp protocol specifications. Do not mistake ignorance of these as a complexity that needs to be avoided.
                        Agree. Totally.
                        • 24. Re: Using FTP via PL/SQL
                          BluShadow
                          Ok, I've just made a tweak to your original code and tested it ok...
                          DECLARE
                           l_conn       UTL_TCP.connection;
                           l_result     PLS_INTEGER;
                           v_ln         varchar2(255);
                           v_multi      boolean := false;
                           v_multi_code varchar2(3);
                           v_cnt        number := 0;
                          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 password');
                           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
                               v_ln := SUBSTR(UTL_TCP.get_line(l_conn),1,255);
                               v_cnt := v_cnt + 1;
                               if v_cnt = 1 then
                                 if substr(v_ln,4,1) = '-' then
                                   v_multi := true;
                                   v_multi_code := substr(v_ln,1,3);
                                 end if;
                               elsif v_cnt > 1 then
                                 if substr(v_ln,4,1) = '-' and substr(v_ln,1,3) = v_multi_code then
                                   v_multi := false;
                                 end if;
                               end if;
                               dbms_output.put_line('4 '||v_ln);
                               exit when not v_multi;
                             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;
                          the multi-line output part i've nabbed from my package, which is modularised so that the reading of a response is it's own seperate procedure and called for each command passed to FTP to verify the response against expected responses for that FTP command (hence then giving the ability to check for failures in the respones).

                          The FTP protocol giving the commands and expected responess are all detailed in RFC 959...

                          http://www.ietf.org/rfc/rfc959.txt
                          • 25. Re: Using FTP via PL/SQL
                            BluShadow
                            OraclePSP wrote:
                            Robert Angel wrote:
                            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.
                            Hi robert,
                            it seems to be the same of Tim Hall pkg ftp.

                            Regards
                            Alex
                            Though at the bottom there is a link relating to a readers comment that points to, what looks like, a full FTP package...

                            http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp
                            • 26. Re: Using FTP via PL/SQL
                              AlbertoFaenza
                              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
                              Hi,

                              the link that you have posted in your procedure has also some packages that can use and get the job easily done.
                              You may decide to write everything again but why can't you use something already prepared if it is working?
                              In your case, suppose that you want to check if a directory exists, in case the directory does not exist you want to create it and then transfer a file.
                              All you need to do is to handle the exception when you send the command cwd

                              Get the package FTP API (ftp.pks, ftp.pkb) from the same link you have posted and create package and package body.

                              Then you can check do something like this:
                              DECLARE
                                l_conn    UTL_TCP.connection;
                                l_list    ftp.t_string_table;
                                e_generic EXCEPTION;
                                    PRAGMA EXCEPTION_INIT (e_generic, -20000);
                                
                              BEGIN
                                l_conn := ftp.login('ftp.myserver.com', '21', 'alberto', '*********');
                              
                                -- check if directory 2012 exists on remote server
                                BEGIN
                                   ftp.send_command(l_conn, 'cwd /data/2012');
                                EXCEPTION
                                   -- When error 
                                   WHEN e_generic THEN
                                      -- If error 550 (Directory not exists)
                                      IF SUBSTR(SQLERRM,12,3)='550' THEN 
                                         -- Create directory on remote server
                                         -- If directory already exists command will fail
                                         ftp.mkdir(p_conn => l_conn,
                                                    p_dir => '/data/2012');
                                      ELSE
                                         RAISE_APPLICATION_ERROR(-20000, SQLERRM);
                                      END IF;
                                END;  
                              
                                -- check if directory 2012/Nov exists on remote server
                                BEGIN
                                   ftp.send_command(l_conn, 'cwd /data/2012/Dic');
                                EXCEPTION
                                   -- When error 
                                   WHEN e_generic THEN
                                      -- If error 550 (Directory not exists)
                                      IF SUBSTR(SQLERRM,12,3)='550' THEN 
                                         -- Create directory on remote server
                                         -- If directory already exists command will fail
                                         ftp.mkdir(p_conn => l_conn,
                                                    p_dir => '/data/2012/Nov');
                                      ELSE
                                         RAISE_APPLICATION_ERROR(-20000, SQLERRM);
                                      END IF;
                                END;  
                                
                                -- Send a binary file to a remote FTP server.
                                ftp.binary(p_conn => l_conn);
                                
                                ftp.put(p_conn      => l_conn,
                                        p_from_dir  => 'MY_DIR',  -- directory defined in Oracle with create directory ...
                                        p_from_file => 'test.jpg',
                                        p_to_file   => '/data/2012/Nov/test.jpg');
                              
                                ftp.logout(l_conn);
                                
                              END;
                              /
                              Regards.
                              Al
                              • 27. Re: Using FTP via PL/SQL
                                OraclePSP
                                Hi Alberto,

                                thaks for your suggestion, it was exatly what i was looking for.
                                I was already using the packages you talk about and i try to add procedures lcd, cwd e pwd like this
                                PROCEDURE pwd (p_conn  IN OUT NOCOPY  UTL_TCP.connection) AS
                                  l_conn  UTL_TCP.connection;
                                BEGIN
                                  l_conn := get_passive(p_conn);
                                  send_command(p_conn, 'pwd ', TRUE);
                                  logout(l_conn, FALSE);
                                END pwd;
                                but now, as in your example i am using this
                                PROCEDURE ckmkdir (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                                      p_dir   IN             VARCHAR2) as
                                  e_generic EXCEPTION;
                                      PRAGMA EXCEPTION_INIT (e_generic, -20000);
                                BEGIN
                                     ftp.send_command(p_conn, 'cwd ' || p_dir);
                                  EXCEPTION
                                     -- When error
                                     WHEN e_generic THEN
                                        -- If error 550 (Directory not exists)
                                        IF SUBSTR(SQLERRM,12,3)='550' THEN
                                           -- Create directory on remote server
                                           -- If directory already exists command will fail
                                           ftp.mkdir(p_conn => p_conn,
                                                      p_dir => p_dir);
                                        ELSE
                                           RAISE_APPLICATION_ERROR(-20000, SQLERRM);
                                        END IF;
                                  END ckmkdir;
                                and it is working very well.
                                In my apex application i can flag file to export and directly in the application make a file from the blob column in my table and export to remote via ftp in the correct path .

                                I have to refine the success or error message returned by the procedure but thanks to your help i can go ahead.

                                Regards,
                                Alex
                                1 2 Previous Next