-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathinsert_all_privs_spool.sql
More file actions
executable file
·199 lines (162 loc) · 4.7 KB
/
insert_all_privs_spool.sql
File metadata and controls
executable file
·199 lines (162 loc) · 4.7 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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
set termout on
set feed off
set trims on
set trim on
set pages 0
set lines 10000
set long 100000
set longc 100000
set head off
set echo off
set verify off
SET SERVEROUTPUT ON FORMAT WRAPPED
def output_path = '/media/sf_Patch'
CREATE OR REPLACE FUNCTION QA (IN_VALUE IN VARCHAR2) RETURN VARCHAR2 AS
V_ENC VARCHAR2(1) := '"';
V_SEP VARCHAR2(1) := ',';
OUT_VALUE VARCHAR2(4000);
BEGIN
IF IN_VALUE IS NOT NULL THEN
OUT_VALUE := REPLACE(REPLACE(IN_VALUE,CHR(13),' '),CHR(10),' ');
IF OUT_VALUE LIKE '%' || V_ENC || '%' OR OUT_VALUE LIKE '%' || V_SEP || '%' THEN
RETURN V_ENC || REPLACE(OUT_VALUE,V_ENC,V_ENC || V_ENC) || V_ENC;
ELSE
RETURN OUT_VALUE;
END IF;
ELSE
RETURN NULL;
END IF;
END;
/
VAR PRINT_COLS CLOB
EXEC :PRINT_COLS := 'qa(' || REPLACE(:v_print_cols,',',') || '','' || qa(') || ')';
--v_print_table = 't_col_privs'
--v_file_pref = 'privs_col'
--v_srczip_pref = 'privs'
set def off
spool /tmp/apoio.sql
SELECT 'SELECT DISTINCT ' || :PRINT_COLS || q'[ || ',' || qa(series) || ',' || qa(oraversion) || ',' || qa(psu_from) || ',' || qa(psu_to) || DECODE(FLAG,'R',',' || QA('-')) from &&v_print_table._F where oraversion='&&oraversion.' order by 1;]'
from dual;
spool off
set def on
COL EXEC1 NEW_V EXEC1 NOPRI
COL EXEC2 NEW_V EXEC2 NOPRI
COL EXEC3 NEW_V EXEC3 NOPRI
COL EXEC4 NEW_V EXEC4 NOPRI
-----------
def oraversion = "11.2.0.4"
def exec1 = ''
def exec2 = ''
def exec3 = ''
def exec4 = ''
SELECT 'spool &&output_path./&&v_file_pref..&&oraversion..csv' EXEC1,
'@@/tmp/apoio.sql' EXEC2,
'spool off' EXEC3,
'HOS zip -m -j -9 &&output_path./&&v_srczip_pref..csv.zip &&output_path./&&v_file_pref..&&oraversion..csv' EXEC4
FROM DUAL where exists (select 1 from &&v_print_table._F where oraversion='&&oraversion.');
spool /tmp/exec.sql
PRO &&EXEC1.
PRO &&EXEC2.
PRO &&EXEC3.
PRO &&EXEC4.
spool off
@@/tmp/exec.sql
-----------
def oraversion = "12.1.0.1"
def exec1 = ''
def exec2 = ''
def exec3 = ''
def exec4 = ''
SELECT 'spool &&output_path./&&v_file_pref..&&oraversion..csv' EXEC1,
'@@/tmp/apoio.sql' EXEC2,
'spool off' EXEC3,
'HOS zip -m -j -9 &&output_path./&&v_srczip_pref..csv.zip &&output_path./&&v_file_pref..&&oraversion..csv' EXEC4
FROM DUAL where exists (select 1 from &&v_print_table._F where oraversion='&&oraversion.');
spool /tmp/exec.sql
PRO &&EXEC1.
PRO &&EXEC2.
PRO &&EXEC3.
PRO &&EXEC4.
spool off
@@/tmp/exec.sql
-----------
def oraversion = "12.1.0.2"
def exec1 = ''
def exec2 = ''
def exec3 = ''
def exec4 = ''
SELECT 'spool &&output_path./&&v_file_pref..&&oraversion..csv' EXEC1,
'@@/tmp/apoio.sql' EXEC2,
'spool off' EXEC3,
'HOS zip -m -j -9 &&output_path./&&v_srczip_pref..csv.zip &&output_path./&&v_file_pref..&&oraversion..csv' EXEC4
FROM DUAL where exists (select 1 from &&v_print_table._F where oraversion='&&oraversion.');
spool /tmp/exec.sql
PRO &&EXEC1.
PRO &&EXEC2.
PRO &&EXEC3.
PRO &&EXEC4.
spool off
@@/tmp/exec.sql
-----------
def oraversion = "12.2.0.1"
def exec1 = ''
def exec2 = ''
def exec3 = ''
def exec4 = ''
SELECT 'spool &&output_path./&&v_file_pref..&&oraversion..csv' EXEC1,
'@@/tmp/apoio.sql' EXEC2,
'spool off' EXEC3,
'HOS zip -m -j -9 &&output_path./&&v_srczip_pref..csv.zip &&output_path./&&v_file_pref..&&oraversion..csv' EXEC4
FROM DUAL where exists (select 1 from &&v_print_table._F where oraversion='&&oraversion.');
spool /tmp/exec.sql
PRO &&EXEC1.
PRO &&EXEC2.
PRO &&EXEC3.
PRO &&EXEC4.
spool off
@@/tmp/exec.sql
-----------
def oraversion = "18.0.0.0"
def exec1 = ''
def exec2 = ''
def exec3 = ''
def exec4 = ''
SELECT 'spool &&output_path./&&v_file_pref..&&oraversion..csv' EXEC1,
'@@/tmp/apoio.sql' EXEC2,
'spool off' EXEC3,
'HOS zip -m -j -9 &&output_path./&&v_srczip_pref..csv.zip &&output_path./&&v_file_pref..&&oraversion..csv' EXEC4
FROM DUAL where exists (select 1 from &&v_print_table._F where oraversion='&&oraversion.');
spool /tmp/exec.sql
PRO &&EXEC1.
PRO &&EXEC2.
PRO &&EXEC3.
PRO &&EXEC4.
spool off
@@/tmp/exec.sql
-----------
def oraversion = "19.0.0.0"
def exec1 = ''
def exec2 = ''
def exec3 = ''
def exec4 = ''
SELECT 'spool &&output_path./&&v_file_pref..&&oraversion..csv' EXEC1,
'@@/tmp/apoio.sql' EXEC2,
'spool off' EXEC3,
'HOS zip -m -j -9 &&output_path./&&v_srczip_pref..csv.zip &&output_path./&&v_file_pref..&&oraversion..csv' EXEC4
FROM DUAL where exists (select 1 from &&v_print_table._F where oraversion='&&oraversion.');
spool /tmp/exec.sql
PRO &&EXEC1.
PRO &&EXEC2.
PRO &&EXEC3.
PRO &&EXEC4.
spool off
@@/tmp/exec.sql
-----------
undef oraversion
undef v_print_table v_file_pref v_srczip_pref
DROP FUNCTION QA;
--HOS rm /tmp/exec.sql /tmp/apoio.sql
set termout on
set feed on
set echo on
set verify on