oracle – Spool task to server

Question:

Good night!

Guys, currently at my job, I have to generate several .txt files to be exported to another company's database.

I run it by hand and it sucks, running everyday, waiting to ship and everything.

I tried doing 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 guy from the other company would go there and get the file.

However, if I would have to:

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 do a procedure and try to call it by job_action, it ends up giving me an error as if I couldn't run the spool because of the bars '\10.0.0.1' that indicate that I'm going to play on the server.

Was there any way to do this?

Answer:

Your problem seems to be just understanding how sqlplus and the job action work .

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

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

Now, when placing a JOB directly on the server where Oracle is, it has to use network paths like a Linux, which does not natively understand Net Bios type paths.

For this case it will be necessary 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

Scroll to Top