用视图能显示单行超过255个字符的例子

sql 代码

/*
说明:
     在调试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

分享到：

Oracle的Package的作用以及使用什么工具编 ... | Oracle中SQL语句块的编写方法

• 2007-07-26 15:11
• 浏览 2140
• 评论(0)
• 查看更多