10 Replies Latest reply: Feb 21, 2011 8:03 AM by BluShadow RSS

    UNIX Command from PL/SQL

    KannanB
      Hi,
      I need to execute unix ftp command inside a pl/sql stored procedure.

      How can I do this. Database version is greater than 9i.

      Thanks,
      Kannan.
        • 1. Re: UNIX Command from PL/SQL
          CGMS
          Hi Kannan,

          I hope below links will help your needs.

          http://www.oracle-base.com/articles/misc/FTPFromPLSQL.php

          http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp


          Suresh.
          • 2. Re: UNIX Command from PL/SQL
            BluShadow
            Chris Poole's XUTL_FTP package is a very flexible and powerful FTP package for PL/SQL and SQL...

            http://www.chrispoole.co.uk/apps/xutlftp.htm
            • 3. Re: UNIX Command from PL/SQL
              KannanB
              Im not going to use ftp command itself. Just wanted to show you that im going to transfer a file. Im not going to use native ftp command. We are going to use a third party tool for this.

              Now, how can I do this. Simply I want to execute a UNIX command from PL/SQL. I can not create a job using scheduler.

              Thanks,
              Kannan
              • 4. Re: UNIX Command from PL/SQL
                Saubhik
                Here are the ways.
                http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php
                • 5. Re: UNIX Command from PL/SQL
                  BluShadow
                  Kannan B wrote:
                  Im not going to use ftp command itself. Just wanted to show you that im going to transfer a file. Im not going to use native ftp command. We are going to use a third party tool for this.

                  Now, how can I do this. Simply I want to execute a UNIX command from PL/SQL. I can not create a job using scheduler.

                  Thanks,
                  Kannan
                  So, you want to do some FTP, but you don't want to control it from within PL/SQL, and you want to execute a command line, but you don't want to use the job scheduler. Is there any point in doing the command line from the database then? Why are you using Oracle at all?

                  If you're controlling from inside the database, you may as well use a PL/SQL based FTP package, rather than trying to FTP from a command line, because at least that way you'll have full control over the FTP and be able to control potential errors easier.
                  • 6. Re: UNIX Command from PL/SQL
                    635471
                    Kannan B wrote:
                    I can not create a job using scheduler.
                    Why not?
                    • 7. Re: UNIX Command from PL/SQL
                      KannanB
                      Guys,

                      You have to understand. Im going to create a file using UTL_FILE and I have to transfer the file. There is a third party tool which I have to use. This is the requirement. I can not go and ask the same questions to the client like you, ok.

                      Now tell me is there any way to execute a UNIX command from pl/sql stored procedure. Say for example, I want to execute the command PWD from pl/sql procedure , how will you do it.

                      Thanks,
                      Kannan.
                      • 8. Re: UNIX Command from PL/SQL
                        Saubhik
                        Take a look at the link posted in my earlier post.
                        • 9. Re: UNIX Command from PL/SQL
                          635471
                          Kannan B wrote:
                          Guys,

                          You have to understand. Im going to create a file using UTL_FILE and I have to transfer the file. There is a third party tool which I have to use. This is the requirement. I can not go and ask the same questions to the client like you, ok.

                          Now tell me is there any way to execute a UNIX command from pl/sql stored procedure. Say for example, I want to execute the command PWD from pl/sql procedure , how will you do it.
                          Here's what I do right now in the project i'm working on.

                          I generate a set of data files using UTL_FILE and I transfer them to a set of remote hosts by invoking scp. I call scp using a DBMS_Scheduler job, because I said to my client, "You know, the sane way of doing this is to use a DBMS_Scheduler job", and they agreed.

                          Hence my interest in why you are not allowed to use a DBMS_Scheduler job, which has specific fuinctionality to solve the problem you are dealing with.
                          • 10. Re: UNIX Command from PL/SQL
                            BluShadow
                            Likewise we have our own FTP package (similar to Chris') so we don't even have to write the files out using UTL_FILE to the database server, we can just write the data directly to the destination FTP server, and of course that is scheduled using DBMS_SCHEDULER. No need for command lines, no need for operating system scripts, all neat and tidy inside the database and no residual files (of potentially sensitive data) left lying around on the database servers o/s.