0

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.

0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.