oracle – Spool task to server


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 \\\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:

  job_action=>'spool \\\diretorio\arquivo.txt; select * from schema.alguma_tabela; spool off;',
  repeat_interval=> 'FREQ=MINUTELY;INTERVAL=5',
  comments=> 'Job que faz a exportação de arquivos');


if instead of

job_action=>'spool \\\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 '\' that indicate that I'm going to play on the server.

Was there any way to do this?


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 and you are on Windows on ip and try to send a file to another Windows on ip , you will do it like Windows does to see computers on the network, which is using \\\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 // -U usuario --pass senha -c "put ~/tmp/arquivo.txt aquivo.txt;"

NOTE: smbclient must be installed

Scroll to Top