_dm_testtool_chk_def_set()
{
sqlplus -s $DB_LOGIN << !create or replace procedure proc_dm_tsttl_chk_def_val as
v_dbtblName svcdatatype.dbtblname%type := null;
v_TitlePrt boolean := false;
v_Count number(10) := 0;
v_SqlSent varchar2(1000) := null;begin
dbms_output.enable(1000000); for c_Attr_r in (
select * from svcattrtype
where sdtname not like '%RTDB%' and
versionname in (
-- select distinct versionname from svctype where INVERSION='2') and
select distinct versionname from svctype) and
attrDefault is not null)
loop select distinct dbtblname into v_dbtblName from svcdatatype
where svcname=c_Attr_r.svcname and
versionname=c_Attr_r.versionname and
sdtname=c_Attr_r.sdtname;
if v_dbtblName not like 'S0%_' then
goto nextdefvalitem;
end if;
v_SqlSent := 'select count(*) from '||v_dbtblName||
' where '||c_Attr_r.satname||' is null';
execute immediate v_SqlSent into v_Count;
if v_Count<>0 then
if v_TitlePrt=false then
DBMS_OUTPUT.put_line('!!!!!!!!!! --WARNING-- default value not correctly set !!!!!!!!!!');
v_TitlePrt := true;
end if;
DBMS_OUTPUT.put_line(' '||v_Count||' records with value NULL for'||
' Service='''||c_Attr_r.svcname||
''' Version='''||c_Attr_r.versionname||''''||
''' TableName='''||v_dbtblName||
''' FieldName='''||c_Attr_r.satname||
''' DefValue='''||c_Attr_r.attrDefault||'''');
end if;
<<nextdefvalitem>>
null;
end loop;exception
when others then
DBMS_OUTPUT.put_line(SQLERRM);
end proc_dm_tsttl_chk_def_val;
/
{
sqlplus -s $DB_LOGIN << !create or replace procedure proc_dm_tsttl_chk_def_val as
v_dbtblName svcdatatype.dbtblname%type := null;
v_TitlePrt boolean := false;
v_Count number(10) := 0;
v_SqlSent varchar2(1000) := null;begin
dbms_output.enable(1000000); for c_Attr_r in (
select * from svcattrtype
where sdtname not like '%RTDB%' and
versionname in (
-- select distinct versionname from svctype where INVERSION='2') and
select distinct versionname from svctype) and
attrDefault is not null)
loop select distinct dbtblname into v_dbtblName from svcdatatype
where svcname=c_Attr_r.svcname and
versionname=c_Attr_r.versionname and
sdtname=c_Attr_r.sdtname;
if v_dbtblName not like 'S0%_' then
goto nextdefvalitem;
end if;
v_SqlSent := 'select count(*) from '||v_dbtblName||
' where '||c_Attr_r.satname||' is null';
execute immediate v_SqlSent into v_Count;
if v_Count<>0 then
if v_TitlePrt=false then
DBMS_OUTPUT.put_line('!!!!!!!!!! --WARNING-- default value not correctly set !!!!!!!!!!');
v_TitlePrt := true;
end if;
DBMS_OUTPUT.put_line(' '||v_Count||' records with value NULL for'||
' Service='''||c_Attr_r.svcname||
''' Version='''||c_Attr_r.versionname||''''||
''' TableName='''||v_dbtblName||
''' FieldName='''||c_Attr_r.satname||
''' DefValue='''||c_Attr_r.attrDefault||'''');
end if;
<<nextdefvalitem>>
null;
end loop;exception
when others then
DBMS_OUTPUT.put_line(SQLERRM);
end proc_dm_tsttl_chk_def_val;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货