Oracle SQL via dbms_job

For long-running SQL, it can be useful to use dbms_job.submit so that the SQL will be detached from the client terminal. A SELECT statement would need to write results out to another table. Errors will be recorded to the database alert.log.



Create a test, in file named file.sql:

set lines 100
create table test (cola int);
select * from user_jobs;

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'insert into test values (1);');
commit;
END;
/

print jobno
select job,what from user_jobs;

exec dbms_lock.sleep(10)
select * from test;

select job,what from user_jobs;

Submit the test:

sqlplus -s user@SID @file.sql


Table created.


no rows selected


PL/SQL procedure successfully completed.


JOBNO
----------
208


JOB
----------
WHAT
----------------------------------------------------------------------------------------------------
208
insert into test values (1);



PL/SQL procedure successfully completed.


COLA
----------
1


no rows selected