forked from pavansolapure/opencodez-samples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpkg_test_scheduler.sql
More file actions
162 lines (125 loc) · 4.11 KB
/
pkg_test_scheduler.sql
File metadata and controls
162 lines (125 loc) · 4.11 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
create or replace PACKAGE pkg_test_scheduler
AS
PROCEDURE JOB_PROC_STEP_1;
PROCEDURE JOB_PROC_STEP_2;
PROCEDURE JOB_PROC_STEP_3;
FUNCTION GENERATE_FILE(FNAME IN VARCHAR2) RETURN NUMBER;
FUNCTION LOG_MESSAGE(MESSAGE IN VARCHAR2) RETURN NUMBER;
END pkg_test_scheduler;
/
create or replace PACKAGE BODY pkg_test_scheduler
AS
PROCEDURE JOB_PROC_STEP_1
AS
L_STEP_NO VARCHAR2(15) := 'JOB_PROC_STEP_1';
L_RC NUMBER;
L_PARAM_NAME VARCHAR2(25) := 'PROC_STEP_1_FILE_NAME';
L_PARAM_VALUE VARCHAR2(100) := 'JOB_SCHED_FILE_';
L_ID NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('[INFO] RUNNING JOB ' || L_STEP_NO);
L_RC := LOG_MESSAGE('[INFO] RUNNING JOB ' || L_STEP_NO);
SELECT (NVL(MAX(PARAM_ID),0) + 1) INTO L_ID FROM JOB_PARAMETERS;
L_PARAM_VALUE := L_PARAM_VALUE || TO_CHAR(SYSDATE, 'MM_DD_YYYY_HH_MI_SS') || '.TXT';
INSERT INTO JOB_PARAMETERS (PARAM_ID,
PARAM_NAME,
PARAM_VALUE,
PARAM_STATUS,
JOB_MESSAGE
) SELECT
L_ID AS PARAM_ID,
L_PARAM_NAME AS PARAM_NAME,
L_PARAM_VALUE AS PARAM_VALUE,
'CREATED' AS PARAM_STATUS,
'INSERTED FROM STEP 1 OF THE JOB' JOB_MESSAGE
FROM DUAL WHERE NOT
EXISTS ( SELECT 1 FROM
JOB_PARAMETERS JP
WHERE JP.PARAM_NAME = L_PARAM_NAME );
L_RC := LOG_MESSAGE('[INFO] FILE NAME GENERATED - ' || L_STEP_NO);
DBMS_OUTPUT.PUT_LINE ('[INFO] COMPLETED JOB ' || L_STEP_NO);
L_RC := LOG_MESSAGE('[INFO] COMPLETED JOB ' || L_STEP_NO);
END JOB_PROC_STEP_1;
----------------
PROCEDURE JOB_PROC_STEP_2
AS
L_STEP_NO VARCHAR2(15) := 'JOB_PROC_STEP_2';
L_RC NUMBER;
L_FILE_NAME VARCHAR2(250);
L_PARAM_NAME VARCHAR2(25) := 'PROC_STEP_1_FILE_NAME';
BEGIN
DBMS_OUTPUT.PUT_LINE ('[INFO] RUNNING JOB ' || L_STEP_NO);
L_RC := LOG_MESSAGE('[INFO] RUNNING JOB ' || L_STEP_NO);
SELECT PARAM_VALUE INTO L_FILE_NAME FROM JOB_PARAMETERS WHERE PARAM_NAME = L_PARAM_NAME AND PARAM_STATUS = 'CREATED';
IF L_FILE_NAME IS NOT NULL THEN
L_RC := GENERATE_FILE(L_FILE_NAME);
L_RC := LOG_MESSAGE('[INFO] FILE CREATED - ' || L_STEP_NO);
IF L_RC = 0 THEN
UPDATE JOB_PARAMETERS
SET PARAM_STATUS = 'USED'
WHERE PARAM_NAME = L_PARAM_NAME
AND PARAM_STATUS = 'CREATED';
L_RC := LOG_MESSAGE('[INFO] PARAMETER UPDATED - ' || L_STEP_NO);
END IF;
ELSE
L_RC := LOG_MESSAGE('[INFO] FILE NOT FOUND IN JOB_PARAMETERS - ' || L_STEP_NO);
END IF;
DBMS_OUTPUT.PUT_LINE ('[INFO] COMPLETED JOB ' || L_STEP_NO);
L_RC := LOG_MESSAGE('[INFO] COMPLETED JOB ' || L_STEP_NO);
COMMIT;
END JOB_PROC_STEP_2;
-------------------------
PROCEDURE JOB_PROC_STEP_3
AS
L_STEP_NO VARCHAR2(15) := 'JOB_PROC_STEP_3';
L_RC NUMBER;
L_PARAM_NAME VARCHAR2(25) := 'PROC_STEP_1_FILE_NAME';
BEGIN
DBMS_OUTPUT.PUT_LINE ('[INFO] RUNNING JOB ' || L_STEP_NO);
L_RC := LOG_MESSAGE('[INFO] RUNNING JOB ' || L_STEP_NO);
UPDATE JOB_PARAMETERS
SET PARAM_STATUS = 'ARCHIVED'
WHERE PARAM_NAME = L_PARAM_NAME
AND PARAM_STATUS = 'USED';
L_RC := LOG_MESSAGE('[INFO] PARAMETER ARCHIVED - ' || L_STEP_NO);
DBMS_OUTPUT.PUT_LINE ('[INFO] COMPLETED JOB ' || L_STEP_NO);
L_RC := LOG_MESSAGE('[INFO] COMPLETED JOB ' || L_STEP_NO);
END JOB_PROC_STEP_3;
----------------------
FUNCTION GENERATE_FILE(FNAME IN VARCHAR2) RETURN NUMBER
AS
V_FILE UTL_FILE.FILE_TYPE;
L_TXT VARCHAR2(500);
BEGIN
BEGIN
V_FILE := UTL_FILE.FOPEN(location => 'G_DATA_DIR',
filename => FNAME,
open_mode => 'w',
max_linesize => 32767);
L_TXT := 'File Written by Step 2 @' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS');
UTL_FILE.PUT_LINE(V_FILE, L_TXT);
UTL_FILE.FCLOSE(V_FILE);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('[INFO] ERROR WHILE GENERATE_FILE ' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 64));
RETURN 1;
END;
END GENERATE_FILE;
FUNCTION LOG_MESSAGE(MESSAGE IN VARCHAR2) RETURN NUMBER
AS
L_ID NUMBER;
BEGIN
BEGIN
SELECT (NVL(MAX(LOG_ID),0) + 1) INTO L_ID FROM JOB_LOG;
INSERT INTO JOB_LOG(LOG_ID, LOG_MESSAGE, LOG_TIME)
VALUES (L_ID, MESSAGE, SYSDATE);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('[INFO] ERROR WHILE LOG_MESSAGE ' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 64));
RETURN 1;
END;
END LOG_MESSAGE;
END pkg_test_scheduler;
/