Spool task for the server

Asked

Viewed 393 times

3

Good night!

Guys, currently in my job, I have to generate multiple files. txt to be exported to another company’s database.

I roll it in my hand and it sucks, every day I spin it, I wait to send it 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 in the network, I thought of doing a job that did this service for me directly and the guy from the other companies would go there and pick up the file.

But if I had 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 make a precedent and try to call it by job_action, ends up returning me error as if I could not run the spool because of the bars ' 10.0.0.1' which indicate that I will play on the server.

There’d be some way to do it?

oracle server procedure job

  • I put the job to run, the generated error is this: 163934, 11/11/2015 18:27:36, FAILED, 6550, 11/11/2015 18:27:36, 11/11/2015 18:27:36, 0 00:00, 1, 1515,16753, 10526, 0 00:00:00.00, "ORA-06550: row 1, column 756: PLS-00103: Encountered the Symbol """" when expecting one of the following:

  • I’ve never done this directly, I’ve always used mapping through the NET USE, but by its error, it looks like character escape problem. : col destino new_value destino
select '\\'||trim(:IP)||'\'||trim(:dir)||'\'||trim(:arquivo) destino from dual;
spool &destino

  • Which version of Oracle?

  • The version is Oracle 11g

  • tried what I went through? Try it too job_action=>'spool {\\10.0.0.1\diretorio\arquivo.txt}; ... '

  • "ORA-06550: line 1, column 756: PLS-00103: Encountered the Symbol ""{"" when expecting one of the following:" &#Xa.

  • and so? col dest new_value dest 
select '{\\10.0.0.1\diretorio\arquivo.txt}' dest from dual; 
spool &dest I tested a code here and it worked displaying in the output with prompt "&dest".

  • Did you say that even if you put the code that works in SQL commands inside job_Action it sticks? From what I understand, it is better to create a precedent because the body of JOB does not seem to escape characters. If it still doesn’t work out, it may be necessary to define a PROGRAM that sends the output file to the destination.

  • Yes, within the job body and in the body of the Function.. it only works in command window in the PL/SQL

  • What Operating System Job is running on???

  • The system is linux

  • And the target is Windows?

  • Yes! That may be the problem.. but why running by command window would it leave?

Show 8 more comments

1 answer

0

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

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

If the server is in one Linux in ip 10.1.1.20 and Voce is in Windows in ip 10.1.1.113 and try to send one file to another Windows in ip 10.1.1.10, Voce will do it as Windows does to see computers on the network, which is using \\10.1.1.10 directory\.

Now, when you put a direct JOB on the server where Oracle is, it has to use network paths like Linux, which doesn’t understand native paths like Net Bios.

For this case it will be necessary to use the smbclient

Create a precedent 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: Requires smbclient to be installed

Browser other questions tagged oracle server procedure job

You are not signed in. Login or sign up in order to post.