create or replace procedure P_SYS_PRINT_DDL
(i_clb_input in clob,
 i_vc_srcowner in varchar2,
 i_vc_srcownerdefaulttbs in varchar2,
 i_vc_srcindextbs in varchar2,
 i_vc_tarowner in varchar2,
 i_vc_tarownerdefaulttbs in varchar2,
 i_vc_tarindextbs in varchar2
 ) is
/*
功能： 一行一行print传入的clob的内容，
        print的内容中如果包含字符串"i_vc_srcowner".，则将其转换为"i_vc_tarowner".
        print的内容中如果包含字符串TABLESPACE "i_vc_srcownerdefaulttbs"，则将其转换为TABLESPACE "i_vc_tarownerdefaulttbs"
        print的内容中如果包含字符串TABLESPACE "i_vc_srcindextbs"，则将其转换为TABLESPACE "i_vc_tarindextbs"

作者： cuihua
创建日期：2009-03-19

输入参数：
i_clb_input：传入的clob
i_vc_srcowner：IPRA开发环境的用于比较差异的指定schema名称
i_vc_srcownerdefaulttbs: IPRA开发环境的用于比较差异的指定schema所在的default tablespace
i_vc_srcindextbs: IPRA开发环境的用于比较差异的指定schema的index所在的tablespace
i_vc_tarowner: IPRA测试环境的用于比较差异的指定schema名称
i_vc_tarownerdefaulttbs: IPRA测试环境的用于比较差异的指定schema所在的default tablespace
i_vc_tarindextbs：IPRA测试环境的用于比较差异的指定schema的index所在的tablespace

输出参数：
无

输入输出参数：
无

调用到的存储过程：
无
*/

  offset number;
  len    number;
  o_buf  varchar2(4000);
  amount number;                    --}
  f_amt  number := 0;               --}To hold the amount of data
  f_amt2 number;                    --}to be read or that has been
  amt2   number := -1;              --}read
  i number;

begin

  len := DBMS_LOB.GETLENGTH(i_clb_input);
  offset := 1;
  while len > 0 loop
     /*
     SQL> desc DBMS_LOB.INSTR;
      Parameter Type     Mode Default?
      --------- -------- ---- --------
      (RESULT)  NUMBER
      LOB_LOC   BLOB     IN
      PATTERN   RAW      IN
      OFFSET    NUMBER   IN   Y
      NTH       NUMBER   IN   Y
      (RESULT)  NUMBER
      LOB_LOC   CLOB     IN
      PATTERN   VARCHAR2 IN
      OFFSET    NUMBER   IN   Y
      NTH       NUMBER   IN   Y
      (RESULT)  NUMBER
      FILE_LOC  BFILE    IN
      PATTERN   RAW      IN
      OFFSET    NUMBER   IN   Y
      NTH       NUMBER   IN   Y
     */
     amount := DBMS_LOB.INSTR(i_clb_input,chr(10),offset,1);

     --Amount returned is the count from the start of the file,
     --not from the offset.
     if amount = 0 then
        --No more linefeeds so need to read remaining data.
        amount := len;
        amt2 := amount;
     else
        f_amt2 := amount;           --Store position of next LF
        amount := amount - f_amt;   --Calc position from last LF
        f_amt := f_amt2;            --Store position for next time
        amt2 := amount - 1;         --Read up to but not the LF
     end if;

     if amt2 != 0 then
        --If there is a linefeed as the first character then ignore.
        /*
        SQL> desc DBMS_LOB.READ;
          Parameter Type     Mode   Default?
          --------- -------- ------ --------
          LOB_LOC   BLOB     IN
          AMOUNT    NUMBER   IN OUT
          OFFSET    NUMBER   IN
          BUFFER    RAW      OUT
          LOB_LOC   CLOB     IN
          AMOUNT    NUMBER   IN OUT
          OFFSET    NUMBER   IN
          BUFFER    VARCHAR2 OUT
          FILE_LOC  BFILE    IN
          AMOUNT    NUMBER   IN OUT
          OFFSET    NUMBER   IN
          BUFFER    RAW      OUT
        */
        DBMS_LOB.READ(i_clb_input,amt2,offset,o_buf);

        --print的内容中如果包含字符串"i_vc_srcowner".，则将其转换为"i_vc_tarowner".
        if( instr(o_buf, '"' || i_vc_srcowner || '"' || '.') > 0 ) then
          o_buf := replace(o_buf,'"' || i_vc_srcowner || '"' || '.','"' || i_vc_tarowner || '"' || '.');
        end if;

        --print的内容中如果包含字符串TABLESPACE "i_vc_srcownerdefaulttbs"，则将其转换为TABLESPACE "i_vc_tarownerdefaulttbs"
        if( instr(o_buf, 'TABLESPACE "' || i_vc_srcownerdefaulttbs || '"') > 0 ) then
          o_buf := replace(o_buf,'TABLESPACE "' || i_vc_srcownerdefaulttbs || '"','TABLESPACE "' || i_vc_tarownerdefaulttbs || '"');
        end if;

        --print的内容中如果包含字符串TABLESPACE "i_vc_srcindextbs"，则将其转换为TABLESPACE "i_vc_tarindextbs"
        if( instr(o_buf, 'TABLESPACE "' || i_vc_srcindextbs || '"') > 0 ) then
          o_buf := replace(o_buf,'TABLESPACE "' || i_vc_srcindextbs || '"','TABLESPACE "' || i_vc_tarindextbs || '"');
        end if;

        --print到最后一行的时候如果是空行，则不print该空行
        if ( amount = 1 ) then
          if ( o_buf = chr(32) ) then
            exit;
          end if;
        end if;

        if ( instr(o_buf,'CREATE OR REPLACE PACKAGE BODY') > 0 ) then
          dbms_output.put_line('/');
          dbms_output.put_line(chr(10));
        end if;

        if ( instr(o_buf,'CREATE OR REPLACE TYPE BODY') > 0 ) then
          dbms_output.put_line('/');
          dbms_output.put_line(chr(10));
        end if;

        if ( instr(o_buf,'ALTER TRIGGER') > 0 ) then
          dbms_output.put_line('/');
          dbms_output.put_line(chr(10));
        end if;

        --这里处理掉"dbms_output.put_line"不能处理超过255个字符的限制
        --注意这里要用substrb，因为所处理的行中可能含有中文
        --这里考虑到了断行的问题，并没有强行从254处截断
        loop        
        exit when o_buf is null; 
        i := 254;
                
        if( lengthb(o_buf) > 254 ) then
          while 1=1 loop  
          if ( substrb( o_buf,i,1 ) != ' ') then
            i := i-1;
          else
            exit;
          end if;
          end loop;
        end if;         
               
        dbms_output.put_line( substrb( o_buf, 1, i ) );
        o_buf := substrb( o_buf, i+1 );        
        end loop;

        --dbms_output.put_line(o_buf);

     end if;

     len := len - amount;
     offset := offset+amount;
  end loop;

end P_SYS_PRINT_DDL;
/