Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calling an FTP from an Oracle PL/SQL stored procedure

Status
Not open for further replies.
swhitt

swhitt

MIS
Aug 27, 2003
28
US
Can anyone tell me if it is possible to call an FTP from within an Oracle stored procedure. If so, how would this be done. Thank you in advance for any suggestions.
 
Sort by date Sort by votes
You may create simple wrapper library to call ftp as OS command and invoke it as an external procedure. Alternatively you may call an appropriate java class ( sun.net.ftp.FtpClient )

Regards, Dima
 
Upvote 0 Downvote
Hi,
Here is a Package which you may use to send FTP through Oracle.
I am pasting only the Package body.You will have to write the Package Specs yourself.
This is code shared by one Freeman Donald in one of the Forums I participate.

HTH
Regards
Himanshu

Code: 
CREATE OR REPLACE PACKAGE BODY ftp AS
--
--------------------------------------------------------------------------
-- Name         : ftp.pkb
/************************************************************************************************************/
-- Script:         FTP_Package
/************************************************************************************************************/

g_reply      t_string_table := t_string_table();
g_binary     BOOLEAN := TRUE;
g_debug      BOOLEAN := TRUE;

FUNCTION get_passive (p_conn  IN  UTL_TCP.connection)
  RETURN UTL_TCP.connection;
PROCEDURE get_reply (p_conn  IN  UTL_TCP.connection);
PROCEDURE debug (p_text  IN  VARCHAR2);

--
--------------------------------------------------------------------------
FUNCTION login (p_host  IN  VARCHAR2,
                p_port  IN  VARCHAR2,
                p_user  IN  VARCHAR2,
                p_pass  IN  VARCHAR2)
  RETURN UTL_TCP.connection IS
--
--------------------------------------------------------------------------
  l_conn  UTL_TCP.connection;
BEGIN
  g_reply.delete;

  l_conn := UTL_TCP.open_connection(p_host, p_port);
  get_reply (l_conn);
  send_command(l_conn, 'USER ' || p_user);
  send_command(l_conn, 'PASS ' || p_pass);
  RETURN l_conn;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
FUNCTION get_passive (p_conn  IN  UTL_TCP.connection)
  RETURN UTL_TCP.connection IS
--
--------------------------------------------------------------------------
  l_conn    UTL_TCP.connection;
  l_reply   VARCHAR2(32767);
  l_host    VARCHAR(100);
  l_port1   NUMBER(10);
  l_port2   NUMBER(10);
BEGIN
  send_command(p_conn, 'PASV');
  l_reply := g_reply(g_reply.last);

  l_reply := REPLACE(SUBSTR(l_reply, INSTR(l_reply, '(') + 1,
(INSTR(l_reply, ')')) - (INSTR(l_reply, '('))-1), ',', '.');
  l_host  := SUBSTR(l_reply, 1, INSTR(l_reply, '.', 1, 4)-1);

  l_port1 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 4)+1,
(INSTR(l_reply, '.', 1, 5)-1) - (INSTR(l_reply, '.', 1, 4))));
  l_port2 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 5)+1));

  l_conn := utl_tcp.open_connection(l_host, 256 * l_port1 + l_port2);
  return l_conn;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE logout(p_conn   IN  UTL_TCP.connection,
                 p_reply  IN  BOOLEAN := TRUE) AS
--
--------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'QUIT', p_reply);
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE send_command (p_conn     IN  UTL_TCP.connection,
                        p_command  IN  VARCHAR2,
                        p_reply    IN  BOOLEAN := TRUE) IS
--
--------------------------------------------------------------------------
  l_result  PLS_INTEGER;
  l_conn    UTL_TCP.connection  := p_conn;
BEGIN
  l_result := UTL_TCP.write_line(l_conn, p_command);

  IF p_reply THEN
    get_reply(l_conn);
  END IF;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE get_reply (p_conn  IN  UTL_TCP.connection) IS
--
--------------------------------------------------------------------------
  l_conn  UTL_TCP.connection  := p_conn;
BEGIN
  LOOP
    g_reply.extend;
    g_reply(g_reply.last) := UTL_TCP.get_line(l_conn, TRUE);
    debug(g_reply(g_reply.last));
    IF SUBSTR(g_reply(g_reply.last), 1, 1) = '5' THEN
      RAISE_APPLICATION_ERROR(-20000, g_reply(g_reply.last));
    ELSIF SUBSTR(g_reply(g_reply.last), 4, 1) = ' ' THEN
      EXIT;
    END IF;
  END LOOP;
EXCEPTION
  WHEN UTL_TCP.END_OF_INPUT THEN
    NULL;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
FUNCTION get_local_ascii_data (p_dir   IN  VARCHAR2,
                               p_file  IN  VARCHAR2)
  RETURN CLOB IS
--
--------------------------------------------------------------------------
  l_bfile   BFILE;
  l_data    CLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);

  l_bfile := BFILENAME(p_dir, p_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  RETURN l_data;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
FUNCTION get_local_binary_data (p_dir   IN  VARCHAR2,
                                p_file  IN  VARCHAR2)
  RETURN BLOB IS
--
--------------------------------------------------------------------------
  l_bfile   BFILE;
  l_data    BLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);

  l_bfile := BFILENAME(p_dir, p_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  RETURN l_data;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
FUNCTION get_remote_ascii_data (p_conn  IN  UTL_TCP.connection,
                                p_file  IN  VARCHAR2)
  RETURN CLOB IS
--
--------------------------------------------------------------------------
  l_conn    UTL_TCP.connection;
  l_amount  PLS_INTEGER;
  l_buffer  VARCHAR2(32767);
  l_data    CLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);

  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RETR ' || p_file, TRUE);
  logout(l_conn, FALSE);

  BEGIN
    LOOP
      l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767);
      DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
    WHEN OTHERS THEN
      NULL;
  END;
  UTL_TCP.close_connection(l_conn);

  RETURN l_data;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
FUNCTION get_remote_binary_data (p_conn  IN  UTL_TCP.connection,
                                 p_file  IN  VARCHAR2)
  RETURN BLOB IS
--
--------------------------------------------------------------------------
  l_conn    UTL_TCP.connection;
  l_amount  PLS_INTEGER;
  l_buffer  RAW(32767);
  l_data    BLOB;
BEGIN
  DBMS_LOB.createtemporary (lob_loc => l_data,
                            cache   => TRUE,
                            dur     => DBMS_LOB.call);

  l_conn := get_passive(p_conn);
  send_command(p_conn, 'RETR ' || p_file, TRUE);
  logout(l_conn, FALSE);

  BEGIN
    LOOP
      l_amount := UTL_TCP.read_raw (l_conn, l_buffer, 32767);
      DBMS_LOB.writeappend(l_data, l_amount, l_buffer);
    END LOOP;
  EXCEPTION
    WHEN UTL_TCP.END_OF_INPUT THEN
      NULL;
    WHEN OTHERS THEN
      NULL;
  END;
  UTL_TCP.close_connection(l_conn);

  RETURN l_data;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE put_local_ascii_data (p_data  IN  CLOB,
                                p_dir   IN  VARCHAR2,
                                p_file  IN  VARCHAR2) IS
--
--------------------------------------------------------------------------
  l_out_file  UTL_FILE.file_type;
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_clob_len  INTEGER;
BEGIN
  l_clob_len := DBMS_LOB.getlength(p_data);

  l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

  WHILE l_pos < l_clob_len LOOP
    DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_out_file, l_buffer);
    UTL_FILE.fflush(l_out_file);
    l_pos := l_pos + l_amount;
  END LOOP;

  UTL_FILE.fclose(l_out_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_out_file) THEN
      UTL_FILE.fclose(l_out_file);
    END IF;
    RAISE;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE put_local_binary_data (p_data  IN  BLOB,
                                 p_dir   IN  VARCHAR2,
                                 p_file  IN  VARCHAR2) IS
--
--------------------------------------------------------------------------
  l_out_file  UTL_FILE.file_type;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  l_blob_len := DBMS_LOB.getlength(p_data);

  l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_out_file, l_buffer, TRUE);
    UTL_FILE.fflush(l_out_file);
    l_pos := l_pos + l_amount;
  END LOOP;

  UTL_FILE.fclose(l_out_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_out_file) THEN
      UTL_FILE.fclose(l_out_file);
    END IF;
    RAISE;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE put_remote_ascii_data (p_conn  IN  UTL_TCP.connection,
                                 p_file  IN  VARCHAR2,
                                 p_data  IN  CLOB) IS
--
--------------------------------------------------------------------------
  l_conn      UTL_TCP.connection;
  l_result    PLS_INTEGER;
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_clob_len  INTEGER;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'STOR ' || p_file, TRUE);

  l_clob_len := DBMS_LOB.getlength(p_data);

  WHILE l_pos < l_clob_len LOOP
    DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
    l_result := UTL_TCP.write_text(l_conn, l_buffer, l_amount);
    UTL_TCP.flush(l_conn);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_TCP.close_connection(l_conn);
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE put_remote_binary_data (p_conn  IN  UTL_TCP.connection,
                                  p_file  IN  VARCHAR2,
                                  p_data  IN  BLOB) IS
--
--------------------------------------------------------------------------
  l_conn      UTL_TCP.connection;
  l_result    PLS_INTEGER;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  l_conn := get_passive(p_conn);
  send_command(p_conn, 'STOR ' || p_file, TRUE);

  l_blob_len := DBMS_LOB.getlength(p_data);

  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
    l_result := UTL_TCP.write_raw(l_conn, l_buffer, l_amount);
    UTL_TCP.flush(l_conn);
    l_pos := l_pos + l_amount;
  END LOOP;
  UTL_TCP.close_connection(l_conn);
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE get (p_conn       IN  UTL_TCP.connection,
               p_from_file  IN  VARCHAR2,
               p_to_dir     IN  VARCHAR2,
               p_to_file    IN  VARCHAR2) AS
--
--------------------------------------------------------------------------
BEGIN
  IF g_binary THEN
    put_local_binary_data(p_data  => get_remote_binary_data (p_conn,
p_from_file),
                          p_dir   => p_to_dir,
                          p_file  => p_to_file);
  ELSE
    put_local_ascii_data(p_data  => get_remote_ascii_data (p_conn,
p_from_file),
                         p_dir   => p_to_dir,
                         p_file  => p_to_file);
  END IF;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE put (p_conn       IN  UTL_TCP.connection,
               p_from_dir   IN  VARCHAR2,
               p_from_file  IN  VARCHAR2,
               p_to_file    IN  VARCHAR2) AS
--
--------------------------------------------------------------------------
BEGIN
  IF g_binary THEN
    put_remote_binary_data(p_conn => p_conn,
                           p_file => p_to_file,
                           p_data => get_local_binary_data(p_from_dir,
p_from_file));
  ELSE
    put_remote_ascii_data(p_conn => p_conn,
                          p_file => p_to_file,
                          p_data => get_local_ascii_data(p_from_dir,
p_from_file));
  END IF;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE help (p_conn  IN  UTL_TCP.connection) AS
--
--------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'HELP', TRUE);
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE ascii (p_conn  IN  UTL_TCP.connection) AS
--
--------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'TYPE A', TRUE);
  g_binary := FALSE;
END;
--
--------------------------------------------------------------------------



--
--------------------------------------------------------------------------
PROCEDURE binary (p_conn  IN  UTL_TCP.connection) AS
--
--------------------------------------------------------------------------
BEGIN
  send_command(p_conn, 'TYPE I', TRUE);
  g_binary := TRUE;
END;
--
--------------------------------------------------------------------------


--
--------------------------------------------------------------------------
PROCEDURE debug (p_text  IN  VARCHAR2) IS
--
--------------------------------------------------------------------------
BEGIN
  IF g_debug THEN
    DBMS_OUTPUT.put_line(SUBSTR(p_text, 1, 255));
  END IF;
END;
--
--------------------------------------------------------------------------

END ftp;
/
SHOW ERRORS
 
Upvote 0 Downvote
Thank you both. I am going to use the PL/SQL package.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

annie1975
  • Locked
  • Question
sql script in an cron job script failing
Replies
3
Views
248
carp
carp
Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
717
Andrzejek
Andrzejek
TheLazyPig
  • Locked
  • Question
Oracle Data Validation 1
Replies
4
Views
219
TheLazyPig
TheLazyPig
KeyserSoze
  • Locked
  • Question
TOAD for Oracle - Icons
Replies
1
Views
246
Andrzejek
Andrzejek
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp

Part and Inventory Search

Sponsor

Back
Top