Como usar UTL FTP - (transmitir arquivo via FTP

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Responder
Desenvolvedor
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 23
Registrado em: Qua, 26 Dez 2007 12:40 pm
Localização: SP

Pessoal,

Estou criando um JOB que irá rodar de 10 em 10 minutos em minha base de dados gerando um arquivo TXT, gostaria de transmitir esse arquivo via FTP para outro computador.

Já realizei diversas pesquisar e milhares de testes mas infelizmente não consigo usar UTL FTP...

Alguém ai já usou ou tenha algum link com mais conteúdos.


Obrigado,


Desenvolvedor
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, dá uma olhada neste exemplo do Metalink:

Selecionar tudo

Subject:  HOW TO FETCH THE CONTENTS OF A REMOTE SITE INTO FILE USING PLSQL 
 
  Doc ID:  Note:120460.1 Type:  BULLETIN 
  Last Revision Date:  12-APR-2001 Status:  PUBLISHED 

 
PURPOSE
-------
 
The purpose of this article is to provide a simple example on
how to use utl_file and utl_tcp to fetch the contents of a remote
site into a local file.

 
SCOPE & APPLICATION
-------------------
 
The sample program in this article is provided for educational purposes only.  
It is NOT supported by Oracle Support Services, however, it has been tested 
and appears to work as documented.  Be sure to test this code in your 
environment before relying on it.

 
PROCEDURE TO FETCH THE CONTENTS OF A REMOTE SITE
------------------------------------------------
 
The following pl/sql procedure can be used to fetch a site's text content
into a file using pl/sql. The stored procedure does not require webserver
only an installed JServer in Oracle 8i.

create or replace procedure grabsite(dir_in varchar2, file_in varchar2, 
                 site_in varchar2, port_in number default 80 ) is
    v_conn  UTL_TCP.CONNECTION;  
    v_file_handle UTL_FILE.FILE_TYPE;
    v_cols pls_integer;
    v_p pls_integer;
  BEGIN
    BEGIN
      v_file_handle :=
               UTL_FILE.FOPEN(dir_in,file_in,'W');
      v_conn := UTL_TCP.OPEN_CONNECTION(site_in, port_in);  
      v_p := UTL_TCP.WRITE_LINE(v_conn, 'GET / HTTP/1.0');           
      v_p := UTL_TCP.WRITE_LINE(v_conn);
      LOOP
        UTL_FILE.PUT(v_file_handle,UTL_TCP.GET_LINE(v_conn, TRUE));
      END LOOP;
    EXCEPTION
      WHEN UTL_TCP.END_OF_INPUT THEN
          NULL;
      WHEN others THEN
          DBMS_OUTPUT.PUT_LINE(substr(SQLERRM, 1, 80));
    END;
    UTL_FILE.FCLOSE(v_file_handle);
    UTL_TCP.CLOSE_CONNECTION(v_conn);    
  END;
/


dir_in must be included in UTL_FILE_DIR init.ora parameter.


You might encounter 
   ORA-29540: class oracle/plsql/net/TCPConnection does not exist 
This is the cause of Bug 1260358.
In this case run $OH/rdbms/admin/initplsj.sql as sys.


Usage:
In sqlplus type

set serveroutput on

exec grabsite('f:\orant816\oradata\v816','site.html','anything.oracle.com',8610);


This will create a file with the name of 'site.html' in 'f:\orant816\oradata\v816'
containing the anything.oracle.com:8610 site's text.


RELATED DOCUMENTS
-----------------

utltcp.sql utlfile.sql
.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 19 visitantes