Генерация генерации таблицы для патча:
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.
declare
COWNER varchar2(5000) := upper('sh'); -- Схема
CTABLE_NAME varchar2(5000) := upper('tbl'); -- Имя таблицы
CTN_SOKR1 varchar2(5000) := 'a'; -- Сокращенное имя таблицы 1 (для полей)
CTN_SOKR2 varchar2(5000) := 'lxs'; -- Сокращенное имя таблицы 2 (для значений)
CID varchar2(5000) := '1,2'; -- COLUMN_ID полей, которые будут в Where
CPL varchar2(32767) := 'dbms_output.put_line';
C varchar2(5000);
CTAB varchar2(5000) := ' ';
C1 varchar2(5000);
C2 varchar2(5000);
C2_V varchar2(5000);
CINS1 varchar2(5000) := '';
CINS2 varchar2(5000) := '';
CUPD varchar2(5000) := '';
CWHR varchar2(5000) := '';
CWHR2 varchar2(5000) := '';
CDCL varchar2(5000) := '';
CRLF varchar2(2) := chr(13);
N number;
D date;
begin
execute immediate 'alter session set nls_numeric_characters=''.,'''; --установка разделителя числа
execute immediate 'alter session set nls_date_format=''yyyy.mm.dd'''; --установка даты
-- 'BK_name_cyr'||'='||RC_BK.name_cyr||CRLF
-- cStr:=cStr||'otv_fio_im'||'='||rc_otv.fio_im||CRLF;
for COL in ( select C.COLUMN_ID
,C.COLUMN_NAME
,C.DATA_TYPE
,C.DATA_LENGTH
,O.COMMENTS
from COLS C, DBA_COL_COMMENTS O
where C.TABLE_NAME = O.TABLE_NAME
and C.COLUMN_NAME = O.COLUMN_NAME
and C.TABLE_NAME = upper(CTABLE_NAME)
order by C.COLUMN_ID) loop
if CINS1 is not null then
CINS1 := CINS1 || ',' || CRLF;
CINS2 := CINS2 || ',' || CRLF;
end if;
C := COL.COLUMN_NAME;
C1 := CTN_SOKR1 || '.' || COL.COLUMN_NAME;
case
when COL.DATA_TYPE = 'VARCHAR2' then
C2_V := 'replace(' || CTN_SOKR2 || '.' || C || ','''''''','''''''''''')';
C2 := '''''''||replace(' || CTN_SOKR2 || '.' || C || ','''''''','''''''''''')||''''''';
when COL.DATA_TYPE = 'NUMBER' then
C2_V := 'nvl(to_char(' || CTN_SOKR2 || '.' || C || '),''null'')';
C2 := '''||nvl(to_char(' || CTN_SOKR2 || '.' || C || '),''null'')||''';
when COL.DATA_TYPE = 'DATE' then
C2_V := 'to_char(' || CTN_SOKR2 || '.' || C || ',''DD.MM.YYYY'')';
C2 := '''||to_char(' || CTN_SOKR2 || '.' || C || ',''DD.MM.YYYY'')||''';
else
C2_V := 'replace(' || CTN_SOKR2 || '.' || C || ','''''''','''''''''''')';
C2 := '''''''||replace(' || CTN_SOKR2 || '.' || C || ','''''''','''''''''''')||''''''';
end case;
--dbms_output.put_line(c||'='||c1);
CINS1 := CINS1 || CTAB || CTAB || CTAB || C1;
CINS2 := CINS2 || CTAB || CTAB || CTAB || C2;
--
select count( * )
into N
from ( select regexp_substr(VAL
,'[^,]+'
,1
,level)
COND
from (select CID VAL
from dual) T
connect by instr(VAL
,','
,1
,level - 1) > 0)
where COL.COLUMN_ID in COND;
--
if N = 0 then
if CUPD is not null then
CUPD := CUPD || ',' || CRLF;
end if;
CUPD := CUPD || CTAB || CTAB || C1 || '=' || C2;
else
if CWHR is not null then
CWHR := CWHR || CRLF || CTAB || CTAB || 'and ';
CWHR2 := CWHR2 || CRLF || CTAB || CTAB || 'and ';
CDCL := CDCL || CRLF;
end if;
CWHR := CWHR || C1 || '=' || C2;
CWHR2 := CWHR2 || C2_V || '=' || C || '_';
if COL.DATA_TYPE = 'VARCHAR2' then
CDCL := CDCL || C || '_ ' || COL.DATA_TYPE || '(' || COL.DATA_LENGTH || ') := '''';';
else
CDCL := CDCL || C || '_ ' || COL.DATA_TYPE || ' := ;';
end if;
--exit;
end if;
end loop;
-------------------- Generation1 --------------------
dbms_output.PUT_LINE('declare');
dbms_output.PUT_LINE(CDCL);
dbms_output.PUT_LINE('CRLF varchar2(2) := chr(13);');
dbms_output.PUT_LINE('begin');
dbms_output.PUT_LINE(CTAB || CPL || '(''begin'');');
dbms_output.PUT_LINE(CTAB || 'for ' || CTN_SOKR2 || ' in (');
dbms_output.PUT_LINE(CTAB || CTAB || 'select ' || CTN_SOKR2 || '.*');
dbms_output.PUT_LINE(CTAB || CTAB || 'from ' || COWNER || '.' || CTABLE_NAME || ' ' || CTN_SOKR2);
dbms_output.PUT_LINE(CTAB || CTAB || 'where ' || CWHR2);
dbms_output.PUT_LINE(CTAB || CTAB || CTAB || ') loop');
dbms_output.PUT_LINE(CTAB || CTAB || '-------------------- begin Generation2 --------------------');
dbms_output.PUT_LINE( CTAB
|| CTAB
|| CPL
|| '('''
|| CTAB
|| 'update '
|| COWNER
|| '.'
|| CTABLE_NAME
|| ' '
|| CTN_SOKR1
|| ' set'
|| ''');');
dbms_output.PUT_LINE(CTAB || CTAB || CPL || '(''' || CUPD || ''');');
dbms_output.PUT_LINE(CTAB || CTAB || CPL || '(''' || CTAB || 'where ' || CWHR || ';' || ''');');
dbms_output.PUT_LINE(CTAB || CTAB || CPL || '(''' || CTAB || 'if sql%notfound then' || ''');');
dbms_output.PUT_LINE('');
dbms_output.PUT_LINE( CTAB
|| CTAB
|| CPL
|| '('''
|| CTAB
|| CTAB
|| 'insert into '
|| COWNER
|| '.'
|| CTABLE_NAME
|| ' '
|| CTN_SOKR1
|| ' ('
|| ''');');
dbms_output.PUT_LINE( CTAB
|| CTAB
|| CPL
|| '('''
|| CINS1
|| ')'
|| CRLF
|| CTAB
|| CTAB
|| CTAB
|| 'VALUES ('
|| CRLF
|| CINS2
|| ');'
|| ''');');
dbms_output.PUT_LINE(CTAB || CTAB || CPL || '(''' || CTAB || 'end if;' || ''');');
dbms_output.PUT_LINE(CTAB || CTAB || CPL || '(''' || CTAB || 'commit;' || ''');');
dbms_output.PUT_LINE(CTAB || CTAB || '-------------------- end Generation2 --------------------');
dbms_output.PUT_LINE(CTAB || 'end loop;');
dbms_output.PUT_LINE(CTAB || CPL || '(''end;'');');
dbms_output.PUT_LINE(CTAB || CPL || '(CRLF||''/''||CRLF);');
dbms_output.PUT_LINE('end;' || CRLF || CRLF || '/' || CRLF);
end;
Выгрузка метаданных таблицы, для sqlplus:
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.
column tempfile new_value tempfile noprint;
select 'D:\ALL_OBJ\sql\&&P'||'_'||to_char(sysdate, 'yyyymmdd_hh24mi')||'.sql' tempfile from dual;
set feedback off
set heading off
set termout off
set linesize 4000
set trimspool on
set verify off
set longchunksize 32767
set LONG 1073741824
COLUNM WRAP OFF
SET RECSEP OFF
--COLUNM text WORD_WRAPPED
set underline OFF
set PAGESIZE 0
set serveroutput on size 320000
---------------------------------------------------------------
spool &&tempfile;
---------------------------
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'INDEX',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'INDEX STATISTICS',TRUE);
/
declare
n number;
cTb varchar2(32000) := UPPER('&&P');
cOw varchar2(32000) := UPPER('схема');
bl clob;
PROCEDURE new_put_line(mes IN clob) AS
c varchar2(32000);
BEGIN
insert into b_reb_rtv a
(a.brr_name, a.brr_type, a.brr_clob)
VALUES (cTb, 'ALL_OBJ', mes);
commit;
END;
begin
delete b_reb_rtv a
where a.BRR_TYPE = 'ALL_OBJ'
and a.BRR_NAME = cTb;
for o in (SELECT o.*
FROM dba_objects o
WHERE 1=1
--and o.owner = cOw
and o.OBJECT_NAME = cTb
--AND o.object_type IN ('PACKAGE')
) loop
--new_put_line('-- '||o.object_type||', '||UPPER(o.object_name)||', '||UPPER(o.owner));
begin
-- таблица
bl := DBMS_METADATA.GET_DDL(o.object_type, UPPER(o.object_name), UPPER(o.owner));
new_put_line(bl);
new_put_line('');
exception when OTHERS then null;
end;
begin
-- коменты
bl := dbms_metadata.get_dependent_ddl('COMMENT', UPPER(o.object_name), UPPER(o.owner));
new_put_line(bl);
new_put_line('');
exception when OTHERS then null;
end;
begin
-- права
bl := dbms_metadata.get_dependent_ddl('OBJECT_GRANT', UPPER(o.object_name), user);
new_put_line(bl);
new_put_line('');
exception when OTHERS then null;
end;
begin
-- синонимы
for s in (SELECT TABLE_OWNER, TABLE_NAME, SYNONYM_NAME
FROM DBA_SYNONYMS s
WHERE s.TABLE_OWNER = UPPER(o.owner)
and s.TABLE_NAME = UPPER(o.object_name)) loop
SELECT dbms_metadata.get_ddl(o.OBJECT_TYPE, o.OBJECT_NAME, o.OWNER) into bl
FROM dba_objects o
where o.OBJECT_NAME = s.SYNONYM_NAME
and o.OBJECT_TYPE = 'SYNONYM';
-- из за кривой РТВ будем извращатся
new_put_line(replace(bl,'CREATE OR REPLACE PUBLIC','CREATE PUBLIC'));
new_put_line('');
end loop;
exception when OTHERS then null;
end;
begin
-- триггеры
for tr in (SELECT tr.*
FROM DBA_TRIGGERS tr
WHERE tr.OWNER = UPPER(o.owner)
and tr.TABLE_NAME = UPPER(o.object_name)) loop
SELECT dbms_metadata.get_ddl(o.OBJECT_TYPE, o.OBJECT_NAME, o.OWNER) into bl
FROM dba_objects o
where o.OBJECT_NAME = tr.TRIGGER_NAME;
new_put_line(bl);
new_put_line('');
end loop;
exception when OTHERS then null;
end;
begin
-- индексы
for tr in (select *
from DBA_INDEXES
where OWNER = UPPER(o.owner)
and TABLE_NAME = UPPER(o.object_name)) loop
SELECT dbms_metadata.get_ddl(o.OBJECT_TYPE, o.OBJECT_NAME, o.OWNER) into bl
FROM dba_objects o
where o.OBJECT_NAME = tr.INDEX_NAME;
new_put_line(bl);
new_put_line('');
end loop;
exception when OTHERS then null;
end;
end loop;
end;
/
-----------------------------------------------------------
spool &&tempfile;
/
SELECT a.brr_clob
FROM b_reb_rtv a
where a.BRR_TYPE = 'ALL_OBJ'
and a.BRR_NAME = upper('&&P')
order by a.BRR_CODE;
---------------------------------------------------------------
spool off
set feedback on
set heading on
set termout on
set linesize 10000
set longchunksize 8000
set underline OFF
exit
Компиляция недействительных объектов для sqlplus:
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.
--REMOTE_DEPENDENCIES_MODE=SIGNATURE;
column tempfile new_value tempfile noprint;
select 'C:\work\sql\COMPILE\MAKE_REC_'||to_char(sysdate, 'yyyymmdd_hh24mi')||'.sql' tempfile from dual;
set term on
prompt ===================================================================
prompt Компиляция недействительных объектов
prompt ===================================================================
set termout off
whenever SQLError continue
spool &&tempfile;
set heading off feedback off trimout on serveroutput on
set termout on trimspool on
prompt Список недействительных("INVALID") объектов до начала компиляции:
select rpad(Object_Name,30,' ')|| Object_Type
from USER_OBJECTS
where Status = 'INVALID'
and Object_Name not like 'BIN%'
and Object_Type in ('VIEW', 'PROCEDURE', 'PACKAGE',
'PACKAGE BODY', 'FUNCTION', 'TRIGGER')
order by Object_Type
;
prompt ===================================================================
prompt _Выполняется ...
prompt
declare
cursor cur_recompile is
select Object_Type, Object_Name, decode( Object_Type, 'PACKAGE BODY', 3,
'VIEW', 1,
2
) Obj_Type
from USER_OBJECTS
where Status = 'INVALID'
and Object_Name not like 'BIN%'
and Object_Type in ('VIEW', 'PROCEDURE', 'PACKAGE',
'PACKAGE BODY', 'FUNCTION', 'TRIGGER')
order by Obj_Type
;
--
cur_rec cur_recompile%ROWTYPE;
--
cursor cur_error (N_E char, T_E char) is
select name, type from user_errors
where name = N_E
and type = T_E;
--
cur_er cur_error%ROWTYPE;
--
error_rec varchar2(130);
axt varchar2(2);
v_Error boolean := FALSE;
cur_NOT_FOUND exception; -- Если нет объектов подлежащих компиляции.
begin
--
dbms_output.enable(1000000);
dbms_output.put_line('================== Протокол работы компиляции =====================');
dbms_output.put_line('-');
--
open cur_recompile;
fetch cur_recompile into cur_rec;
if cur_recompile%Found
then
close cur_recompile;
--
for i in 1..5
loop
--
for k in cur_recompile
loop
--
If k.Object_Type = 'PACKAGE' then
begin
dbms_utility.exec_ddl_statement('alter package ' || k.Object_Name || ' compile');
exception
when others then null;
end;
elsif k.Object_Type = 'PACKAGE BODY' then
begin
dbms_utility.exec_ddl_statement('alter package ' || k.Object_Name || ' compile body');
exception
when others then null;
end;
elsif k.Object_Type = 'PROCEDURE' then
begin
dbms_utility.exec_ddl_statement('alter procedure ' || k.Object_Name || ' compile');
exception
when others then null;
end;
elsif k.Object_Type = 'FUNCTION' then
begin
dbms_utility.exec_ddl_statement('alter function ' || k.Object_Name || ' compile');
exception
when others then null;
end;
elsif k.Object_Type = 'VIEW' then
begin
dbms_utility.exec_ddl_statement('alter view ' || k.Object_Name || ' compile');
exception
when others then null;
end;
elsif k.Object_Type = 'TRIGGER' then
begin
dbms_utility.exec_ddl_statement('alter trigger ' || k.Object_Name || ' compile');
exception
when others then null;
end;
end if;
--
-- Проверяем: Был ли "Warning" при компиляции объекта.
--
open cur_error(k.Object_Name, k.Object_Type);
fetch cur_error into cur_er;
If cur_error%FOUND then
--
If i = 5 then
dbms_output.put_line('Объект ' || rpad(k.Object_Name,30,' ') || ' ' ||
rpad(k.Object_Type,12,' ') || ' не скомпилировался !!!');
v_Error := TRUE;
end if;
--
end if;
--
close cur_error;
--
end loop; -- Конец цикла по инвалидным объектам.
--
end loop; -- Конец цикла от 1-го до 5-ти.
else
close cur_recompile;
raise cur_NOT_FOUND;
end if;
--
If not v_Error then
dbms_output.put_line('Компиляция выполнена успешно.');
end if;
--
exception
when cur_NOT_FOUND then
dbms_output.put_line('Объектов, подлежащих компиляции не обнаружено !!!');
when OTHERS then
dbms_output.put_line( sqlerrm );
end;
/
prompt
prompt
commit;
set termout off
spool off
|
