Oracle

Oracle UTL_FTP

Hace unos me surgió la necesidad de realizar el paso de algunos archivos planos entre servidores, el problema es que este paso se va a realizar constantemente, entonces tuve que pensar en una solución que fuera fácil, aparte esos archivos no son mas que información que se procesa e ingresa a una tabla de base de datos.

De entrada la solución que se proponía era realizar un shell script para realizar el paso de archivos vía ftp de un servidor a otro, soluciona el problema pero no estaba muy de acuerdo, primero tener que llamar a un shell para después conectarte a Oracle y llamar a un procedimiento, le quita el control a Oracle sobre el manejo de esos archivos, otra era usar directamente UTL_FILE, pero este paquete sirve para el manejo de archivos en el mismo servidor de base de datos, que yo sepa, con utl_file no se puede manejar archivos de otras maquinas.

Tiempo atrás ya había leído de la existencia de un paquete para el manejo de conexiones tcp, el paquete se llama UTL_TCP, suponía entonces que ya debería de existir algo relacionado con el manejo de archivos entre servidores, la opción lógica era buscar el paquete UTL_FTP pero sorpresa, este no existe, bueno y ¿que dice Google al respecto? le di buscar utl_ftp y me regreso varias paginas, me encontré primero con un script guardado en sourceforce llamado “plsqlftp“, pero no me convenció, su desarrollo se ve casi abandonado y no encontré un solo ejemplo de cómo se usa, acabe descartándolo.

Rascándole un poquito mas a los resultados me tope con el que finalmente implemente, le llamaron “FTP Interfase“, me quedo perfecto para lo que necesitaba, se ve bastante robusto y completo, yo solo use tres funciones, “VERIFY_SERVER”, “PUT” y “GET” pero tiene muchas mas, igual más adelante las pueda necesitar, aquí les dejo el procedimiento que generé para realizar los movimientos de archivos:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
  --
  -- MOVE_FILE
  --
  -- move file "p_filename" with ftp interfase, use put or get "p_type_move
  -- "p_type_move" = "PUT" => from "p_localpath" to "p_remotepath"
  -- "p_type_move" = "GET" => from "p_remotepath" to "p_localpath"
  -- use ftp server "p_hostname" with usr "p_username" and pwd "p_password"
  --   
  PROCEDURE move_file    (
     p_type_move           IN      VARCHAR2,
     p_localpath           IN      VARCHAR2,
     p_remotepath          IN      VARCHAR2,
     p_filename            IN      VARCHAR2, 
     p_username            IN      VARCHAR2,
     p_password            IN      VARCHAR2,
     p_hostname            IN      VARCHAR2,
     p_error               OUT     VARCHAR2) IS
 
     v_exception    EXCEPTION;
     p_status       VARCHAR2(32000);
     p_bytes_trans  NUMBER;
     p_trans_start  DATE;
     p_trans_end    DATE;
     dummy          BOOLEAN;
  BEGIN
 
     IF (p_type_move != 'PUT') AND
        (p_type_move != 'GET') THEN
        p_error := 'ERROR: value for p_type_move not supported.';
        RAISE v_exception;
     END IF;
 
     dummy := ftp_interface.verify_server ( 
                    p_remotepath           => p_remotepath,
                    p_username             => p_username,
                    p_password             => p_password,
                    p_hostname             => p_hostname,
                    v_status               => p_status,
                    v_error_message        => p_error,
                    p_port                 => 21,
                    p_filetype             => 'ASCII',
                    p_mainframe_connection => FALSE );
 
     IF p_status != 'SUCCESS' THEN
        RAISE v_exception;
     END IF;
 
	 IF p_type_move = 'PUT' THEN
 
	     dummy := ftp_interface.put (
                    p_localpath            => p_localpath,
                    p_filename             => p_filename,
                    p_remotepath           => p_remotepath,
                    p_username             => p_username,
                    p_password             => p_password,
                    p_hostname             => p_hostname,
                    v_status               => p_status,
                    v_error_message        => p_error,
                    n_bytes_transmitted    => p_bytes_trans,
                    d_trans_start          => p_trans_start,
                    d_trans_end            => p_trans_end,
                    p_port                 => 21,
                    p_filetype             => 'ASCII',
                    p_mainframe_ftp        => FALSE,
                    p_mainframe_cmd        => '' );
 
         IF p_status != 'SUCCESS' THEN
            RAISE v_exception;
         END IF;
 
     ELSIF p_type_move = 'GET' THEN
 
		 dummy := ftp_interface.get (
                    p_localpath            => p_localpath,
                    p_filename             => p_filename,
                    p_remotepath           => p_remotepath,
                    p_username             => p_username,
                    p_password             => p_password,
                    p_hostname             => p_hostname,
                    v_status               => p_status,
                    v_error_message        => p_error,
                    n_bytes_transmitted    => p_bytes_trans,
                    d_trans_start          => p_trans_start,
                    d_trans_end            => p_trans_end,
                    p_port                 => 21,
                    p_filetype             => 'ASCII',
                    p_mainframe_ftp        => FALSE,
                    p_mainframe_cmd        => '' );
 
         IF p_status != 'SUCCESS' THEN
            RAISE v_exception;
         END IF;
 
     END IF;
 
     p_error := 'OK';
 
  EXCEPTION 
     WHEN v_exception THEN
         NULL;
     WHEN OTHERS THEN
         p_error := 'ERROR: ' || SQLERRM;
  END move_file;

La llamada al procedimiento se realiza de manera muy fácil:

1
2
3
4
5
6
7
8
move_file('GET',
          '/datos',
          '/home',
          'test.txt',
          'usuario',
          'password',
          'ip_del_host',
          v_error);

49 thoughts on “Oracle UTL_FTP

  1. connect as sysdba and run this script:
    BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => ‘network_services.xml’,
    description => ‘FTP ACL’,
    principal => ‘USERNAME’,
    is_grant => true,
    privilege => ‘connect’);
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => ‘network_services.xml’,
    principal => ‘USERNAME’,
    is_grant => true,
    privilege => ‘resolve’);
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => ‘network_services.xml’,
    host => ‘*’);
    COMMIT;
    END;

    don’t forget replace ‘USERNAME’ with name of the user you want to grant access.

  2. EStoy probando el pakete desde Oracle 11g pero no se muy buien que configuracion se deba poner para los acls..
    me sale el siguiente mensaje:

    hum_ftp_interface.FTP_FILES_STAGE :: -29260 – ORA-29260: network error: not connected
    ORA-24247: network access denied by access control list (ACL) :: PERFORMING CWD COMMAND FOR /a/b/trace/udump/

    pudieras ayudarme?

  3. No encuentro la forma de instalar los archivos, ya los descargue y lei el post pero no encuentro laas linesas en la que me indiquen como instalar los archivos, un apoyo por favor

  4. Hola, Tengo que tomar archivos de una maquina, esos archivos se encuentran en un directorio en Windows C:\archivos_web\archivo.txt
    Requiero leer esos archivos desde la base de datos ORACLE y guardar su informacion en tablas. Lo que estaba pensando es pasarlos al FTP de ORACLE y de ahí realizar un procedimiento que lea dichos archivos.
    Te agradeceria muchisimo tu ayuda.
    Susana

  5. Yo estoy probando tu codigo Donzote.
    Baje la ultima version del ftp_interface desde http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp que es la 3.1.3 y para usar esta version hay un cambio en tu código, toca cambiar el ftp_interface por hum_ftp_interface.

    Yo instale el codigo de ftp_interface con un usuario dba diferente a sys o system.

    Cuando trato de ejecutar el ejemplo no puede ejecutar el select que busca en dba_directories.

    ¿Tengo que instalar el codigo de ftp_interface con sys?

    1. el ftp_interfase por hum_ftp_interfase si no mal recuerdo fue un cambio que yo realice pero a fin de cuentas es lo mismo
      eso del select a dba_directories te lo debo, no recuerdo esa consulta, lo del usuario segun yo no habia problema, incluso yo lo instale con un usuario x, no necesita ser obligatorio en sys o system, de hecho NUNCA he recomendado crear cosas bajo sys o system, saludos

  6. Looks great. Looks a lot like the one that I pieced together ;o) . The mainframe support parm was the give-away for me… but what is really great is that people are getting use out of the code. Proving that you really can have a production level FTP process.

    In my original code, I left a lot of credits at the top showing where I gathered the information from. Lot of hardwork from a lot of people and sources went into the final solution.

    Final..well maybe not final… always evolving.

    BarryC
    http://www.myoracleportal.net

    1. que bueno que nos visites por aca barry, aunque no me queda claro si tu eres el creador de ftp_interfase, siendo asi gracias a ti por compartirnos tu trabajo, como lo comente en el post me fue de gran ayuda, esperemos que oracle en proximas versiones incluya algo asi y tambien esperemos que te tomen en cuenta a la hora que lo hagan, un saludo desde mexico.

      1. I am in fact the author of the FTP_INTERFACE utility and owner of the MyOraclePortal site. I recently upgraded and hopefully made my findings and offerings easier to access. The FTP utility is currently at version 3.1.3 and the Email utility (if you have not used it you should try it) is at version 3.0.0.

        I have a brief mention in the O’Reilly Pl/SQL Programming (4th Edition) authored by Steven Feurenstein and Bill Pribyl with respect to the FTP solution.

        Some postings here talked about Secure FTP using PL/SQL. That probably is a highly doubtful objective due to the encryption layers. I have been thinking more of a java solution to handle the SFTP and then just have the FTP_INTERFACE api call that java solution instead. That way it would be transparent to the requestor other than them or it stating they need a secure FTP and here are the keys,etc.

        Anyways… good luck and thank you.

        Barry C.
        http://www.myoracleportal.com

  7. nunca he intentado realizar una conexion sftp a travez de este paquete, igual seria cuestion de probar y tal vez modificar algunas cosas, tal vez la mas facil es crearte un shell o un bat que realice la conexion y dejarle solo a oracle la creacion del archivo, saludos

  8. Perdón me he expresado mal. Programo en pl/sql, Se utilizar el UTL_FILE desde el server o TEXT_IO desde el cliente. Perfecto hasta ahí. Ahora… el tema es que el txt que se generaría en el Server de la base, tiene que ser inmediatamente movido a un SFTP que no es lo mismo que un FTP.
    Mi duda es si alguien sabe de algún script que resuelva esto, ya que oracle no maneja certificaciones de seguridad en las interfaces.

    Espero se me entienda
    Saludos

  9. las dos cosas que intentas hacer (utl_file para la creacion del txt y utl_ftp para el paso entre servidores ftp) se realizan con PL/SQL, lo mejor sera que te busques un manualito en la pagina de oracle sobre el lenguaje para que te des una idea de como funciona, saludos

  10. Perfecto tengo todo. Pero el problema que tengo es que tengo que generar un .txt en el Server donde esta la base Oracle y luego llamar a este paquete para que lo copie en otro server por medio de FTPS. Esto complica las cosas.
    Por lo que he visto se usa java para la conexión.
    Alguien puede orientarme un poco, como hacerlo?

    Aclaro que aun no lo he intentado pero en la brevedad tendré que hacerlo y lo veo complicado.

    Saludos Y muchas gracias por este excelente posteo

  11. esta raro que no funcione, solo que lo hayan cambiado, ya tiene mas de un año que escribi este post, los mejor es que te bajes su ultima version y sobre esa trabajes, igual lo mio se tenga que modificar, saludos

  12. Hola, estoy utilizando el paquete utl_smtp para enviar correos y he visto que se define una variable como utl_tcp.crlf, pero al compilar me da error de restricción de implementación, no se puede acceder a una varible o cursor de paquete remoto. Estoy compialndo en local. Alguien me puede ayudar?
    MUchas gracias.

  13. mmm es que directamente esta medio dificil, supongo que al decir “6i” estas trabajando en cliente/servidor por lo que se complica mas, la mas facil que se me ocurre, es que el archivo del servidor lo copies a una tabla de base de datos con UTL_FILE (suponiendo que ese archivo este en el servidor de la base de datos) ya con la info en la base, con text_io de forms generes el archivo localmente, espero haberte ayudado, saludos

  14. Hola Favor tu ayuda, tengo una pantalla en Forms 61,que debe ir ha ver/ buscar un archivo de texto en una ruta del servidor y ese archivo copiarlo localmente

    Favor tu ayuda urgente
    Gracias

  15. al estar usando un protocolo como FTP es independiente del sistema operativo, tu te conectas a un servicio remoto en el puerto 21 normalmente y si ese servicio esta sobre un windows, linux, unix, solaris no deberias de tener problemas

  16. los directorios son fisicos, del servidor remoto y local, los usuarios son del servidor FTP remoto, el puerto depende del servidor FTP que puerto esta escuchando, saludos

  17. hola. estupendo tu artículo. Qué pena que no haya más gente como tú.
    Estoy desarrollando una aplicación que requiere que copie un fichero plano de un servidor a otro y tengo unas dudas con respecto a tu artículo.
    He montado el paquete de oracleportal en mi servidor desde el que disparo el procedimiento y testeo el move_file con GET, pero exactamente qué directorios hay que poner? los fisicos o los definidos en BD?. El usuario y password son de la bd de destino o del equipo? Puedes usar otro puerto que no sea el 21? Espero no ser muy pesada y reitero mis felicitaciones. un saludo

  18. hola. estupendo tu artículo. Qué pena que no haya más gente como tú.
    Estoy desarrollando una aplicación que requiere que copie un fichero plano de un servidor a otro y tengo unas dudas con respecto a tu artículo.
    He montado el paquete de oracleportal en mi servidor desde el que disparo el procedimiento y testeo el move_file con GET, pero exactamente qué directorios hay que poner? los fisicos o los definidos en BD?. El usuario y password son de la bd de destino o del equipo? Puedes usar otro puerto que no sea el 21? Espero no ser muy pesada y reitero mis felicitaciones. un saludo

  19. sobre el error al copiar, ya investigaste los demas parametros del procedure “fcopy” de utl_file? se me hace que le tienes que especificar como realizar la copia si ascii o binario (algo como en un cliente ftp)
    aparte de esto me queda la duda si realmente es necesario el abrir y cerrar el archivo (fopen y fclose) si solo quieres copiar el archivo, tal vez por ahi valla

  20. “el paquete del blog” yo inserte el link para la descarga del paquete de una pagina externa, si marca error deberias de investigar mejor el error, a mi me funciono bien como esta

  21. He probado algunas opciones de copiado de archivos en oracle pero al quer abrir el archivo marca error …

    Tienes Idea por que ?

    Los txt los pasa como si nada, con los binarios es que copia pero corruptos los archivos…

    PROCEDURE UTLTESTCOPY
    AS
    f1 UTL_FILE.file_type;
    BEGIN
    f1 := UTL_FILE.fopen (‘GESTORDOC_DIR_R’, ‘busq_index.gif’, ‘r’);
    UTL_FILE.fcopy (src_location => ‘GESTORDOC_DIR_R’,
    src_filename => ‘busq_index.gif’,
    dest_location => ‘GESTORDOC_DIR’,
    dest_filename => ‘busq_index.gif’
    );
    UTL_FILE.fclose (f1);
    END;

  22. Subi los paquetes del blog y en marca error el procedimiento: ftp_files_stage … tienes otra version o podrias publicar el paquete ftp_interfase que tienes tu para probar move_fle.

    Saludos

  23. pues tal y como lo harias para otras cosas en windows “e:\directorio\” claro que al ser un paquete en la base de datos este directorio debe de ser del servidor base de datos

    como se obtiene?, lee de nuevo el post, saludos

  24. con utl_file no tienes acceso a ninguna maquina fuera de donde esta corriendo la base de datos, para usar utl_file forzosamente necesitas los privilegios sobre un directorio de ese servidor

  25. Hola, necesito crear un archivo y enviarlo por mail pero no tengo privilegios en el servidor, ni un directorio donde depositarlo, por lo que el utl_file me provoca problemas para encontrar donde dejar el archivo, hay alguna manera de que le diga a Oracle que lo deje en mi maquina y despues lo tome y envie???

  26. entiendo el problema pero estas viendo la solucion por otro lado, con utl_file no puedes definir que celda de excel va a tener que dato, la solucion es generar un string con los valores separados por algo (comas, pipes, tabulador, etc) y esa cadena la guardas en el archivo linea por linea, al abrir eso en un excel te abre un wizard con opciones de como importar y listo

  27. hola muy bueno el articulo, mencionaste el utl_file y yo estoy tratando de armar un excel a partir de una consulta en oracle ej:

    select * from tabla
    where nombre = ‘jose’;

    esta consulta da varios registros como resultados y el excel tendria que quedar asi:

    Nombre Apellido Direccion
    Jose perez nom 258
    Jose fernandez esquin 258

    Tipo : contrato
    Actualizado el pago.

    Todo esto separado en las diferentes columnas del excel. Estuve intentando armar con utl_file pero no con buenos resultados no se si era porq yo lo armaba mal…agradeceria cualquier ayuda..
    Desde ya gracias!!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *