BlueMichael

/**/ /* ????
說明:?????
?????在調試sql腳本時，如果要用Dbms_Output.Put_Line顯示腳本中的變量，如果變量中的內容單行長度????
?????超過了255個字符，會提示下面錯誤：????
?????ORA-20000:?ORU-10028:?line?length?overflow,?limit?of?255?chars?per?line????
?????所以我們可以用下面的已經創建好的包和視圖來實現。????
?????????
?????簡單點說就是也可以創建一個表，其中一個字段為long，將變量插入后再用select去查看。上面方式省略了創建表的過程。????
*/ ????
-- ?第一步????
create ? or ? replace ?package?my_output?????
as ?????
????? procedure ?put(?s? in ? varchar2 ?);?????
????? procedure ?put_line(?s? in ? varchar2 ?);?????
????? procedure ?new_line;?????
??????
????? function ?get_line(?n? in ? number ?)? return ? varchar2 ;?????
?????pragma?restrict_references(?get_line,?wnds,?rnds?);?????
??????
????? function ?get_line_count? return ? number ;?????
?????pragma?restrict_references(?get_line_count,?wnds,?rnds,?wnps?);?????
??????
?????pragma?restrict_references(?my_output,?wnds,?rnds,?wnps,?rnps?);?????
end ;?????
???
-- ?第二步??????
create ? or ? replace ?package?body?my_output?????
as ?????
?????type?Array? is ? table ? of ? varchar2 ( 4000 )? index ? by ?binary_integer;?????
?????g_data????????array;?????
?????g_cnt???????? number ? default ? 1 ;?????
??????
????? procedure ?put(?s? in ? varchar2 ?)?????
????? is ?????
????? begin ?????
????????? if ?(?g_data.last? is ? not ? null ?)? then ?????
?????????????g_data(g_data.last)?: = ?g_data(g_data.last)? || ?s;?????
????????? else ?????
?????????????g_data( 1 )?: = ?s;?????
????????? end ? if ;?????
????? end ;?????
??????
????? procedure ?put_line(?s? in ? varchar2 ?)?????
????? is ?????
????? begin ?????
?????????put(?s?);?????
?????????g_data(g_data.last + 1 )?: = ? null ;?????
????? end ;?????
??????
????? procedure ?new_line?????
????? is ?????
????? begin ?????
?????????put(? null ?);?????
?????????g_data(g_data.last + 1 )?: = ? null ;?????
????? end ;?????
??????
????? function ?get_line(?n? in ? number ?)? return ? varchar2 ?????
????? is ?????
?????????l_str? varchar2 ( 4000 )? default ?g_data(n);?????
????? begin ?????
?????????g_data. delete (n);?????
????????? return ?l_str;?????
????? end ;?????
??????
????? function ?get_line_count? return ? number ?????
????? is ?????
????? begin ?????
????????? return ?g_data. count + 1 ;?????
????? end ;?????
??????
end ;?????
???
-- ?第三步????
create ? or ? replace ? view ?my_output_view?????
as ?????
select ?rownum? lineno ,?my_output.get_line(?rownum?)? text ?????
??? from ?all_objects?????
?? where ?rownum? < ?(? select ?my_output.get_line_count? from ?dual?);?????
???
-- ---------------------------------------------------------------????
-- ?實現????
-- ?1?寫入要查看的內容????
declare ?????
??QuerySql? varchar2 ( 4000 );????
begin ??????
??Dbms_Output.enable( 4000 );????
??QuerySql???: = ' ?IIDD?AS?ID,?IsDel?AS?已刪除,?AnJianID?AS?案件編號,?BaoGaoRenXM?AS?報案人姓名,?JieBaoRenXM?AS?接報人姓名,?JieBaoShiJian?AS?接報時間,?BM_MingCheng?AS?辦案單位名稱??From?XZ_ShouLiDengJi?a?WHERE??(exists?(Select? '' S '' ?from?GG_AnJianBanLi??where?AnJianID=a.AnJianID)?and?not?exists?(Select? '' S '' ?From?GG_AnJian?Where?AnJianID=a.AnJianID?and?HuiBiRenYuan_BH?like? '' %admin% '' ))?ORDER?BY?AddDateTime?DESC ' ;????
??Dbms_Output.Put_Line( ' LENGTH?:? ' ? || ?Length(QuerySql));????
??my_output.put_line(QuerySql);?????
end ;?????
-- ?2?查看結果????
select ? * ? from ?my_output_view??