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
.
Best Answer
Both works.
DBMS_SCHEDULER.CREATE_JOB
The type of the job (
job_type
) can bePLSQL_BLOCK
orSTORED_PROCEDURE
as well.For
PLSQL_BLOCK
, you can specify an anonymous block, a piece of PL/SQL code such asbegin ... end;
.For
STORED_PROCEDURE
, you can specify the name of the stored procedure to call.