高分求救！！！关于oracle报错！！

Question

当然不需要写啦,就是怕你西了,导致OUTPUT的BUFFER满了
你最好把你的SP贴出来看,才知道什么原因啊(希望别太长)

Answer 1

将init<sid>.ora中的这两个参数增大一下db_block_buffers db_block_size

Answer 2

不是吧,要修改init.ora就要重启数据库，我这里是双机？而且是在线运行中，在重启时，不能出任何问题，否则工作不保了，：）
create or replace procedure transfer
as
  /*Declare vairables for tblUserInfo fields*/
  usrRecordType             varchar2(2);
  usrTopLevelCustName       varchar2(128);
  usrSecondLevelCustName    varchar2(64);
  usrTelephoneNo            varchar2(32);
  usrAddress                varchar2(128);
  usrTelephoneType          varchar2(4);
  usrCustType               varchar2(2);
  usrDistrictCode           varchar2(8);
  usrBureauCode             varchar2(12);
  usrUserLevel              varchar2(2);
  usrUserStatus             varchar2(2);
  usrZipCode                varchar2(8);
  usrCustCallerNo           varchar2(30);
  usrPhoneInstallDate       varchar2(14);
  usrNewService             varchar2(128);  /* Declare variables for tblLineInfo fields*/
  lineRecordType            varchar2(2);
  lineTelephoneNo           varchar2(32);
  lineHorizColumnNo         varchar2(64);
  lineVerticalColumnNo      varchar2(64);
  lineSubscriberLineInfo    varchar2(1024);  /*Declare vairables for hw112 t_yhsjk*/
  hw112Dhhm                 varchar2(11); --Can be used in table t_yhxlsjk
  hw112Jx                   varchar2(6); --Can be used in table t_yhxlsjk  hw112Dhlx                 varchar2(2);
  hw112Yhjb                 varchar2(2);
  hw112Yhzt                 char(2);
  hw112Yhmc                 varchar2(100);
  hw112Yhdz                 varchar2(100);
  hw112Yzbm                 varchar2(6);
  hw112Yhbbjh               varchar2(20);
  hw112Zjsj                 date;
  hw112Xyw                  varchar2(30);
  hw112Yhlx                 char(2);
  hw112Sblx                 char(2);
  hw112Fjsb                 varchar2(20);
  hw112Sfyshm               char(1);
  hw112Zwbs                 char(1);
  hw112Yhfwlsh              varchar2(25); --Can be used in table t_yhxlsjk
  hw112Bz                   varchar2(100); --Can be used in table t_yhxlsjk  /*Declare variables for Huawei INtess-112 t_yhxlsjk*/
  hw112Jlh                  varchar2(10);
  hw112Jkh                  varchar2(10);
  hw112Jdh                  varchar2(10);
  hw112Zgdlh                varchar2(15);
  hw112Zgdlxx               varchar2(10);
  hw112Jjx1bm               varchar2(15);
  hw112J1_zgdlh             varchar2(10);
  hw112J1_zgxx              varchar2(10);
  hw112J1_pxdlh             varchar2(10);
  hw112J1_pxxx              varchar2(10);
  hw112J1_dz                varchar2(50);
  hw112J1_jl                numeric(10,2);
  hw112Jjx2bm               varchar2(15);
  hw112J2_zgdlh             varchar2(10);
  hw112J2_zgxx              varchar2(10);
  hw112J2_pxdlh             varchar2(10);
  hw112J2_pxxx              varchar2(10);
  hw112J2_dz                varchar2(50);
  hw112J2_jl                numeric(10,2);
  hw112Fxxbm                varchar2(15);
  hw112Fxxdlh               varchar2(10);
  hw112Fxxxxx                varchar2(10);
  hw112Fxxdz                varchar2(50);
  hw112Fxxjl                numeric(10,2);
  hw112Xllx                 char(2);
  hw112Dluh                  varchar2(25);
  hw112Sbh                  varchar2(15);
  hw112Yhhlbs               varchar2(25);
  /*Some other variables used by this procedure*/
  chNumericalSymbols        char;
  cnt                       integer;
  bitset                    integer;
  len                       integer;
  pos                       integer;
  pos1                      integer;
  pos2                      integer;
  tmpStr                    varchar2(1024);
  tmpStrAddress             varchar2(256);
  tmpStrName                varchar2(256);
  tmpStrLineInfo            varchar2(256);
  hwZgdlhFromHoriz          varchar2(15);
  hwZgdlhFromVert           varchar2(15);
  hwJlhFromHoriz            varchar2(10);
  hwJlhFromVert             varchar2(10);
  hwNewServiceCode          varchar2(2);

Answer 3

begin
  /*Transfer the record in table tblUserInfo to t_yhsjk*/
  /*First declare a cursor of this table*/
  declare cursor curUserInfo is
     select fldRecordType,   fldTopLevelCustName, fldSecondLevelCustName, fldTelephoneNo,
            fldAddress,      fldTelephoneType,    fldCustType,            fldDistrictCode,
            fldBureauCode,   fldUserLevel,        fldUserStatus,          fldZipCode,
            fldCustCallerNo, fldPhoneInstallDate, fldNewService
     from   tblUserInfo order by fldTelephoneNo;
  begin
     /*Open the cursor curUserInfo*/
     open curUserInfo;
     loop
        fetch curUserInfo into
           usrRecordType,   usrTopLevelCustName,  usrSecondLevelCustName,  usrTelephoneNo,
           usrAddress,      usrTelephoneType,     usrCustType,             usrDistrictCode,
           usrBureauCode,   usrUserLevel,         usrUserStatus,           usrZipCode,
           usrCustCallerNo, usrPhoneInstallDate,  usrNewService;
     exit when curUserInfo%NOTFOUND;        /*First to check if the telephone number string is valid or not*/
        chNumericalSymbols := '0';
        chNumericalSymbols := isdigit(ltrim(rtrim(usrTelephoneNo)));
        if ( chNumericalSymbols = '0') then /*Valid one, process it, if invalid fetch next record directly*/
           if usrRecordType = '2' then /*Delete type*/
              /*Delete the record from the table t_yhsjk*/
              begin
                 delete from t_yhsjk where dhhm = ltrim(rtrim(usrTelephoneNo));
              exception
                 when others then
                   rollback;
              end;
           else
              /* Get the bureau from Huawei INtess-112's t_jxk*/
              hw112Jx := null;
              begin
                 select ltrim(rtrim(jxdm)) into hw112Jx from t_jxk
                 where  ltrim(rtrim(usrTelephoneNo)) like hmt||'%' and rownum = 1;
                 exception
                     when others then
                       hw112Jx := null;
              end;
             if (hw112Jx is not null) then  /*If the jx is null, skip this record and process next record directly*/
                 hw112Dhhm := ltrim(rtrim(usrTelephoneNo));
                 hw112Dhlx := '00'; --Set it to tone telephone default
                 hw112Yhmc := ltrim(rtrim(usrSecondLevelCustName));
                 hw112Yhdz := ltrim(rtrim(usrAddress));
                 hw112Yzbm := ltrim(rtrim(usrZipCode));
                 hw112Yhbbjh := ltrim(rtrim(usrCustCallerNo));
                 hw112Zjsj := to_date(usrPhoneInstallDate, 'YYYYMMDDHH24MISS');
                 hw112Xyw := null;
                 hw112Fjsb := null;
                 hw112Sfyshm := 'N';
                 hw112Zwbs := 'N';
                 hw112Yhfwlsh := null;
                 hw112Bz := null;                 /*Map the Neusoft's user level to Huawei INtess-112 by looking
                  up the table tblCustLevelMap*/
                  begin
                     select ltrim(rtrim(fldHwCustLevelCode)) into hw112Yhjb from tblCustLevelMap
                     where  fldNsCustLevelCode = ltrim(rtrim(usrUserLevel));
                  exception
                     when others then
                        hw112Yhjb := '00'; /*Set to default '00' */
                  end;                  /*Map the Neusoft's user status to Huawei INtess-112 by looking
                  up the table tblCustStatusMap*/
                  begin
                      select ltrim(rtrim(fldHwCustStatusCode)) into hw112Yhzt from tblCustStatusMap
                      where fldNsCustStatusCode = ltrim(rtrim(usrUserStatus));
                  exception
                      when others then
                         hw112Yhjb := '00'; /* Set default to normal state*/
                  end;                  /*Map the Neusoft's telephone type to Huawei INtess-112 by looking
                  up the table tblTelephoneTypeMap */
                  begin
                      select ltrim(rtrim(fldHwDeviceTypeCode)) into hw112Sblx from tblTelephoneTypeMap
                      where fldNsPhoneTypeCode = ltrim(rtrim(usrTelephoneType));
                  exception
                      when others then
                         hw112Sblx := '00'; /* Set default to normal state*/
                  end;                  /*Map the Neusoft's customer type to Huawei INtess-112 by looking
                  up the table tblCustTypeMap */
                  begin
                      select ltrim(rtrim(fldHwCustTypeCode)) into hw112Yhlx from tblCustTypeMap
                      where fldNsCustTypeCode = ltrim(rtrim(usrCustType));
                  exception
                      when others then
                         hw112Yhlx := '01';
                  end;

Answer 4

/*Analyse the new service*/
                  pos1 := 0;
                  bitset := 1;
                  usrNewService := ltrim(rtrim(usrNewService));
                  hw112Xyw := null;
                  while (bitset = 1) loop
                    pos := instr(usrNewService, '1', pos1+1);
                    if (pos <> 0) then
                       bitset := 1;
                       pos1 := pos;
                       hwNewServiceCode := null;
                       begin
                         select ltrim(rtrim(fldHwNewServiceCode)) into hwNewServiceCode from tblNewServiceMap
                         where  fldNsNewServiceCode = pos;
                       exception
                          when others then
                            hwNewServiceCode := null;
                       end;
                       hw112Xyw := concat(hw112Xyw, hwNewServiceCode);
                    else
                       bitset := 0;
                    end if;
                  end loop;
                  /*Initialize cnt to 0*/
                  cnt := 0;
                  select count(*) into cnt from t_yhsjk
                  where dhhm = hw112Dhhm;                  if (cnt > 0) then
                    begin
                       update t_yhsjk set
                         dhhm    = hw112Dhhm,
                         jx      = hw112Jx,
                         dhlx    = hw112Dhlx,
                         yhjb    = hw112Yhjb,
                         yhzt    = hw112Yhzt,
                         yhmc    = hw112Yhmc,
                         yhdz    = hw112Yhdz,
                         yzbm    = hw112Yzbm,
                         yhbbjh  = hw112Yhbbjh,
                         zjsj    = hw112Zjsj,
                         xyw     = hw112Xyw,
                         yhlx    = hw112Yhlx,
                         sblx    = hw112Sblx,
                         fjsb    = hw112Fjsb,
                         sfyshm  = hw112Sfyshm,
                         zwbs    = hw112Zwbs,
                         yhfwlsh = hw112Yhfwlsh,
                         bz      = hw112Bz
                      where dhhm = hw112Dhhm;
                    exception
                       when others then
                         rollback;
                    end;
                  else
                    begin
                       insert into t_yhsjk(dhhm, jx, dhlx, yhjb, yhzt, yhmc, yhdz,
                                           yzbm, yhbbjh, zjsj, xyw, yhlx, sblx, fjsb,
                                           sfyshm, zwbs, yhfwlsh, bz)
                                    values(hw112Dhhm, hw112Jx, hw112Dhlx, hw112Yhjb, hw112Yhzt,
                                           hw112Yhmc, hw112Yhdz, hw112Yzbm, hw112Yhbbjh, hw112Zjsj,
                                           hw112Xyw,  hw112Yhlx, hw112Sblx, hw112Fjsb, hw112Sfyshm,
                                           hw112Zwbs, hw112Yhfwlsh, hw112Bz);                    exception
                        when others then
                          rollback;
                    end;
                  end if; /* end of If cnt > 0 */
              end if; /* end of If jx is not null */
          end if;  /* end of If usrRecordType = '2'*/
        end if; /*end of If cNumericalSymbol = '0'*/
     end loop;     /*Has processed the table tblUserInfo, then close the cursor*/
     close curUserInfo;     /*Delete the processed record from tblUserInfo*/
     delete from tblUserInfo;
     /*Commit the changes*/
     commit;
   end;

Answer 5

怀疑SHARE_POOL，可以试试alter system flush shared_pool;
不过不保证成功

Answer 6

在存储过程里对每条记录循环处理就会有这个问题，原因我不是太清楚，你可以尝试这样做：
采用批量处理的方式，而不是一次提交一条记录来做。将存储过程改写成函数如myFun(Deal With tblUserInfo)取出数据后，批量插入到t_yhsjk表中：
insert into t_yhsjk select * from select a, b, myFun(...) from tblUserInfo。

Answer 7

可是，现在有没有办法释放一些buffer出来？？除了重启数据库！！！

Answer 8

alter system flush shared_pool;
这句就是清空SHARE_POOL的啊，不用重启的。