上面的类型对象是作为元数据存储器的,用于存放多个表的完整的元数据信息集合。所有上面的类型都包含在程序包规范中(列表2所示)。我还介绍了下面一些API:/ a0 w. `) S6 P1 l& p: T
3 z& D3 h) R+ o+ K# h) v · MetaDataPkg.Load()过程:把特定表的元数据信息载入存储器中。
& m8 X, H$ d Y% w6 m
3 `- H) @, e8 J" @/ d- i4 r · MetaDataPkg.GetMeta()函数:它从存储器中检索tFullMetaObject类型的对象。
! `* E' z; E: e; ~) q. Z# S3 s; P0 M. b( i
· MetaDataPkg.SetMeta()过程(重载的):把对象的元数据存储到存储器中。 3 ^5 H' _6 K* r/ X
- z4 k$ O. X' ^3 j1 ?8 P
· MetaDataPkg.Reset过程(重载的):对存储器复位。
, `) z. Y" H3 F! u* z; `, i, B) U' v& |8 d" u
· MetaDataPkg.Show过程(重载的):显示存储器的内容。7 _5 W f% Y9 }$ o8 c) q2 S4 y
( d# z. D* Q7 c! P& [& [" m
实现所有这些事务的代码
7 Y! ~5 ]! K' g6 P) R2 a% U- p/ o
& o2 u& E; T1 D; L 列表3显示了MetaDataPkg程序包主体代码的一些解释。私有过程SetEnvironment()包含了所有的环境设置代码。在程序包的初始化部分会调用这个过程,因此在每个对话中它都只执行一次,符合你的需求(你希望在开头设置一次)。程序包提供了用于设置环境参数的API:DBMS_METADATA.SET_TRANSFORM_PARAM()过程。/ n, W1 L6 b2 P% [5 N. q
) H/ v/ y4 J- L 列表3:MetaDataPkg程序包主体
8 ]6 ~ F. E1 X
2 S$ K$ i% ~# m2 M$ k' Y* C, I4 t6 `vMetaData tArrayFullMetaObjectByString;
; v4 ]( Z$ r" L% L% c+ z/ O* P8 g9 i A$ l. s( s
procedure SetEnvironment
, }, n+ z1 f5 s3 q4 g; Wis
5 W- z0 C2 P& m$ p6 Fbegin
, w0 `; m1 p6 C) o: P) L dbms_metadata.set_transform_param(7 l1 X, ^2 q! A) G
dbms_metadata.session_transform, ’PRETTY’, false);9 z* [: Z0 ]( [
dbms_metadata.SET_TRANSFORM_PARAM() u/ `5 K0 Z2 i% V. F& ^5 s: R! @
dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);" m% p. I" U+ w
dbms_metadata.set_transform_param(6 S+ x: Q* Z. l4 N, V
dbms_metadata.session_transform, ’STORAGE’, true);4 v, k& a- [! e3 m
dbms_metadata.set_transform_param(1 k) w) v6 f- ^/ e) z" k5 ^4 _
dbms_metadata.session_transform, ’TABLESPACE’, true); 3 I H6 C7 Q( N% W$ \! z* |
dbms_metadata.set_transform_param(
* Q. S: T5 ^$ w2 G# L" n dbms_metadata.session_transform, ’CONSTRAINTS’, false);! N. }3 R% H. G$ p1 l0 u
dbms_metadata.set_transform_param(
' m5 q1 z: Z1 [& C dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);4 u9 \, o- i# {% }4 \/ b' ?
dbms_metadata.set_transform_param(' o, d& r K. h1 I
dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);
2 `1 X) w5 o; I3 |: J! M7 C% w' ~
$ v7 g* ]. @, d( u& Uend SetEnvironment;; l. t/ d" J3 E: J& r
- G7 k+ V4 T, e9 |procedure Print1 Q7 A2 G4 z7 q" ]3 ?+ z
(* {. k5 C4 N+ m, D) h
pString varchar2,
/ D2 k: R/ f; v pLineSize positive := 80
+ y: z3 p* E1 T) S9 F)
8 t3 I4 K _6 d2 His
" w' Z) ?2 j( b3 s% g# ?' z4 |& K/ ^0 A: s% ^% g" t
vLineSize pls_integer := least(nvl(pLineSize, 80), 255);6 o" {1 J' _& J$ a+ [6 Q+ n
9 _4 t1 r) u9 |- F- V
begin
; I! Y; d& W0 |# K7 @; J dbms_output.enable(1000000);6 w1 \: n `3 m! B- P* l; `* V6 a
" ~+ z2 l8 }; K. J( I1 q4 s
if (length(pString) > vLineSize)
" S( i# @; b7 l4 y( r. S then
8 R2 j) M, `. l1 |6 r7 p dbms_output.put_line(substr(pString, 1, vLineSize));
5 F4 C( y$ a1 p# c! D( x7 z/ s Print(substr(pString, pLineSize + 1), vLineSize);
& c- ?- h2 C K3 B else d8 o" _( J* r. r( s* u+ H
dbms_output.put_line(pString);4 |: `$ f( M# D& [9 a
end if;; p7 Z7 |3 W# F N! I; G# J
5 B2 {8 D% d. b6 Bend Print;
T' ]6 ^5 H# K. U! D
' r! p$ x0 g4 h! H& c G4 D" Dprocedure Show
5 h- T5 |; {9 z" T. A(! ?3 Q/ f3 k$ Z" y
pMetaObject in tMetaObject: x3 f+ `0 N/ J9 w! q, ^% ^9 F8 b% K
)) Q5 u' {8 D' T {6 l9 `
is
" t8 M+ D7 m8 O" s& Ubegin o' v3 x$ ^5 r& ~% j) A' d4 ]
dbms_output.put_line(’***’);
$ M1 j8 A' I; ?1 P r! kdbms_output.put_line(’Name: ’ || pMetaObject.aName);5 d' U5 Y+ v1 ?2 H0 k/ @1 m6 F
dbms_output.put_line(’Type: ’ || pMetaObject.aType);
; x T6 ^3 G1 S9 p4 q1 B. Cdbms_output.put_line(’Logging: ’ || pMetaObject.aLogging);
* R5 B; T% t2 p \" ]! M: M1 sdbms_output.put_line(’Parallel: ’ ||) j9 G: ?0 M% m' E3 m
to_char(pMetaObject.aParallel));
: a( G7 M/ D8 O Sdbms_output.put_line(’Status: ’ || pMetaObject.aStatus);
0 U( i: r! G# \2 Gdbms_output.put_line(’Validated: ’ ||
# T5 ] e3 v5 r0 l- j0 h, N _/ N: z3 lpMetaObject.aValidated);
4 M1 c" i d) Bdbms_output.put_line(’Rely: ’ || pMetaObject.aRely);
( D9 Y, o& S- \ L4 [5 nprint(’DDL String: ’ || pMetaObject.aDDLString, 255);6 Q" n; C7 S+ ]; @& E1 y; Y
dbms_output.put_line(’***’);
# F/ t( K0 b" o8 ~- h' z$ ~ A2 a, Tend Show;
: a( e. @! r# m; B& U& A; r
6 ]$ @$ \0 V" @& N* C: cfunction GetDDL
$ P7 G+ B$ t7 ]8 ]- `, x2 m(
1 Y( Y) m9 ]* s% z( Q6 ~" ~ pName in tString,
) C8 b' ~$ H. X$ W$ }# d' z$ ` pType in tString0 |2 m% G @% V( h9 p. l
) i% A0 L" \2 O; p" b
return tLongString
( C! Q S' ^6 S( Xis
# ~ z3 p, N" z2 s8 cvClob clob;
+ f- C) m$ M1 B; C( O' ~
* H% H# }7 W2 L5 W( g4 v3 _- ~ pvLongStrings tArrayLongString;
# v" Q: x6 r. v- r/ U+ `$ [' ?- c
vFullLength pls_integer := 0;" g Z8 Q; r% ?& h: r, h' B" y- O% j
vOffSet pls_integer := 0;( A; \. }* a+ D+ J R0 B$ w; x
vLength pls_integer := 0;
# I3 E$ j. i5 @8 _6 F
9 T$ o% q; _; E. b- k; J4 d8 rbegin
. f e6 T* R# w3 B0 m& D( d9 ~+ u; i, {4 T4 v9 }# B
vClob := dbms_metadata.get_ddl(pType, upper(pName));
6 b8 ?4 J. V4 m4 L9 t! B Y
4 J8 _8 v! ]- JvFullLength := dbms_lob.GetLength(vClob);* o5 C& h- n* B# g
2 A8 ]8 ~7 z- z4 ^1 U9 V8 Mfor nIndex in 1..ceil(vFullLength / 32767) S5 ?# B4 t: n7 A* w4 B
loop
- E# U- n- h6 H6 |2 r vOffSet := vLength + 1;. K8 i4 Q, ~& z( q# M
vLength := least(vFullLength - (nIndex - 1) * 32767, 32767);8 p8 h: D1 w1 U( v: V& w
p; {$ y% B/ n) P) \3 I6 I* o& J- R
dbms_lob.read(vClob, vLength, vOffSet, vLongStrings(nIndex));
. l$ K2 A% I# M( d+ Z/ Y2 t$ @! @; z) X9 E, c+ L6 Y5 K
vLongStrings(nIndex) := replace(vLongStrings(nIndex),
7 G8 Y7 l; K& p, ^ ]- G1 v9 G" p" m3 h cDoubleQuote || user || cDoubleQuote || ’.’,% U# ~7 j7 n: M8 B) t- B0 B
’’);' Y6 K* E& C: Q- m' ^9 D
) `2 e0 ~/ p2 |5 l2 F. ? vLongStrings(nIndex) :=ltrim(rtrim(replace(vLongStrings(nIndex), chr(10), ’’))); $ ^: m/ E7 {! T! g
end loop;
' R, ]1 R/ c) t% U/ T$ [4 W- q* s! O' q% X
if (dbms_lob.isOpen(vClob) > 0)
3 c" y9 m+ |% U% Q4 |6 ~& q/ `- Athen
2 d/ g6 ~) q( P8 ?+ ] dbms_lob.close(vClob);# g, `5 u1 _4 s
end if;" ]$ A0 s0 g4 h7 X; S
- I3 M; r. r: h$ ?- O. z
return vLongStrings(1);
9 X3 @8 c* z5 u! i9 M7 _3 i: v! [& B8 e' ]; g
end GetDDL;
: F1 J z2 E2 _' C) s" Y; O' X. ?$ h2 b j8 a- H
function ObjectExists
& n7 ~, a/ Y; Y# e(
4 x( x" `/ B) Y; w pObjectName in tString,/ q7 {4 Q$ ]% P5 J* Q
pObjectType in tString,$ F; T! l8 v! L8 g
pTableName in tString := null2 d" g; h! n$ b2 x
)# s' S. z9 `( W; U" c- u
return boolean Z8 U8 ^6 G- D# v: M U" e7 A; K
is; _+ P7 R0 _7 b0 p) ]5 X' B
# p, e* b* h& @9 e& O( _
vCount pls_integer := 0;
) a9 d0 p' {# A qvObjectName tString := ltrim(rtrim(pObjectName));
% w4 D' e# r9 G; l; L9 K- D TvObjectType tString := upper(ltrim(rtrim(pObjectType)));
2 h+ A6 C1 w7 {& E: X; ] z" mvTableName tString := upper(ltrim(rtrim(pTableName)));
( |. C8 S' f7 D. q8 Q# C
: v, } L! k( v6 q" Pbegin& A' j% K; G7 s; A* ]6 G
& X u# I. B9 S% [* Y3 b! Mcase! a0 X: A/ s/ @" l, K) a Q9 e
when vObjectType = cColumn
6 N3 @& I0 m% }- G) P! e& ]8 I9 ` then' V9 |$ e" t: ]+ q2 N9 E# v
select count(*)$ E3 `! b7 W& i
into vCount% ^" z' J0 j- m- m" o0 j
from Dual
9 a2 J8 d, t9 g, G6 O where exists (select ’1’1 k7 ~# J/ c ~% X
from user_tab_columns% T2 b. |& l) V0 c% M1 K
where column_name = upper(vObjectName)8 F# R+ r! Y4 q) o: p
and table_name = vTableName);
9 s+ A' t5 R8 E8 O4 n! T: g1 K8 g8 H5 E: a/ \) `
when vObjectType = cConstraint
" i* O/ Q5 w2 N; |6 P5 Z then) [7 h$ n* a0 `
select count(*)
, l- | S9 o+ F* V# q into vCount( Q; N/ @5 F# O1 [$ k
from Dual
* F, {$ K) A7 R/ I3 y& y& W where exists (select ’1’+ x; K) A9 }# }
from user_constraints2 \8 U2 W% G, B9 H3 m$ d
where constraint_name = upper(vObjectName)
% q% l0 D! L! l/ J) R& W and table_name = vTableName);
- e! W4 T# e' ?+ T
1 }, \; n% R6 E$ p5 p8 mwhen vObjectType in (cJavaSource, cJavaClass)0 C4 ^- S# f5 L0 w: N7 n
then# D2 ^1 x6 E8 Q5 [. N
select count(*)) o5 j9 `+ N ~: V% N/ W3 Z! c' P- U
into vCount
A( Z7 Q9 G& P- k( s s: p9 ]# g from Dual
. S5 U+ b& _7 X5 G* C7 _ where exists (select ’1’' L; J1 W9 `; X5 u
from user_objects. U0 b: @3 Q6 h$ j
where object_name = vObjectName and object_type = vObjectType);' {& k- }1 _ `# p5 G1 O
8 M8 f4 f' y: N j3 z else7 r. I& e3 X: a+ ~; Q9 U4 ?" j
select count(*)0 s# l% Z) n! f6 F& M5 E
into vCount9 l M. N* E; c3 h5 o" D. M# y
from Dual+ G4 o) ?. w0 K& p" ^
where exists (select ’1’- P' {" B* O8 q* q2 Z! q9 M
from user_objects- v T! ~% R; E2 T+ c
where object_name = upper(pObjectName)" z4 T: ~' q7 `" h
and object_type = vObjectType);
- p T( T2 J" M* C. g end case;1 e3 t# Z. R! X! ~% M
1 N' ~. ~. e! w
return (vCount > 0);8 {) B: t' j" s6 S8 t
+ A% S7 ~2 z- Z# xend ObjectExists;
/ Z, o$ J" C' ]3 _6 z' b
) @3 f$ f& M& z7 D' w1 a+ t8 Mprocedure Load
7 t0 m# J+ d1 f) x(
4 {- N. p* g4 X J7 N' v# C pTable in tString,
! C: W/ Q0 v8 }5 A) u/ w pForce in boolean := false- \. p& l( `1 a
)9 g/ ^8 [* K. d0 y
is+ [% [, N% D7 [
5 [- A3 [" F9 B3 `, GvFullMetaObject tFullMetaObject;4 D% [; p" `4 |! e9 Z' N
/ G2 X- @5 Z+ K# O; R( W3 {vTable tString := upper(ltrim(rtrim(pTable)));0 K, K- { N4 i3 ~
vCount pls_integer := 0;, a, i# ?1 q/ F3 d9 u7 P
* e& r, l" q( P# Jbegin
+ c+ v& Z6 U" B3 R: a; \
; \% f" B8 t" O4 s3 jif (not vMetaData.exists(vTable) or nvl(pForce, false))2 R" }6 t5 c5 m6 ^
then0 n: J7 K1 y% k9 i9 w
if not ObjectExists(pTable, cTable)
& r/ W! Q& ]) Y5 I% E |3 o. G C then
. J1 S, Z% u0 Z. T raise_application_error(-20500,
9 z8 _. ~' l0 t) R ’Unable to load metadata for ’ || nvl(pTable, ’NULL’) ||
0 O% g& [4 Q# U5 h ’. ’ || ’Table does not exist.’$ R9 ^. {) w& l3 [1 N' s* ]& j
);
3 G& X* G) z* }1 yend if; 7 |& ]* s& a4 |
8 [# V& v) R% n/ w Jfor rec in (select table_name,# P4 a: c p5 U/ U
logging,
# g3 Q( X2 P9 v/ Jltrim(rtrim(degree)) as degree,% i6 @8 O( l. ^+ T$ h
partitioned,
1 R/ h( y. x: a8 c2 Sbacked_up7 \* f) {5 s8 h/ v2 P
from user_tables
E9 j8 R/ k8 I" ewhere table_name = vTable)8 h: T3 n( z& Y( ~5 Z/ y1 w
loop4 A9 s7 E0 y& p+ E# E* H }1 W0 ^4 S2 E
* y% e3 ^& ]& |: U9 P$ m& D
vFullMetaObject.aTable.aName := rec.table_name;
* O, S1 {8 \+ z) M vFullMetaObject.aTable.aType := rec.partitioned;! u& w2 a# e2 H! k) j$ y$ W
vFullMetaObject.aTable.aLogging := rec.logging;
/ F6 B: Z& P& y; |$ G' p vFullMetaObject.aTable.aParallel := ltrim(rtrim(rec.degree));% ]/ g t4 _, h6 i! l
vFullMetaObject.aTable.aStatus := rec.backed_up;
/ s# Q" e: k/ S* d+ Y( ?vFullMetaObject.aTable.aDDLString := GetDDL(rec.table_name, cTable);6 x1 R( o" O( L- p
end loop;
, \$ G( c% P1 ]4 |: @# q7 D7 i& f @9 V( [" F" p) H+ O8 s
for rec in (select index_name,9 F- _% F H9 T* `
uniqueness,& s! |7 f) |0 g7 V" j7 C
logging,
9 Q& T4 f" \7 R, S1 A3 ?: rltrim(rtrim(degree)) as degree,7 n" w C4 n, _9 i1 k
status
2 m4 V1 E3 x3 |1 i( }/ P9 l0 p1 V- bfrom user_indexes
$ e( t7 l S1 n9 J5 ^# ]where table_name = vTable
7 I5 W( ]5 O* jand index_type != cLobType)
+ R* J% d+ |& {) ]3 M3 M" V- N0 Iloop, f3 `$ Y2 Q2 J$ J# e
, G* l. t; W+ n
vCount := vCount + 1;, j- ` \+ M+ R4 @2 \- Q% N
vFullMetaObject.aIndexes(vCount).aName := rec.index_name;
2 r. i0 @& S0 }9 P/ c" jvFullMetaObject.aIndexes(vCount).aType := rec.uniqueness;' u' u: ^, z) x# j6 Z/ |* d$ Q% o
vFullMetaObject.aIndexes(vCount).aLogging := rec.logging;
, \8 F0 U3 I7 ^$ v+ E( O, a+ avFullMetaObject.aIndexes(vCount).aParallel :=
& J2 P( |, k2 j4 k! h, {ltrim(rtrim(rec.degree));2 `2 o4 s f5 L0 E
vFullMetaObject.aIndexes(vCount).aStatus := rec.status;$ h, h- ]( R1 ^5 h, F
vFullMetaObject.aIndexes(vCount).aDDLString :=7 p( C7 F3 U) v. Q' f
GetDDL(rec.index_name, cIndex);
5 v1 b1 O% g, X5 u. _0 I0 S5 g
d' T: m6 @; l U7 u; R; Oend loop;/ q% g( R, ?5 _5 j
0 k4 q( [& j$ G0 O( `1 `; P
vCount := 0;& m2 W4 u) z4 Q8 Z, j, I! G- g! A
for rec in (select constraint_name,constraint_type,status,search_condition,validated,rely from user_constraints where table_name = vTable7 y$ M0 M# p( T1 a
order by decode(constraint_type,0 E6 c" Y, _% Y/ t( q
cPKConsType, 10,5 l! V% ^) c) I7 B4 n
cUNConsType, 20,% Q/ S3 i) @( G7 f5 w
cFKConsType, 30, Q: e, ]" R! v& R" t& X0 k( B
cCKConsType, 40,
5 J+ a' Q3 _$ c% y# J 100),7 l6 j! s& E3 v+ X
constraint_name)
: [/ M8 I! `1 M0 P8 Q% Y; w1 }loop 9 I% E. x* t( ^
vCount := vCount + 1;! H2 q' q5 `9 X* {
vFullMetaObject.aConstraints(vCount).aName := rec.constraint_name;
4 N9 B1 X4 m% L* K+ U4 }5 s vFullMetaObject.aConstraints(vCount).aType := rec.constraint_type;
/ T, C" E* X, [4 K9 o6 Q+ J vFullMetaObject.aConstraints(vCount).aLogging := null;
# L2 }- q7 ~' f! w vFullMetaObject.aConstraints(vCount).aParallel := null;
& n) ]6 T& s8 @5 [ vFullMetaObject.aConstraints(vCount).aStatus := rec.status;
3 |9 i; ]1 z7 x- I, r vFullMetaObject.aConstraints(vCount).aValidated := rec.validated;, ]! ~8 C! n/ A& _
vFullMetaObject.aConstraints(vCount).aRely := rec.rely;; g* q4 `! d7 q7 ^4 U
. V7 I3 S2 \ P, \
if substr(rec.constraint_name, 1, length(cSYSPrefix)) = cSYSPrefix and
, x+ u: y4 f" C4 u; v upper(rec.search_condition) like ’%IS ’ || cNotNull || ’%’
% V" j, U/ G' C3 N) f3 T9 z6 ? then
M5 Q6 w( i" }1 M, G vFullMetaObject.aConstraints(vCount).aDDLString := 0 `: ~! M0 r1 U. v2 R _
’ALTER TABLE ’ || cDoubleQuote || vFullMetaObject.aTable.aName || cDoubleQuote || ’ ’ ||( f# ^8 R1 C* @9 V: U3 D9 S G
’MODIFY ’ || replace(rec.search_condition, ’IS ’ || cNotNull, cNotNull) ||
) ]" \$ Q! k* D0 y (case when vFullMetaObject.aConstraints(vCount).aValidated = cNotValidated/ ]9 f% |7 E7 y" B% d4 a
then ’ ’ || cNovalidate
2 R5 o8 G! V& _1 Q+ L I# D: K% s else ’’end);
, I+ h7 j* n7 A+ f1 H' u. p else
; D* v6 M/ K/ R/ f5 z vFullMetaObject.aConstraints(vCount).aDDLString := GetDDL(rec.constraint_name, cConstraint);
1 c1 P" o0 l. t5 o$ w E. t end if;
! T* a( d& j: _. C" ?# `' @end loop;
% R9 G4 k0 |* n$ }! A8 \
' k$ h/ C# `% o+ P: YSetMeta(pTable, vFullMetaObject); {0 m8 H# t4 t# O
end if;( ]0 ]) ~! n+ q* G5 ~! h
end Load;: p9 }+ G- y/ X- z- }
" j, ?: A) F5 N% H% K$ M6 U
procedure Reset
( q, ?6 a+ z. X F(
# Q# u+ x* W$ d1 \* u* \ pTable in tString
1 j( w* _& q+ x* p. E5 w& c2 `)7 y) x* ]3 W/ \+ y7 k
is
" {: c; B9 l# h9 H$ ]0 ^- W1 Y! Zbegin7 R+ p v* I5 `, K4 u
, _% F/ r+ z# P" ~& z3 U. L/ ]
vMetaData.delete(pTable);; r7 w5 W* J: D
7 X" n/ X" _* E$ x" c
end Reset;
+ C( c% W2 M4 j- W3 s$ l& q4 i2 z% ?9 \
/ I% A9 S; y, X. G, c" M/ }procedure Reset# L" ^! q; l6 t! C8 D
is& s: d B1 y! H" d! t
begin* j% N0 Q" I! Y" `8 M( G1 d
vMetaData.delete;4 e' Z1 x3 p& }* k/ ^. w
end Reset;
6 Y9 h# t6 S5 N8 x8 m! B! s0 I
) M1 K5 _) o$ \4 b- R; pfunction GetMeta
8 z# ?1 m. C$ ` v2 _1 S( _! H(( `% }; ~! v8 w' f/ w3 M
pTable in tString,
0 a1 x. |. h& [ pForce in boolean := false
. e# h) u! e1 `7 n1 H8 W)0 v: H# ?$ a3 v$ T
return tFullMetaObject
! @6 {. F* W: j0 lis
; |* c$ y z& N; N7 O. @begin1 t }- ?- D) y' n
: P; W& u9 G( W3 z4 R2 \if (not vMetaData.exists(pTable) or nvl(pForce, false))
7 m$ c: f8 c" x0 H1 cthen
4 }: N& Z5 ]: W$ t Load(pTable, pForce);8 E9 p0 O. S8 }9 @* C
0 [ A8 j6 A @# u5 e if not vMetaData.exists(pTable)
. V- {* t Q" |: P$ U4 v then
4 n% H* i! k5 Q0 \ raise_application_error(-20501, ’Unable to find metadata for ’ || pTable || ’ in repository.’);
) z$ J( M. I$ y8 d, u2 a4 L end if;' K) _/ C( S. t. A9 @& k$ k
end if;
9 q" j; E5 M+ B A1 m6 A$ ?/ Q. _# P& D
return vMetaData(pTable);
" t* [" C u5 s9 E; ~6 v/ g4 r! Z
# q+ |. b4 i" ?; Oend GetMeta;1 m2 }+ P; A& Z9 H' x9 J
, Z9 o$ L; P) Nfunction GetMeta, A6 U) Q* Z/ @3 [/ e, v
return tArrayFullMetaObjectByString* a6 n7 o8 q8 \! R- f) E y
is
! ~8 W+ ?' o$ S! ] X( sbegin
" q; z/ R" q) @2 w, T7 O8 d2 c6 p1 [0 x2 ]
return vMetaData;
3 b. J& F! x, {8 z2 C+ [- o) V
, w% L1 {- i, z, R# @6 Gend GetMeta;
3 n8 T) w5 f1 z& ^ |: B2 `
- I2 q' J# o3 f& ?/ Pprocedure SetMeta
$ s- u+ n1 w; N# ^(
0 ^* P0 ~2 P' q. A" M9 l0 k5 u5 h pTable in tString,5 D6 t. R5 t8 w3 S
pFullMetaObject in tFullMetaObject+ j+ x. G/ u9 ]+ t) E
)
* }! ?3 Y3 Q. @is6 T8 y- K! R+ n# s: p
begin; M; q' l( I! g
3 I1 Y) J) w3 C6 ~; T) S
vMetaData(pTable) := pFullMetaObject;. u" b. H. c6 g# |
' r, H& L+ S9 [
end SetMeta;) J& C& {! i$ H1 m' R/ R8 [9 q( w, a
) ]- g) z% e& ^' p! a- c8 Mprocedure SetMeta$ w0 Z5 I' N. m7 `* Q* F: G
(0 X4 s: J$ X# c9 J3 J% f" [
pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
( v! y( a1 q+ z5 V5 W)
3 r, Y0 Z# ]( |- J/ m) Dis
) L& O: u1 A8 p* @* hbegin6 m5 W5 y7 `; Z6 i0 L
4 Y8 Z; d8 C s0 T) b$ W0 n1 ^vMetaData := pArrayFullMetaObjectByString;3 j. W, y) K. n0 O+ m
7 ]" W3 d+ e% j' Y; R. S8 e
end SetMeta;1 _6 J. z6 w0 @1 f1 q/ k: l8 t
. L3 ~# d, b* _" ]procedure Show
6 d1 S7 ^ u5 j, J8 W(
3 y, {& p2 ?% l- v% R6 | pTable in tString
; v. ]% A& r) B" N% ~$ O% g)
8 M8 c' \3 i# C" `5 Ris
4 M( U+ b: E2 e, _! Y
9 B& \6 O1 J& ~9 U2 d1 m* o: RvFullMetaObject tFullMetaObject;; v' g7 ~3 X0 j2 J0 t& {
! X8 \- K1 n3 O9 Ubegin8 ]0 J" M8 j- ^
8 }1 l' \* R; W- N7 {* A" e% L
if (vMetaData.exists(pTable))8 [- E! Q4 {* _/ [+ t4 a9 W
then8 J) D& M* t; }0 B# U9 J
dbms_output.enable(1000000);* ~$ k& H% L& a4 G
0 c6 D& x$ c! B5 q* J& q, R/ I
vFullMetaObject := vMetaData(pTable);/ ^% Z: [& r" r: b* P; F
dbms_output.put_line(’Start Full Object: ’ || pTable);1 ^2 i8 ]- P" d# q; X
dbms_output.put_line(’Start Table: ’ || pTable);
, ~5 z& N1 l _. T3 w3 }8 v2 JShow(vFullMetaObject.aTable);
" S+ \# v8 A$ [5 I7 Kdbms_output.put_line(’Finish Table: ’ || pTable);
: \* k- p, u* c) j- v5 c4 p* P
3 o# Z; h9 \0 h8 G- G2 idbms_output.put_line(’Start Indexes: ’ || pTable);1 M- {2 G' R/ S/ [5 u
if (vFullMetaObject.aIndexes.count > 0)
- v) M) j- `4 U" i: _$ y( W7 Ithen5 [- K! n; d1 C2 O7 t/ S8 M, Y
for nIndex in vFullMetaObject.aIndexes.first..vFullMetaObject.aIndexes.last; p. f% t, K% i8 _: P
loop
/ V4 a4 _4 I- l7 oShow(vFullMetaObject.aIndexes(nIndex));
+ F8 n1 s: |7 r9 _3 Gend loop;: |/ C& v8 t5 I
end if;
" y* n8 f- R; t0 x" idbms_output.put_line(’Finish Indexes: ’ || pTable);
" N9 d3 P6 K- a% F6 P+ d! {dbms_output.put_line(’Start Constraints: ’ || pTable); ~' \0 x* @: H( \: S/ T$ h
if (vFullMetaObject.aConstraints.count > 0)
& G- p& X; M6 b5 M2 t4 [. Wthen W$ W* }/ A( W q. Q/ a
for nIndex in vFullMetaObject.aConstraints.first..vFullMetaObject.aConstraints.last: z' n- c7 D i) |
loop
& h; G( r- ~3 D& P2 ^" R uShow(vFullMetaObject.aConstraints(nIndex));! C" i! T0 v1 G2 ^/ i$ Y, U/ J; c
end loop;, M' ]7 d& s5 K# a
end if;: p! k$ Y9 q( g
dbms_output.put_line(’Finish Constraints: ’ || pTable);
6 a* d" h/ h$ f5 H8 Ldbms_output.put_line(’Start Triggers: ’ || pTable);
' B9 A. \3 _$ }# ?& kif (vFullMetaObject.aTriggers.count > 0)
8 R( c1 a9 P, P" D U- U9 Athen- \. k" K1 K. ~- N: |9 R
for nIndex in vFullMetaObject.aTriggers.first..vFullMetaObject.aTriggers.last( A. }" [# X4 k1 D
loop
6 S' C8 d3 R$ x7 u- vShow(vFullMetaObject.aTriggers(nIndex));
5 R4 w( w% R. ^end loop;# \) c0 q: _0 y: i7 H
end if;9 J7 D, S+ W, W
dbms_output.put_line(’Finish Triggers: ’ || pTable); . [0 h; t% ]' `8 S H4 n
dbms_output.put_line(’Finish Full Object: ’ || pTable);
% q; I1 i0 s g5 Cend if;" Y2 B! F, m8 p- O7 Q
end Show; / U3 h/ C0 I" s( c1 b7 s
1 x- ~% i( u1 c% K+ j9 ~3 i
procedure Show
, D) l5 ~2 P) g. U3 dis
# s* f. Q2 w7 V( gvTable tString;
+ u$ G6 u8 e: \! j& Jbegin
( i" W6 ?* T( I! a# p1 t if vMetaData.count > 0
/ I6 k( B$ n* {% u/ u then
7 H1 q4 f8 f+ g$ C" S dbms_output.put_line(’Total Meta Objects: ’ || to_char(vMetaData.count));
, `* Y. k. N1 y6 n9 s vTable := vMetaData.first;
$ f! d, ~# u% T8 P9 b while (vTable is not null)2 d7 ?2 k! |0 y' k
loop
5 Q4 g. n. S8 m( N Show(vTable);
& {0 D m. o# P% I, b vTable := vMetaData.next(vTable);
) n8 B) o! Z) e, M end loop; ! p+ a! p0 N& u* V: l5 o# U3 C# _
end if;# a: H0 y$ B% d+ e) m6 O
end Show;
. K* A& i. ^; C- d# N
2 P2 V7 h; e/ ?4 A& a7 A! ^% hbegin
+ B5 ~# ]( Y; dSetEnvironment; $ X; E$ ?/ g% v
end MetaDataPkg; |