CREATE OR REPLACE PACKAGE BW_SIMPLE_GEN AS
--------------------------------------------------------------
--Purpose: Module generates package body based on XML --
-- specification of mapping stored as comment --
-- in package specification. --
--Author: Bob Jankovsky, copyleft 2007 --
--Version: 1.0 /19-OCT-2007 --
--Exceptions: 20006 ... Unsuccessfully compiled module --
--------------------------------------------------------------
Procedure GenPackageBody(
p_Module in VARCHAR2 -- package name
);
END BW_SIMPLE_GEN;
/
CREATE OR REPLACE PACKAGE BODY BW_SIMPLE_GEN AS
-------------------------------------------------------------
Procedure GenPackageBody(
p_Module in VARCHAR2 -- package name
)is
TYPE TAE is table of Varchar2(1000) index by Varchar2(100);
TYPE TAI is table of Varchar2(2000) index by binary_integer;
va_expr TAE;
c_StdSourcePrefix Varchar2(10):='SRC$$';
c_StdTargetPrefix Varchar2(10):='TRG$$';
v_xml XMLTYPE;
v_Delim1 Varchar2(30);
v_isDBLink Boolean:=false;
va_expr_replace TAE;
va_spec_functions TAI;
va_spec_funcnames TAI;
Cursor c_mod_attrs is SELECT * FROM XMLTABLE
( '/' PASSING v_xml
COLUMNS
ORD for ordinality
,Pattern Varchar2(100) PATH '@Pattern'
,TargetOwner Varchar2(100) PATH '@TargetOwner'
,TargetTable Varchar2(100) PATH '@TargetTable'
,TargetLink Varchar2(100) PATH '@TargetLink'
,TargetPartition Varchar2(100) PATH '@TargetPartition'
,TargetHint Varchar2(100) PATH '@TargetHint'
,SourceHint Varchar2(100) PATH '@SourceHint'
);
Cursor c_expr is SELECT * FROM XMLTABLE
( '//EXPR' PASSING v_xml
COLUMNS
Name Varchar2(100) PATH '@Name'
,Expr Varchar2(1000)PATH 'text()'
);
Cursor c_constant is SELECT * FROM XMLTABLE
( '//CONSTANT' PASSING v_xml
COLUMNS
Name Varchar2(100) PATH '@Name'
,Type Varchar2(100) PATH '@Type'
,Value Varchar2(100) PATH '@Value'
,Query Varchar2(2000)PATH 'text()'
);
Cursor c_premappings is SELECT * FROM XMLTABLE
( '//PREMAPPING' PASSING v_xml
COLUMNS
Name Varchar2(500) PATH '@Comment'
,Code Varchar2(2000)PATH 'text()'
);
Cursor c_postmappings is SELECT * FROM XMLTABLE
( '//POSTMAPPING' PASSING v_xml
COLUMNS
Name Varchar2(500) PATH '@Comment'
,Code Varchar2(2000)PATH 'text()'
);
Cursor c_execConditions is SELECT * FROM XMLTABLE
( '//EXECCONDITION' PASSING v_xml
COLUMNS
Name Varchar2(500) PATH '@Comment'
,Code Varchar2(2000)PATH 'text()'
);
Cursor c_loop is SELECT * FROM XMLTABLE ('//LOOP' PASSING v_xml);
Cursor c_loopVar (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '/*/VAR' PASSING p_xml
COLUMNS
Name Varchar2(500) PATH '@Name'
,Value Varchar2(2000)PATH 'text()'
);
Cursor c_srctab is SELECT * FROM XMLTABLE('//SRCTAB' PASSING v_xml);
Cursor c_tab_attrs (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '/' PASSING p_xml
COLUMNS
ORD for ordinality
,Alias Varchar2(100) PATH '@Alias'
,Owner Varchar2(100) PATH '@Owner'
,Name Varchar2(100) PATH '@Name'
,Link Varchar2(100) PATH '@Link'
,Partition Varchar2(100) PATH '@Partition'
,JoinType Varchar2(100) PATH '@Type'
,EnclFlag Varchar2(100) PATH '@EncloseFlag'
,NoIndex Varchar2(100) PATH '@NoIndex'
);
Cursor c_tab_attrs2 (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '/' PASSING p_xml
COLUMNS
ORD for ordinality
,Alias Varchar2(100) PATH '@Alias'
,Owner Varchar2(100) PATH '@Owner'
,Name Varchar2(100) PATH '@Name'
,Link Varchar2(100) PATH '@Link'
,Partition Varchar2(100) PATH '@Partition'
,JoinType Varchar2(100) PATH '@Type'
,EnclFlag Varchar2(100) PATH '@EncloseFlag'
,NoIndex Varchar2(100) PATH '@NoIndex'
);
Cursor c_tab_on (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '/*/ON' PASSING p_xml
COLUMNS
ORD for ordinality
,COND Varchar2(2000) PATH 'text()'
);
Cursor c_tab_where (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '//WHERE' PASSING p_xml
COLUMNS
ORD for ordinality
,COND Varchar2(2000) PATH 'text()'
);
Cursor c_tab_dedup_part (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '//DEDUP_PARTITION' PASSING p_xml
COLUMNS
ORD for ordinality
,COND Varchar2(2000) PATH 'text()'
);
Cursor c_tab_dedup_ord (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '//DEDUP_ORDER' PASSING p_xml
COLUMNS
ORD for ordinality
,COND Varchar2(2000) PATH 'text()'
);
Cursor c_tab_agg (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '//AGG' PASSING p_xml
COLUMNS
COLUMN_NAME Varchar2(1000) PATH '@ColumnName'
,AGG_FUNCTION Varchar2(100) PATH '@AggFunction'
,DistinctFlag Varchar2(10) PATH '@DistinctFlag'
,Alias Varchar2(100) PATH '@Alias'
);
Cursor c_tab_subjoin (p_xml XMLTYPE)is SELECT * FROM XMLTABLE ( '//SUBJOIN' PASSING p_xml);
Cursor c_subjoin_col (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
( '//COL' PASSING p_xml
COLUMNS
COLUMN_NAME Varchar2(100) PATH '@ColumnName'
,COLUMN_ALIAS Varchar2(100) PATH '@ColumnAlias'
);
Cursor c_filter is SELECT * FROM XMLTABLE
( '//FILTER' PASSING v_xml
COLUMNS
Type Varchar2(100) PATH '@Type'
,Cond Varchar2(2000) PATH 'text()'
);
Cursor c_colmap is SELECT * FROM XMLTABLE
( '//COLMAP' PASSING v_xml
COLUMNS
ORD for ordinality
,SourceAlias Varchar2(100) PATH '@SourceAlias'
,SourceColumn Varchar2(100) PATH '@SourceColumn'
,SourceExpression Varchar2(2000)PATH 'text()'
,TargetColumn Varchar2(100) PATH '@TargetColumn'
,MatchFlag Varchar2(10) PATH '@MatchFlag'
,UpdateFlag Varchar2(10) PATH '@UpdateFlag'
,DirectFlag Varchar2(10) PATH '@DirectFlag'
,DummyFlag Varchar2(10) PATH '@DummyFlag'
,PartFlag Varchar2(10) PATH '@PartFlag'
,ValidFromFlag Varchar2(10) PATH '@ValidFromFlag'
,ValidToFlag Varchar2(10) PATH '@ValidToFlag'
,NullableFlag Varchar2(10) PATH '@NullableFlag'
,Expr Varchar2(100) PATH '@Expr'
,UpdateExpr Varchar2(100) PATH '@UpdateExpr'
,InsertExpr Varchar2(100) PATH '@InsertExpr'
);
-------------------------------------------------------------------------------------------------------
Procedure fillXML is
v_is_metadata Boolean:=false;
v_is_xml Boolean:=false;
v_Str Varchar2(32767);
Begin
for r1 in (
Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
)LOOP
if v_is_xml then --witin XML code
if r1.TEXT like '%*/%' then --resolve end
v_is_xml:=false;
r1.TEXT:=regexp_replace(r1.TEXT,'(^.*)([*][/])(.*$)','\1');
end if;
if r1.TEXT not like '#%' then
v_Str:=concat(v_str,r1.TEXT);
end if;
elsif upper(replace(r1.TEXT,' ')) like '/*METADATA%' then --find tag METADATA:
v_is_metadata:=true;
elsif v_is_metadata and upper(replace(r1.TEXT,' ')) like '<?XML%?>%' then --resolve start of XML
v_is_xml:=true;
v_Str:=r1.TEXT;
end if;
End LOOP;
v_xml:=XMLTYPE.CREATEXML(v_Str);
for r1 in c_expr LOOP
va_expr(r1.Name):=r1.Expr;
end LOOP;
End fillXML;
-------------------------------------------------------------------------------------------------------
Function translateDBLink(p Varchar2) return Varchar2 is
v_oldL Varchar2(100):=regexp_replace(regexp_substr(p,'@\w+(\W|$)'),'\W','',1,0);
v_newL Varchar2(100);
Begin
if v_oldL is not null then
v_isDBLink:=true;
end if;
v_newL:= v_oldL; --possible translation
return regexp_replace(p,'(@'||v_oldL||')(\W|$)','@'||v_newL||'\2');
End;
-------------------------------------------------------------------------------------------------------
Function getTableQual(p_name Varchar2,p_owner Varchar2:=null,p_link Varchar2:=null,p_partition Varchar2:=null) return Varchar2 is
v_partition_suffix Varchar2(100):=case when p_partition is null then '' else ' PARTITION('||p_partition||')' end;
Begin
return translateDBLink(rtrim(ltrim(p_owner||'.'||p_name,'.')||'@'||p_link,'@')||v_partition_suffix);
End getTableQual;
-------------------------------------------------------------------------------------------------------
Function dedupList(p_list Varchar2) return Varchar2 is
v_word Varchar2(30);
i pls_integer:=1;
v_res Varchar2(8000):=p_list;
v_res_old Varchar2(8000);
Begin
LOOP
v_word:=regexp_replace(regexp_substr(v_res,'(^|\W)\w+($|\W)',1,i),'\W');
exit when v_word is null;
v_res_old:=v_res;
v_res:=regexp_replace(v_res,'(^|\W+)('||v_word||')(\W+'||v_word||')($|\W)','\1\2\4');
v_res:=regexp_replace(v_res,'(^|[[:punct:]])(\W*)('||v_word||')($|\W)','\4',1,2);
if v_res=v_res_old then
i:=i+1;
end if;
end LOOP;
return v_res;
End dedupList;
-------------------------------------------------------------------------------------------------------
Function getDedupAttrList(P_alias Varchar2, P_srctabXml XMLTYPE) return Varchar2 is
v_res Varchar2(8000):='';
v_delim Varchar2(20):='';
Function getList (p Varchar2) return Varchar2 is
v_str Varchar2(2000):=p;
Begin
for i in 1..2 LOOP
v_str:=regexp_replace(v_str,'(\W|^)('||p_alias||'[.])(\w+)(\W|$)','\1~\3;\4',1,0,'i');
end LOOP;
if v_str!=p then
v_str:=regexp_replace(v_str,';[^~]*~',', ',1,0);
v_str:=regexp_replace(v_str,'^[^~]*~','',1,0);
v_str:=regexp_replace(v_str,';[^;]*$','',1,0);
v_str:=concat(v_delim,v_str);
v_delim:=', ';
else
v_str:='';
end if;
return v_str;
End getList;
Begin
for r1 in c_colmap LOOP
if r1.SourceAlias=P_ALIAS and r1.SourceColumn is not null then
v_res:=concat(v_res,v_delim||r1.SourceColumn);
v_delim:=', ';
elsif r1.SourceExpression is not null then
v_res:=concat(v_res,getList(r1.SourceExpression));
end if;
End LOOP;
for r1 in (SELECT * FROM XMLTABLE ( '/*/*/ON' PASSING v_Xml COLUMNS Cond Varchar2(100) PATH 'text()'))LOOP
v_res:=concat(v_res,getList(r1.cond));
end LOOP;
for r1 in c_filter LOOP
v_res:=concat(v_res,getList(r1.cond));
end LOOP;
return deduplist(v_res);
End getDedupAttrList;
-------------------------------------------------------------------------------------------------------
Function getSourceExpression(r1 c_colmap%rowtype) return Varchar2 is
v_express Varchar2(2000);
Begin
if r1.SourceAlias is not null and r1.SourceColumn is not null then
v_express:=r1.SourceAlias||'.'||r1.SourceColumn;
else
v_express:=translateDBLink(r1.SourceExpression);
end if;
if r1.Expr is not null then
v_express:=replace(va_expr(r1.Expr),':1',v_express);
end if;
return v_express;
End getSourceExpression;
-------------------------------------------------------------------------------------------------------
Function getGeneratedSourceHints return Varchar2 is
v_express Varchar2(8000):='';
Begin
for r1 in c_srctab LOOP
for r2 in c_tab_attrs(r1.COLUMN_VALUE) LOOP
if NVL(r2.NoIndex,'N')='Y' then
v_express:=concat(v_express,'NO_INDEX('||r2.Alias||') ');
end if;
end LOOP;--r2
end LOOP;--r1
return v_express;
End getGeneratedSourceHints;
-------------------------------------------------------------------------------------------------------
Function isEnclosed(p_SRCTAB XMLTYPE, p_mask Varchar2:='%' ) return boolean is
Begin
for r2 in c_tab_attrs2(p_SRCTAB) LOOP
if (nvl(r2.EnclFlag,'N')='Y') and ('ENCL' like p_mask) then
return true;
end if;
for r3 in (SELECT * FROM XMLTABLE( '//AGG | //DEDUP_PARTITION | //DEDUP_ORDER | //SUBJOIN' PASSING p_SRCTAB)) LOOP
if r3.COLUMN_VALUE.getRootElement() like p_mask then
return true;
end if;
end LOOP;--r3
end LOOP;--r2
return false;
End isEnclosed;
-------------------------------------------------------------------------------------------------------
-- STATEMENT PARTS PART
-------------------------------------------------------------------------------------------------------
Procedure addSelectPart (P_Source_hint Varchar2:=null)is
v_delim Varchar2(30):=' ';
v_Source_hint varchar2(8000):=P_Source_hint;
Begin
for r1 in c_srctab LOOP
if isEnclosed(r1.COLUMN_VALUE) then
for r2 in c_tab_attrs2(r1.COLUMN_VALUE) LOOP
v_Source_hint:=regexp_replace(v_Source_hint,'(\W)('||r2.alias||')(\W)','\1@'||r2.alias||' '||r2.alias||'\3');
end LOOP; --r2
end if;
end LOOP; --r1
BW_BUFFER.AddLN('SELECT'||replace(' /*+'||v_Source_hint||'*/',' /*+*/'));
BW_BUFFER.IND(3);
for r1 in c_COLMAP LOOP
if NVL(r1.DirectFlag,'N')!='Y' then
BW_BUFFER.AddLN(v_delim||getSourceExpression(r1)||' as '||r1.TargetColumn);
v_delim:=',';
end if;
end LOOP;
BW_BUFFER.IND(-3);
End addSelectPart;
-------------------------------------------------------------------------------------------------------
Procedure addFromPart is
v_Delim1 Varchar2(30);
v_AnyEncl boolean;
v_Dedup boolean;
v_Agg boolean;
v_Encl boolean;
v_Subjoin boolean;
v_Dedup_part Varchar2(4000);
v_Dedup_ord Varchar2(4000);
v_AttrList Varchar2(8000);
v_GrpList Varchar2(8000);
v_Hint Varchar2(2000):=null;
Procedure QualifyList (P_list IN OUT Varchar2,P_alias Varchar2) is
Begin
for i in 1..2 LOOP
P_List:=regexp_replace(P_List,'(^|[,(]|distinct)(\s*)(\w+)(\s*)([,)]|\s+[Aa][Ss]|$)','\1\2'||P_ALIAS||'.\3\4\5');
end LOOP;
End QualifyList;
Begin
for r1 in c_srctab LOOP
v_AnyEncl :=isEnclosed(r1.COLUMN_VALUE);
v_Dedup :=false;
v_Agg :=isEnclosed(r1.COLUMN_VALUE,'AGG');
v_Encl :=isEnclosed(r1.COLUMN_VALUE,'ENCL');
v_Subjoin :=isEnclosed(r1.COLUMN_VALUE,'SUBJOIN');
v_Dedup_part:='';
v_Dedup_ord :='';
for r2 in c_tab_attrs(r1.COLUMN_VALUE) LOOP
--BW_BUFFER.AddLN('--Source table : '||r2.Name);
v_Encl:=isEnclosed(r1.COLUMN_VALUE,'ENCL');
for r3 in c_tab_dedup_part(r1.COLUMN_VALUE) LOOP
v_dedup:=true;
v_dedup_part:='partition by '||r3.cond;
end LOOP;--r3 --dedup partition
for r3 in c_tab_dedup_ord(r1.COLUMN_VALUE) LOOP
v_dedup:=true;
v_dedup_ord:='order by '||r3.cond;
end LOOP;--r3 --dedup order
BW_BUFFER.AddCH(r2.JoinType||' ');
v_HINT:=null;
if v_anyEncl then
v_HINT:=regexp_replace(NVL(v_HINT,'/*+*/'),'[+]','+ QB_NAME('||r2.ALIAS||') ');
if NVL(r2.NoIndex,'N')='Y' then
v_HINT:=regexp_replace(NVL(v_HINT,'/*+*/'),'[+]','+ NO_INDEX('||r2.ALIAS||') ');
end if;
BW_BUFFER.AddLN('-- '||getTableQual(r2.Name,r2.Owner,r2.Link,r2.Partition)||' '||r2.Alias||' ... '||case when v_dedup then 'deduplication' when v_agg then 'aggregation' when v_subjoin then 'subjoin' else 'enclosed' end);
BW_BUFFER.IND(2);
v_AttrList:=getDedupAttrList(r2.Alias,r1.COLUMN_VALUE);
------------------------------------------------
--aggregations
if v_agg then
v_GrpList:=v_AttrList;
for r3 in c_tab_agg(r1.COLUMN_VALUE) LOOP
v_AttrList:=regexp_replace(v_AttrList,'(\W|^)('||COALESCE(r3.ALIAS,r3.COLUMN_NAME,'#')||')(\W|$)','\1'||r3.AGG_FUNCTION||'('||case when r3.DistinctFlag='Y' then 'distinct ' else '' end||r3.COLUMN_NAME||') as '||NVL(r3.ALIAS,r3.COLUMN_NAME)||'\3');
v_GrpList:=regexp_replace(v_GrpList,'(\W|^)('||COALESCE(r3.ALIAS,r3.COLUMN_NAME,'#')||')(\W|$)','\3');
end LOOP;--r3 --aggregation
v_GrpList:=regexp_replace(v_GrpList,'(^)(\W*)(\w)','\1\3');
v_GrpList:=regexp_replace(v_GrpList,'(\w)(\W*)($)','\1\3');
end if;
------------------------------------------------
--subjoins
if v_subjoin then
for r3 in c_tab_subjoin(r1.COLUMN_VALUE) LOOP
for r4 in c_tab_attrs2(r3.COLUMN_VALUE) LOOP
for r5 in c_subjoin_col(r3.COLUMN_VALUE) LOOP
v_AttrList:=regexp_replace(v_AttrList,'(\W|^)([aA][sS] )('||r5.COLUMN_ALIAS||')(\W|$)','\1\2\3xxxxxxxx\4');
v_AttrList:=regexp_replace(v_AttrList,'([^([:alnum:]]|^)('||r5.COLUMN_ALIAS||')(\W|$)','\1'||r4.ALIAS||'.'||r5.COLUMN_NAME||' as '||r5.COLUMN_ALIAS||'xxxxxxxx\3');
v_AttrList:=regexp_replace(v_AttrList,'([(])('||r5.COLUMN_ALIAS||')(\W|$)','\1'||r4.ALIAS||'.'||r5.COLUMN_NAME||'\3');
v_AttrList:=regexp_replace(v_AttrList,'(\W|^)([aA][sS] '||r5.COLUMN_ALIAS||')(xxxxxxxx)(\W|$)','\1\2\4');
v_GrpList:=regexp_replace(v_GrpList,'([^([:alnum:]]|^)('||r5.COLUMN_ALIAS||')(\W|$)','\1'||r4.ALIAS||'.'||r5.COLUMN_NAME||'\3');
end LOOP;--r5
if NVL(r4.NoIndex,'N')='Y' then
v_HINT:=regexp_replace(NVL(v_HINT,'/*+*/'),'[+]','+ NO_INDEX('||r4.ALIAS||') ');
end if;
end LOOP;--r4
end LOOP;--r3 --subjoin process
QualifyList(v_AttrList,r2.Alias);
QualifyList(v_GrpList,r2.Alias);
end if;
------------------------------------------------
BW_BUFFER.AddLN('(Select '||v_Hint||v_AttrList);
BW_BUFFER.IND(2);
if v_dedup then
BW_BUFFER.AddLN(', row_number()over('||v_dedup_part||' '||v_dedup_ord||') as rn$');
end if;
BW_BUFFER.AddLN('From '||getTableQual(r2.Name,r2.Owner,r2.Link,r2.Partition)||' '||r2.alias);
------------------------------------------------
--subjoins
if v_subjoin then
for r3 in c_tab_subjoin(r1.COLUMN_VALUE) LOOP
for r4 in c_tab_attrs2(r3.COLUMN_VALUE) LOOP
BW_BUFFER.AddLN(r4.JoinType||' '||getTableQual(r4.Name,r4.Owner,r4.Link,r4.Partition)||' '||r4.alias);
BW_BUFFER.IND(2);
v_Delim1:='ON ';
for r5 in c_tab_on(r3.COLUMN_VALUE) LOOP
BW_BUFFER.AddLN(v_Delim1||'('||r5.COND||')');
v_Delim1:='AND ';
end LOOP;--r5
BW_BUFFER.IND(-2);
end LOOP;--r4
end LOOP;--r3 --subjoin process
end if;
------------------------------------------------
v_Delim1:='Where ';
for r3 in c_tab_where(r1.COLUMN_VALUE) LOOP
BW_BUFFER.AddLN(v_Delim1||'('||r3.COND||')');
v_Delim1:='and ';
end LOOP;--r3
if v_agg then
BW_BUFFER.AddLN('Group by '||v_GrpList);
end if;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(') '||r2.alias);
BW_BUFFER.IND(-2);
else
BW_BUFFER.AddLN(getTableQual(r2.Name,r2.Owner,r2.Link,r2.Partition)||' '||r2.Alias);
end if;
BW_BUFFER.IND(2);
v_Delim1:='ON ';
for r3 in c_tab_on(r1.COLUMN_VALUE) LOOP
BW_BUFFER.AddLN(v_Delim1||'('||r3.COND||')');
v_Delim1:='AND ';
end LOOP;--r3
if upper(r2.JoinType) not in ('FROM') then
if v_dedup then
BW_BUFFER.AddLN(v_Delim1||'('||r2.Alias||'.rn$=1)');
v_Delim1:='AND ';
end if;
if (not v_anyEncl) then
for r3 in c_tab_where(r1.COLUMN_VALUE) LOOP
BW_BUFFER.AddLN(v_Delim1||'('||r3.COND||')');
v_Delim1:='AND ';
end LOOP;--r3
end if;
end if;
BW_BUFFER.IND(-2);
end LOOP;--r2
End LOOP;--r1
End addFromPart;
-------------------------------------------------------------------------------------------------------
Procedure addFilterPart is
v_delim Varchar2(30):='WHERE ';
v_encl boolean:=false;
Begin
for r1 in c_filter LOOP
if NVL(r1.type,'SOURCE')='SOURCE' then
BW_BUFFER.AddLN(v_delim||r1.cond);
v_delim:='AND ';
end if;
end LOOP;
for r1 in c_srctab LOOP
for r2 in c_tab_attrs(r1.COLUMN_VALUE) LOOP
if upper(r2.JoinType) in('FROM') then
if not isEnclosed(r1.COLUMN_VALUE) then
for r3 in c_tab_where(r1.COLUMN_VALUE) LOOP
BW_BUFFER.AddLN(v_delim||r3.cond);
v_delim:='AND ';
end LOOP;--r3 --dedup order
elsif isEnclosed(r1.COLUMN_VALUE,'DEDUP%') then
BW_BUFFER.AddLN(v_delim||r2.Alias||'.rn$=1');
v_delim:='AND ';
end if;
end if;
end LOOP;
end LOOP;
End addFilterPart;
-------------------------------------------------------------------------------------------------------
Procedure addColumnList(P_Prefix Varchar2,p_use_direct Boolean:=false,p_incl_direct Boolean:=true) is
v_delim Varchar2(30):=' ';
Begin
for r1 in c_COLMAP LOOP
if NVL(r1.DummyFlag,'N')!='Y' then
if p_use_direct and r1.InsertExpr is not null then
BW_BUFFER.AddLN(v_delim||replace(va_expr(r1.InsertExpr),':1',P_Prefix||'.'||r1.TargetColumn));
elsif p_use_direct and r1.DirectFlag='Y' then
if va_expr_replace.exists(r1.TargetColumn) then
BW_BUFFER.AddLN(v_delim||va_expr_replace(r1.TargetColumn));
else
BW_BUFFER.AddLN(v_delim||getSourceExpression(r1));
end if;
v_delim:=',';
elsif p_incl_direct or NVL(r1.DirectFlag,'N')='N' then
BW_BUFFER.AddLN(v_delim||P_Prefix||'.'||r1.TargetColumn);
v_delim:=',';
end if;
end if;
end LOOP;
End addColumnList;
-------------------------------------------------------------------------------------------------------
Procedure addSetList(P_AllDirect boolean:=false) is
v_delim Varchar2(30):=' ';
Begin
for r1 in c_COLMAP LOOP
if NVL(r1.UpdateFlag,'Y')!='N' and NVL(r1.MatchFlag,'N')!='Y' and NVL(r1.DummyFlag,'N')!='Y'
and getSourceExpression(r1) is not null then
BW_BUFFER.AddCH(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'=');
if r1.UpdateExpr is not null then
BW_BUFFER.AddLN(replace(va_expr(r1.UpdateExpr),':1',c_StdSourcePrefix||'.'||r1.TargetColumn));
elsif NVL(r1.DirectFlag,'N')='Y' or P_AllDirect then
BW_BUFFER.AddLN(getSourceExpression(r1));
else
BW_BUFFER.AddLN(c_StdSourcePrefix||'.'||r1.TargetColumn);
end if;
v_delim:=',';
end if;
end LOOP;
End addSetList;
-------------------------------------------------------------------------------------------------------
Procedure addOnList is
v_delim Varchar2(30):='';
Begin
for r1 in c_COLMAP LOOP
if r1.MatchFlag='Y' then
if NVL(r1.DirectFlag,'N')='N' then
BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'='||c_StdSourcePrefix||'.'||r1.TargetColumn);
else
BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'='||r1.SourceExpression);
end if;
v_delim:='AND ';
end if;
end LOOP;
for r1 in c_filter LOOP
if NVL(r1.type,'SOURCE') in ('TARGET','SOURCE_OUTER','ON') then
BW_BUFFER.AddLN(v_delim||r1.cond);
v_delim:='AND ';
end if;
end LOOP;
End addOnList;
-------------------------------------------------------------------------------------------------------
Procedure addInsertWhere is
v_delim Varchar2(30):='WHERE ';
Begin
for r1 in c_filter LOOP
if NVL(r1.type,'SOURCE') in ('INSERT','SOURCE_OUTER') then
BW_BUFFER.AddLN(v_delim||r1.cond);
v_delim:='AND ';
end if;
end LOOP;
End addInsertWhere;
-------------------------------------------------------------------------------------------------------
Procedure addUpdateWhere (p_diff boolean:=false,p_AllDirect boolean:=false) is
v_delim Varchar2(30):='WHERE ';
Begin
if p_diff then
v_delim:=v_delim||'(';
for r1 in c_COLMAP LOOP
if nvl(r1.updateFlag,'Y')!='N' and nvl(r1.dummyFlag,'N')!='Y' and NVL(r1.MatchFlag,'N')!='Y' and NVL(r1.DirectFlag,'N')!='Y' then
if not p_AllDirect then
BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'!='||c_StdSourcePrefix||'.'||r1.TargetColumn);
else
BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'!='||getSourceExpression(r1));
end if;
v_delim:='OR ';
end if;
end LOOP;
Case
when v_delim='OR ' then
BW_BUFFER.AddLN(')--/differential part');
v_delim:='AND ';
when v_delim like '%(' then
v_delim:=rtrim(v_delim,'(');
else null;
end Case;
end if;
for r1 in c_filter LOOP
if NVL(r1.type,'SOURCE') in ('UPDATE') then
BW_BUFFER.AddLN(v_delim||r1.cond);
v_delim:='AND ';
end if;
end LOOP;
End addUpdateWhere;
-------------------------------------------------------------------------------------------------------
Procedure addLinkDummyMergeInsert(r1 c_mod_attrs%rowtype ) is
v_delim Varchar2(2):=' ';
Begin
if v_isDBLink then
BW_BUFFER.AddLN('WHEN NOT MATCHED THEN INSERT');
BW_BUFFER.IND(2);
BW_BUFFER.AddLN('(');
BW_BUFFER.IND(2);
for r2 in c_COLMAP LOOP
if NVL(r2.MatchFlag,'N')='Y' then
BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r2.TargetColumn);
v_delim:=',';
end if;
end LOOP;
BW_BUFFER.IND(-1);
BW_BUFFER.AddLN(')VALUES(');
BW_BUFFER.IND(1);
v_delim:=' ';
for r2 in c_COLMAP LOOP
if NVL(r2.MatchFlag,'N')='Y' then
BW_BUFFER.AddLN(v_delim||c_StdSourcePrefix||'.'||r2.TargetColumn);
v_delim:=',';
end if;
end LOOP;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(')');
BW_BUFFER.AddLN('WHERE 1=0');
BW_BUFFER.IND(-2);
end if;
End addLinkDummyMergeInsert;
-------------------------------------------------------------------------------------------------------
-- Patterns Part
-------------------------------------------------------------------------------------------------------
Procedure addMergePattern( r1 c_mod_attrs%rowtype ) is
Begin
BW_BUFFER.AddLN('MERGE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
BW_BUFFER.AddLN('USING(');
BW_BUFFER.IND(2);
AddSelectPart(getGeneratedSourceHints||r1.SourceHint);
AddFromPart;
AddFilterPart;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
BW_BUFFER.AddLN('ON(');
BW_BUFFER.IND(2);
AddOnList;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(')');
if r1.pattern in ('MERGE','DIFF_MERGE','UPDATE','DIFF_UPDATE') then
BW_BUFFER.AddLN('WHEN MATCHED THEN UPDATE SET');
BW_BUFFER.IND(2);
AddSetList;
addUpdateWhere(r1.pattern in ('DIFF_MERGE','DIFF_UPDATE'));
BW_BUFFER.IND(-2);
end if;
if r1.pattern in ('MERGE','DIFF_MERGE','DIFF_INSERT') then
BW_BUFFER.AddLN('WHEN NOT MATCHED THEN INSERT');
BW_BUFFER.IND(2);
BW_BUFFER.AddLN('(');
BW_BUFFER.IND(2);
AddColumnList(c_StdTargetPrefix,false);
BW_BUFFER.IND(-1);
BW_BUFFER.AddLN(')VALUES(');
BW_BUFFER.IND(1);
AddColumnList(c_StdSourcePrefix,true);
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(')');
addInsertWhere;
BW_BUFFER.IND(-2);
end if;
If r1.pattern in ('UPDATE','DIFF_UPDATE') then
addLinkDummyMergeInsert(r1);
end if;
BW_BUFFER.AddLN(';');
BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
End addMergePattern;
-------------------------------------------------------------------------------------------------------
Procedure addInsertPattern(r1 c_mod_attrs%rowtype) is
v_delim Varchar2(30):='WHERE ';
Begin
BW_BUFFER.AddLN('INSERT'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
BW_BUFFER.IND(2);
BW_BUFFER.AddLN('(');
BW_BUFFER.IND(2);
AddColumnList(c_StdTargetPrefix,false);
BW_BUFFER.IND(-1);
BW_BUFFER.AddLN(')');
BW_BUFFER.AddLN('Select');
BW_BUFFER.IND(1);
AddColumnList(c_StdSourcePrefix,true);
BW_BUFFER.IND(-1);
BW_BUFFER.AddLN('from(');
BW_BUFFER.IND(2);
AddSelectPart(getGeneratedSourceHints||r1.SourceHint);
AddFromPart;
AddFilterPart;
if r1.pattern in ('DIFF_INSERT(MINUS)') then
BW_BUFFER.AddLN('MINUS');
BW_BUFFER.IND(2);
BW_BUFFER.AddLN('SELECT');
AddColumnList(c_StdTargetPrefix,false,false);
BW_BUFFER.AddLN('FROM '
||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
for r1 in c_filter LOOP
if NVL(r1.type,'SOURCE')='TARGET' then
BW_BUFFER.AddLN(v_delim||r1.cond);
v_delim:='AND ';
end if;
end LOOP;
BW_BUFFER.IND(-2);
end if;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
addInsertWhere;
BW_BUFFER.IND(-3);
BW_BUFFER.AddLN(';');
BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
End addInsertPattern;
-------------------------------------------------------------------------------------------------------
Procedure addSelfUpdatePattern( r1 c_mod_attrs%rowtype ) is
Begin
BW_BUFFER.AddLN('UPDATE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' '
||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
BW_BUFFER.AddLN('SET');
BW_BUFFER.IND(2);
AddSetList(true);
addUpdateWhere(true,true);
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(';');
BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
End addSelfUpdatePattern;
-------------------------------------------------------------------------------------------------------
Procedure addFRDeletePattern( r1 c_mod_attrs%rowtype ) is
v_delim Varchar2(30):=' ';
Begin
BW_BUFFER.AddLN('MERGE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
BW_BUFFER.AddLN('USING(');
BW_BUFFER.IND(2);
BW_BUFFER.AddLN('SELECT');
BW_BUFFER.IND(3);
for r1 in c_COLMAP LOOP
if NVL(r1.DirectFlag,'N')!='Y' then
BW_BUFFER.AddLN(v_delim||r1.TargetColumn);
v_delim:=',';
end if;
end LOOP;
BW_BUFFER.IND(-3);
BW_BUFFER.AddLN('FROM '||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition));
BW_BUFFER.IND(-1);
BW_BUFFER.AddLN('MINUS');
BW_BUFFER.IND(1);
AddSelectPart(getGeneratedSourceHints||r1.SourceHint);
AddFromPart;
AddFilterPart;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
BW_BUFFER.AddLN('ON(');
BW_BUFFER.IND(2);
AddOnList;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(')');
BW_BUFFER.AddLN('WHEN MATCHED THEN UPDATE SET');
BW_BUFFER.IND(2);
AddSetList;
addUpdateWhere(r1.pattern in ('DIFF_MERGE','DIFF_UPDATE'));
BW_BUFFER.IND(-2);
addLinkDummyMergeInsert(r1);
BW_BUFFER.AddLN(';');
BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
End addFRDeletePattern;
-------------------------------------------------------------------------------------------------------
Procedure addCreepingDeathPattern(r1 c_mod_attrs%rowtype) is
Procedure addSelectPartCreepingDeath (P_Source_hint Varchar2:=null)is
v_delim Varchar2(30):=' ';
v_partitionBy Varchar2(8000):='';
v_validFrom Varchar2(8000):='';
v_partitionDelim Varchar2(30):=' ';
Begin
BW_BUFFER.AddLN('SELECT'||replace(' /*+'||P_Source_hint||'*/',' /*+*/'));
BW_BUFFER.IND(3);
for r1 in c_COLMAP LOOP
if NVL(r1.MatchFlag,'N')='Y' or NVL(r1.ValidToFlag,'N')='Y' then
BW_BUFFER.AddLN(v_delim||r1.TargetColumn);
v_delim:=',';
end if;
if NVL(r1.PartFlag,'N')='Y' then
v_partitionBy:=concat(v_partitionBy,v_partitionDelim||r1.TargetColumn);
v_partitionDelim:=',';
end if;
if NVL(r1.ValidFromFlag,'N')='Y' then
v_validFrom:=r1.TargetColumn;
end if;
end LOOP;
BW_BUFFER.AddLN(v_delim||'Lead('||v_validFrom||')over(partition by '||v_partitionBy||' order by '||v_validFrom||') as NEXT$FROM');
BW_BUFFER.IND(-3);
End addSelectPartCreepingDeath ;
Begin
BW_BUFFER.AddLN('MERGE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
BW_BUFFER.AddLN('USING(');
BW_BUFFER.IND(2);
AddSelectPartCreepingDeath(getGeneratedSourceHints||r1.SourceHint);
BW_BUFFER.AddLN('From '||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition));
AddFilterPart;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
BW_BUFFER.AddLN('ON(');
BW_BUFFER.IND(2);
AddOnList;
BW_BUFFER.AddLN('AND '||c_StdSourcePrefix||'.NEXT$FROM is not null');
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(')');
addLinkDummyMergeInsert(r1);
BW_BUFFER.AddLN('WHEN MATCHED THEN UPDATE SET');
BW_BUFFER.IND(2);
AddSetList(true);
for r1 in c_COLMAP LOOP
if r1.ValidToFlag='Y' then
BW_BUFFER.AddLN(','||c_StdTargetPrefix||'.'||r1.TargetColumn||'='||c_StdSourcePrefix||'.NEXT$FROM-1');
end if;
end LOOP;
BW_BUFFER.AddLN('');
addUpdateWhere(false);
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN(';');
BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
End addCreepingDeathPattern;
-------------------------------------------------------------------------------------------------------
-- Package Part
-------------------------------------------------------------------------------------------------------
Procedure addLogProcedure is
Begin
BW_BUFFER.AddLN('--Log info -- some logging procedure should be added here');
End addLogProcedure;
-------------------------------------------------------------------------------------------------------
Procedure addSeqWasteIssueFunctions is
v_seqNm Varchar2(100);
v_delim Varchar2(30):='';
v_cond Varchar2(9000):='';
Begin
for r1 in c_filter LOOP
if NVL(r1.type,'SOURCE') in ('INSERT','SOURCE_OUTER') then
v_cond:=concat(v_cond,v_delim||r1.cond);
v_delim:=chr(10)||'and ';
end if;
end LOOP;
if v_cond is not null then
for r2 in c_colmap LOOP
if upper(r2.SourceExpression) like '%.NEXTVAL%' then
v_seqNM:=regexp_replace(regexp_substr(upper(r2.SourceExpression),'(^|\W)\w+([.]NEXTVAL)'),'\W|NEXTVAL');
BW_BUFFER.AddLN('--sequence '||v_seqNM);
BW_BUFFER.AddLN('Function get_'||v_seqNM||'(isit Integer) return Integer is');
BW_BUFFER.AddLN(' i integer:=0;');
BW_BUFFER.AddLN('Begin');
BW_BUFFER.IND(2);
BW_BUFFER.AddLN(' if isit=1 then');
BW_BUFFER.AddLN(' select '||r2.SourceExpression||' into i from dual;');
BW_BUFFER.AddLN(' end if;');
BW_BUFFER.AddLN(' return i;');
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN('End get_'||v_seqNM||';');
va_expr_replace(r2.targetColumn):=p_module||'.get_'||v_seqNM||'(case when '||v_cond||' then 1 else 0 end)';
va_spec_functions(nvl(va_spec_functions.last,0)+1):='Function get_'||v_seqNM||'(isit Integer) return Integer;';
va_spec_funcnames(nvl(va_spec_funcnames.last,0)+1):='get_'||v_seqNM;
end if;
end LOOP; --r2
end if;
End addSeqWasteIssueFunctions ;
-------------------------------------------------------------------------------------------------------
Procedure addMainProcHead is
v_is_head Boolean:=false;
v_tbdConst Boolean:=true;
Begin
for r1 in (
Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
)LOOP
if not v_is_head and upper(replace(r1.TEXT,' ')) like 'PROCEDUREMAIN%' then
v_is_head:=true;
end if;
if v_is_head then --witin header code
if r1.TEXT like '%;%' then --resolve end
v_is_head:=false;
r1.TEXT:=rtrim(regexp_replace(r1.TEXT,'(^.*)(;)(.*$)','\1'),chr(10)||chr(13))||' is';
BW_BUFFER.AddLN(r1.TEXT);
else
BW_BUFFER.AddCH(r1.TEXT);
end if;
end if;
End LOOP;
BW_BUFFER.IND(2);
BW_BUFFER.AddLN('--Audit variables');
BW_BUFFER.AddLN('v_processed_rows Integer:=0;');
BW_BUFFER.AddLN('v_Module_name Varchar2(100):='''||p_Module||''';');
for r1 in c_constant LOOP
BW_BUFFER.AddCH(r1.Name||' '||r1.Type);
if r1.Value is not null then
BW_BUFFER.AddCH(' := '||r1.Value);
end if;
BW_BUFFER.AddLN(';');
end LOOP;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN('Begin');
BW_BUFFER.IND(2);
addLogProcedure;
for r1 in c_constant LOOP
if r1.query is not null then
if v_tbdConst then
BW_BUFFER.AddLN('--Constant queries');
v_tbdConst:=false;
end if;
BW_BUFFER.AddLN('Begin');
BW_BUFFER.AddLN(' '||translateDBLink(regexp_replace(r1.query,'(\W)(FROM)(\W)','\1INTO '||r1.Name||' \2\3',1,1,'i'))||';');
BW_BUFFER.AddLN(' Exception when others then '||r1.name||':='||NVL(r1.value,'null')||';');
BW_BUFFER.AddLN('End;');
end if;
end LOOP;
BW_BUFFER.AddLN('--Mapping statement');
End addMainProcHead;
-------------------------------------------------------------------------------------------------------
Procedure addMainProcTail is
Begin
addLogProcedure;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN('End Main;');
End addMainProcTail;
-------------------------------------------------------------------------------------------------------
Procedure addPremappings is
Begin
for r1 in c_premappings LOOP
BW_BUFFER.AddLN('-- Premapping: '||r1.name);
BW_BUFFER.AddLN(translateDBLink(r1.code));
end LOOP;
End addPremappings;
-------------------------------------------------------------------------------------------------------
Procedure addPostmappings is
Begin
for r1 in c_postmappings LOOP
BW_BUFFER.AddLN('-- Postmapping: '||r1.name);
BW_BUFFER.AddLN(translateDBLink(r1.code));
end LOOP;
End addPostmappings;
-------------------------------------------------------------------------------------------------------
Procedure addExecConditionHead is
Begin
for r1 in c_execConditions LOOP
BW_BUFFER.AddLN('if '||r1.code||' then -- Execute condition '||r1.name);
BW_BUFFER.IND(2);
end LOOP;
End addExecConditionHead;
-------------------------------------------------------------------------------------------------------
Procedure addExecConditionTail is
Begin
for r1 in c_execConditions LOOP
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN('end if;');
end LOOP;
End addExecConditionTail;
-------------------------------------------------------------------------------------------------------
Procedure addLoopHead is
i integer:=0;
v_case TAE;
v_cs Varchar2(2000);
Begin
for r1 in c_loop LOOP
i:=i+1;
for r2 in c_loopVar(r1.COLUMN_VALUE) LOOP
v_cs:=chr(10)||' when '||to_char(i)||' then '||r2.value;
if v_case.exists(r2.name) then
v_case(r2.name):=concat(v_case(r2.name),v_cs);
else
v_case(r2.name):=r2.name||':=case i '||v_cs;
end if;
end LOOP;
end LOOP;
if i>0 then
BW_BUFFER.AddLN('for i in 1..'||to_char(i)||' LOOP');
BW_BUFFER.IND(2);
v_cs:=v_case.first;
while v_cs is not null LOOP
BW_BUFFER.AddLN(concat(v_case(v_cs),chr(10)||'end;'));
v_cs:=v_case.next(v_cs);
end LOOP;
end if;
End addLoopHead;
-------------------------------------------------------------------------------------------------------
Procedure addLoopTail is
Begin
for r1 in c_loop LOOP
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN('end LOOP;');
exit;
end LOOP;
End addLoopTail;
-------------------------------------------------------------------------------------------------------
Procedure addPackageBodyHead is
Begin
BW_BUFFER.AddLN('Create or replace package body '||p_Module||' as');
BW_BUFFER.IND(2);
addSeqWasteIssueFunctions;
BW_BUFFER.AddLN('--Procedure MAIN');
addMainProcHead;
addLoopHead;
addExecConditionHead;
addPremappings;
End addPackageBodyHead;
-------------------------------------------------------------------------------------------------------
Procedure addPackageBodyTail is
Begin
addPostmappings;
addExecConditionTail;
addLoopTail;
addMainProcTail;
BW_BUFFER.IND(-2);
BW_BUFFER.AddLN('End '||p_Module||';');
End addPackageBodyTail;
-------------------------------------------------------------------------------------------------------
procedure checkStatus is
v_status Varchar2(30);
Begin
Select status into v_status from user_objects where object_type='PACKAGE BODY' and object_name=p_Module;
if v_status='INVALID' then
raise_application_error(-20006,'Module '||p_Module||' compiled unsuccessfully');
end if;
End checkStatus;
-------------------------------------------------------------------------------------------------------
Procedure rewriteSpec is
v_yetgen boolean:=false;
v_ptg boolean:=false;
v_pt1 boolean:=false;
v_pt2 boolean:=false;
v_done boolean:=false;
v_skip boolean:=false;
Begin
for r1 in (
Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
)LOOP
if upper(replace(r1.TEXT,' ')) like '/*GENERATEDPART%' then
v_yetgen:=true;
exit;
end if;
End LOOP;
BW_BUFFER.Init;
BW_BUFFER.AddCh(' CREATE OR REPLACE ');
for r1 in (
Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
)LOOP
if not v_pt1 and upper(replace(r1.TEXT,' ')) like 'PROCEDUREMAIN%' then
v_pt1:=true;
end if;
if v_pt1 and r1.TEXT like '%;%' then
v_pt2:=true;
end if;
if not v_ptg and upper(replace(r1.TEXT,' ')) like '/*GENERATEDPART%' then
v_ptg:=true;
end if;
if v_skip and upper(replace(r1.TEXT,' ')) like '/*!GENERATEDPART%' then
v_skip:=false;
end if;
if not v_skip then
BW_BUFFER.AddLN(rtrim(r1.TEXT,chr(10)));
end if;
if not v_done and ((v_pt2 and not v_yetgen) or v_ptg) then
if va_spec_functions.count>0 then
if v_yetgen then
v_skip:=true;
else
BW_BUFFER.AddLN('/* GENERATED PART */');
end if;
for i in 1..va_spec_functions.count LOOP
BW_BUFFER.AddLN(' '||va_spec_functions(i));
BW_BUFFER.AddLN(' PRAGMA RESTRICT_REFERENCES ('||va_spec_funcnames(i)||', WNPS, WNDS, RNPS);');
end LOOP;
if not v_yetgen then
BW_BUFFER.AddLN('/* !GENERATED PART */');
end if;
elsif v_yetgen then
v_skip:=true;
end if;
v_done:=true;
end if;
End LOOP;
BW_BUFFER.BuffExec;
BW_BUFFER.Init;
BW_BUFFER.AddLN('ALTER PACKAGE '||p_Module||' COMPILE BODY');
BW_BUFFER.BuffExec;
End rewriteSpec;
-------------------------------------------------------------------------------------------------------
Begin
DBMS_OUTPUT.ENABLE(1000000);
BW_Buffer.Init;
fillXML;
addPackageBodyHead;
for r1 in c_mod_attrs LOOP
if r1.pattern in ('MERGE','DIFF_MERGE','UPDATE','DIFF_UPDATE','DIFF_INSERT') then
addMergePattern(r1);
elsif r1.pattern in ('INSERT','DIFF_INSERT(MINUS)') then
addInsertPattern(r1);
elsif r1.pattern in ('SELF_UPDATE') then
addSelfUpdatePattern(r1);
elsif r1.pattern in ('FR_DELETE') then
addFRDeletePattern(r1);
elsif r1.pattern in ('CREEPING_DEATH') then
addCreepingDeathPattern(r1);
elsif r1.pattern in ('NONE') then
null;
end if;
end LOOP;
addPackageBodyTail;
BW_Buffer.BuffExec;
rewritespec;
checkStatus;
End GenPackageBody;
END BW_SIMPLE_GEN;
/