Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER

Question

If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report.

DROP TABLE TEST1;
CREATE TABLE TEST1 AS (
SELECT 
C.DATE,
C.ITEM,   
(SELECT name FROM name_codes WHERE code = C.DEPARTMENT) AS DEPARTMENT,
C.BUSINESS
FROM LOGS C));

That's a fraction of it but the code more or less creates tables, filters fields, and combines outputs until the desired result. I am looking to automate data input for other scripts as well. Each script calculates a KPI and would be its own procedure. Then all of those procedures would be wrapped into a package.

That package will be scheduled as a job through DBMS_SCHEDULER. Does this sound like the correct course of action? If so, can I call an existing script to create a procedure or would I need to paste the PLSQL into the procedure script? For the moment, I just want to test the first script as a procedure which is scheduled as a job through DBMS_SCHEDULER.

Answer 1

If so, can I call an existing script to create a procedure or would I need to paste the PLSQL into the procedure script?

Both works.

DBMS_SCHEDULER.CREATE_JOB

The type of the job (job_type) can be PLSQL_BLOCK or STORED_PROCEDURE as well.

For PLSQL_BLOCK, you can specify an anonymous block, a piece of PL/SQL code such as begin ... end;.

For STORED_PROCEDURE, you can specify the name of the stored procedure to call.