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); |
Cuando trato de transferir archivos PDF o DOC se corrompen. Ya intenté cambiando el tipo de transferencia y no funciona. Que se puede hacer??
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.
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?
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
estimado esto sirve para CUALQUIER SERVIDOR o solo el servidor de base de datos en los dirs de UTL_FILE?
gracias y saludos
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
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?
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
No accedes a dba_directories porque no tienes los grants necesarios.
Consulta con el DBA
Hola, yo tuve el mismo problema y lo solucione con lo que comenta donzote. con grant select on dba_directories to squema
Saludos
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
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.
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
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
Mi complicación transcurre unicamente en la conexión con el SFTP nada mas.
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
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
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
pues igual fue un problema temporal por que el dia de hoy que intento entrar si puedo, por etica lo mejor es dar el link de la pagina donde se puede descargar, saludos
buenos días,
no esta operativa la pagina de donde se puede descargar ftp_interface, podrias mandarlo?
muchas gracias, un saludo
si leiste el post, seguro sabras de donde se descarga, saludos
Estimado: no encuentro la libreria ftp_interface,
donde puedo obtenerla!
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
Donzonte:
Subete un Zip con los fuentes que probastes, los del articulo no hace lo que dice que hace.
Saludos
DonZote
Rolate los fuentes que probastes tu, los del articulos no hacen lo que dice que hace.
Subete un ZIP con los fuentes.
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.
lo mejor es que vuelvas a leer el post, saludos
no me funciona necesito ftp_interface.verify_server
ftp_interface.put
ftp_interface.get
gracias
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
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
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
Muy bueno el articulo.
Una pregunta el servidor local o remoto puede ser Windows / Linux? o viceversa?.
Gracias 🙂
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
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
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
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
“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
marca error el pakete del blog .. cual usaste proporcionalo
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;
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
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
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
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???
gracias, que bueno que les sirva, saludos
¡ Que buen articulo! Felicitaciones. Lo voy a probar a ver si puedo usarlo a futuro en alguna implementación…
espero haberte ayudado, gracias, saludos
bien mcuhas gracias ahora mismo me pongo a ver como lo soluciono…muy bueno el blog!!!
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
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!!