Spool task for the server

Navigation
2

Good evening!

Personal, currently in my work, I have to generate multiple .txt files to be exported to the bank of another company.

I flip this in hand and that sucks, every day I run, wait to send and everything.

I tried to do the spool command as follows: 

spool \10.0.0.1\diretorio\arquivo.txt
select * from schema.tabela_qualquer;
spool off;

The file was generated on this server, all right. Following this same line of reasoning, that I could generate this file on any computer on the network, I thought of doing a job that would do this service for me directly and the other company's boy would go there and pick up the file.

However, if I had to do: 

BEGIN
dbms_scheduler.create_job('"exporta_arquivos"',
  job_type=>'PLSQL_BLOCK',
  job_action=>'spool \10.0.0.1\diretorio\arquivo.txt; select * from schema.alguma_tabela; spool off;',
  number_of_arguments=>0,
  start_date=>TRUNC(SYSDATE,'HH'),
  repeat_interval=> 'FREQ=MINUTELY;INTERVAL=5',
  end_date=>NULL,
  job_class=>'"DEFAULT_JOB_CLASS"',
  enabled=>FALSE,
  auto_drop=>FALSE,
  comments=> 'Job que faz a exportação de arquivos');
END;
/

or

If instead of 

job_action=>'spool \10.0.0.1\diretorio\arquivo.txt; select * from schema.alguma_tabela; spool off;'

I try to make a procedure and try to call it by job_action, it ends up returning me error as if I could not spool because of the bars '\ 10.0.0.1' indicating that I'm going to play on the server.

Would you have any way to do this?

    
asked by anonymous 11.11.2015 / 21:57

1 answer

0

Your problem seems to be only an understanding of how sqlplus works and the job action .

When we connect to a remote server and execute some commands, they are running locally and not remotely.

If the server is in a Linux on ip 10.1.1.20 and you are in Windows on ip 10.1.1.113 and try to send a file to another Windows no ip 10.1.1.10 , you will do as Windows does to view computers on the network, which is using \\ 10.1.1.10 \ directory \ .

Now, when you place a JOB directly on the server where Oracle is, it has to use network paths such as a Linux, which does not natively understand Net Bios

In this case you will need to use smbclient

Create a procedure with the following command lines 

host mkdir -p ~/tmp
spool ~/tmp/arquivo.txt
select * from schema.tabela_qualquer;
spool off;
host smbclient //10.0.0.1/diretorio -U usuario --pass senha -c "put ~/tmp/arquivo.txt aquivo.txt;"
  
    

NOTE: smbclient must be installed

  
    
12.11.2015 / 21:36
What error is this javax.servlet.ServletException: getOutputStream ()? [closed] Alert message