I created a file watcher job 'fw_job', but it seems the file watcher doesn't start it.
"JServer JAVA Virtual Machine" is VALID
I granted "CREATE CREDENTIAL" and "CREATE ANY JOB" to my user
SYS.FILE_WATCHER FREQUENCY is set to 'FREQ=MINUTELY;INTERVAL=1' and i can see it is running minutely via dba_scheduler_job_log
Now what i did:
-- Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
DROP TABLE fw_check;
CREATE TABLE fw_check(run_date DATE);
CREATE OR REPLACE PROCEDURE setFwRun IS BEGIN
INSERT INTO fw_check(run_date)VALUES(SYSDATE);
COMMIT;
END;
/
BEGIN
dbms_credential.create_credential(
'fw_credential',
username => 'user', -- <-- my local user on database server
password => 'password' -- <-- my password
);
END;
/
BEGIN
dbms_scheduler.create_program(
program_name => 'fw_proc',
program_type => 'stored_procedure',
program_action => 'setFwRun',
number_of_arguments => 0,
ENABLED => FALSE
);
END;
/
BEGIN
dbms_scheduler.create_file_watcher(
file_watcher_name => 'fw_file_watcher',
directory_path => 'Q:\impex', -- <-- my directory path on database machine
file_name => '*.finished',
credential_name => 'fw_credential',
enabled => FALSE
);
END;
/
BEGIN
dbms_scheduler.create_job(
job_name => 'fw_job',
program_name => 'fw_proc',
queue_spec => 'fw_file_watcher',
auto_drop => FALSE,
ENABLED => FALSE
);
END;
/
BEGIN
dbms_scheduler.enable('fw_proc,fw_file_watcher,fw_job');
END;
/
-- manually creating new file x.finished at Q:\impex
I verified, the job itself works:
BEGIN
dbms_scheduler.run_job('fw_job');
END;
/
SELECT * FROM fw_check;
-- RUN_DATE
-- -----------
-- 16.08.2017
Am i doing anything wrong in configuration?
The credentials seems fine for me because i connected with this local user to the server to manueally create the .finished file.
Can i verify if a credential is valid?
2nd option is the sys.file_watcher does not start my fw_file_watcher. Am i able to verify this?
Thanks for your response
Bets regards
Benjamin
You can see file watcher details in
SQL> desc user_scheduler_file_watchers
Name Null? Type
----------------------------------------------------- -------- ------------------------------
FILE_WATCHER_NAME NOT NULL VARCHAR2(128)
ENABLED VARCHAR2(5)
DESTINATION_OWNER VARCHAR2(261)
DESTINATION VARCHAR2(261)
DIRECTORY_PATH NOT NULL VARCHAR2(4000)
FILE_NAME NOT NULL VARCHAR2(512)
CREDENTIAL_OWNER VARCHAR2(128)
CREDENTIAL_NAME VARCHAR2(128)
MIN_FILE_SIZE NOT NULL NUMBER
STEADY_STATE_DURATION INTERVAL DAY(3) TO SECOND(0)
LAST_MODIFIED_TIME TIMESTAMP(6) WITH TIME ZONE
COMMENTS VARCHAR2(4000)
Some things to watch out for.
1) privs
If you create the credential as one user (eg SYS) and want another user to use it, you need to do:
grant execute on [credential] to other_user
2) logs
If your credential is wrong, you'll get messages in the alert log and trace files, eg, I did this
exec dbms_scheduler.create_credential('LOCAL_ORACLE','oracle','wrong');
begin
dbms_scheduler.create_job(
job_name=>'RUN_SHELL',
job_type=>'EXECUTABLE',
job_action=>'/home/oracle/my_shell.sh',
start_date=>sysdate,
enabled=>false,
comments=>'Run script'
);
dbms_scheduler.set_attribute('RUN_SHELL', 'credential_name', 'LOCAL_ORACLE');
dbms_scheduler.enable('RUN_SHELL');
end;
/
and I got this in the alert log
2017-08-28T21:35:59.194360-04:00
Errors in file /u01/app/oracle/diag/rdbms/db122/db122/trace/db122_j000_4822.trc:
ORA-12012: error on auto execute of job "MCDONAC"."RUN_SHELL"
ORA-27369: job of type EXECUTABLE failed with exit code: 7 !@#--!@#7#@!--#@!
3) callback routine
(I think) the called back routine must take a SCHEDULER_FILEWATCHER_RESULT parameter, ie
procedure setFwRun(x SCHEDULER_FILEWATCHER_RESULT) is ...