Execute SQL queries in DBMS_SCHEDULER without saving Password at Server

You can execute the SQL queries without saving password at Server Script instead of saving and scheduling directly in DBMS_SCHEDULER. With this you can eliminate the use of password mention as Server script for doing particular task. But you need the OS password for it.

1. Create an OS Level credential from SYS USER.
Credential package is used Operating system username and password. So, verify OS user before creating OS credentials in Oracle database.

--For Normal user
exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'Eoracle', password => 'Password1');
--For Domain user
exec dbms_scheduler.create_credential( credential_name => 'Windowuser', username => 'EORACLE', password => 'Password1', windows_domain => 'ORCL' );


2. Run the SQL statments and get output in spool file without saving it in server.
The benefit of doing this way is you never need to save password in the Server script. You are saving credential in the dbms scheduler.

declare
l_Script varchar2(100) :=
'connect sys/Password1 as sysdba
spool /tmp/x.out
select * from v$database;
Select * from v$instance;
exit';
begin
dbms_scheduler.create_job(
job_type => 'SQL_SCRIPT',
job_name => 'SCRIPTSQLEXECUTION',
job_action => l_Script,
enabled => true,
credential_name => 'Windowuser'
);
end;
/


3. Check the job is created in DBMS SCHEDULER

select job_name,start_date,last_start_date,Next_run_Date,failure_count,run_count,job_action from dba_scheduler_jobs where job_name='SCRIPTSQLEXECUTION';


4. Manually run the job for check its error free:

EXEC dbms_scheduler.run_job(job_name => 'SCRIPTSQLEXECUTION');


--If any error, you need to correct by dropping and re-creating it by executing step 2
-- For Dropping the job
exec dbms_scheduler.drop_job(job_name => 'SCRIPTSQLEXECUTION');


5. For Schedule the script, use following script:


declare
l_Script varchar2(100) :=
'connect sys/Password1 as sysdba
spool /tmp/x.out
select * from v$database;
Select * from v$instance;
exit';
begin
dbms_scheduler.create_job(
job_type => 'SQL_SCRIPT',
job_name => 'SCRIPTSQLEXECUTION',
job_action => l_Script,
enabled => true,
credential_name => 'Windowuser',
start_date => SYSDATE +1/24/59,
repeat_interval => 'FREQ=DAILY; BYHOUR=22;'
);
end;
/