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