When it comes to unloading data, often we would like to run SQL*Plus directly on the database server to avoid the network latency cost of dragging the resultant data down to your client. But (quite correctly) you probably do not have permissions to login to the server, because that opens up a giant security issue.
Using the scheduler, it is possible to run SQL scripts directly on the database server. Clearly, this still opens up some risk so we still protect it with a privilege (EXTERNAL JOB), and you also require an OS credential. But for certain application requirements, this might be a very productive mechanism to unload data.
First we allow the required privileges
SQL> GRANT CREATE JOB TO scott;
Grant succeeded.
SQL> GRANT CREATE EXTERNAL JOB TO scott;
Grant succeeded.
SQL> GRANT CREATE CREDENTIAL TO scott;
Grant succeeded.
SQL> BEGIN
2 DBMS_CREDENTIAL.create_credential(
3 credential_name => 'OS_ORACLE',
4 username => 'oracle',
5 password => 'oracle'
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
And now we can use the scheduler job type of SQL_SCRIPT to access SQL*Plus facilities on the database server. Note that we still include the connection string, so once again, be careful about giving this facility to people because they could easily connect to a totally different database running on your server!
SQL> DECLARE
2 l_job_name VARCHAR2(30) := 'RUN_SQLPLUS';
3 l_script VARCHAR2(32767);
4 BEGIN
5
6 -- sqlplus script in a variable
7 l_script := 'CONN scott/tiger@pdb1
8 set termout off
9 set echo off
10 SPOOL /tmp/test.lst
11 select object_id, object_name from user_objects;
12 SPOOL OFF';
13
14 DBMS_SCHEDULER.create_job(
15 job_name => l_job_name,
16 job_type => 'SQL_SCRIPT',
17 job_action => l_script,
18 credential_name => 'OS_ORACLE',
19 enabled => TRUE
20 );
21 END;
22 /
PL/SQL procedure successfully completed.
SQL> SELECT job_name, status, error#
2 FROM user_scheduler_job_run_details
3 ORDER BY job_name;
JOB_NAME STATUS ERROR#
-------------------- ------------------------------ ----------
RUN_SQLPLUS SUCCEEDED 0
Once the job has run, we can see that our spool file is now available on the server.
[oracle]$ cat /tmp/test.lst
SQL> select object_id, object_name from user_objects;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
71405 MY_PLSQL_FUNC
71503 MY_INITCAP
71787 DEPT_ROW
80653 DM$VJDOC_MODEL
80652 DM$VPDOC_MODEL
80651 DM$VMDOC_MODEL
80971 NEW_ORDER
82708 MY_VIRTUAL_COL
81283 MYPKG
Oh….did I mention? BE CAREFUL about who you grant this capability to!
Miss you Joel
Learning is not a spectator sport 
Got some thoughts? Leave a comment