导出数据时报错 'temporary table resource limit'(100分)

  • 主题发起人 主题发起人 偶尔幽默
  • 开始时间 开始时间

偶尔幽默

Unregistered / Unconfirmed
GUEST, unregistred user!
用ODBC连接,ORACLE数据库。Delhpi中创建TABLE,然后把查询的数据添加到TABLE表中,<br>由于数据量太大(87万),所以当TABLE中添加了20万条记录时,就再创建一TABLE, 以此类推。问题:当循环到457856条记录执行NEXT操作时报 'temporary &nbsp;table resource limit'错误,请问该如何解决。源码如下:<br>Function Tf_main.SaveQueryToTable(aQuery:TQuery;xh:Integer;SourceTableName,destTableName:String):Boolean;<br>var<br>&nbsp; &nbsp;i,rno,tcount:Integer;<br>&nbsp; &nbsp;tmpdb,tmpname:string;<br>&nbsp; &nbsp;msg:TMsg;<br>&nbsp; &nbsp;tmpstr:String;<br>&nbsp; &nbsp;j,n: integer;<br>begin<br>&nbsp; &nbsp; &nbsp;Result:=True;<br><br>&nbsp; &nbsp; &nbsp;tmpdb:=ExtractFilePath(destTableName);<br>&nbsp; &nbsp; &nbsp;tmpname:=ExtractFileName(destTableName);<br><br>&nbsp; &nbsp; &nbsp;DelTable(tmpdb,tmpName);<br><br>&nbsp; &nbsp; &nbsp;tmpTable.Close;<br>&nbsp; &nbsp; &nbsp;tmpTable.DatabaseName:=tmpdb;<br>&nbsp; &nbsp; &nbsp;tmpTable.TableName:=tmpname;<br>&nbsp; &nbsp; &nbsp;tmpTable.TableType:=ttParadox;<br>&nbsp; &nbsp; &nbsp;tmpTable.FieldDefs.Clear;<br>&nbsp; &nbsp; &nbsp;For i:=0 to aQuery.FieldDefs.Count-1 do begin<br>&nbsp; &nbsp; &nbsp; &nbsp; case aQuery.FieldDefs.DataType of<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ftBlob,ftOraBlob:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.fielddefs.add(aQuery.FieldDefs.Name,ftBlob,0,aQuery.FieldDefs.Required);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ftDate,ftDateTime,ftTime:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.fielddefs.add(aQuery.FieldDefs.Name,ftDateTime,0,aQuery.FieldDefs.Required);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;else begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.fielddefs.add(aQuery.FieldDefs.Name,aQuery.FieldDefs.DataType<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,aQuery.FieldDefs.Size,aQuery.FieldDefs.Required);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp;end;//for i<br>&nbsp; &nbsp; &nbsp;tmpTable.IndexDefs.Clear;<br>&nbsp; &nbsp; &nbsp;Try<br>&nbsp; &nbsp; &nbsp; &nbsp; TmpTable.CreateTable;<br>&nbsp; &nbsp; &nbsp; &nbsp; except<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Result:=False;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ErrorMessage('创建表'+destTableName+'时出错,备份失败!');<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SaveLog(tkeyword,toperation,ttitle,false);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Exit;<br>&nbsp; &nbsp; &nbsp;end;<br><br>&nbsp; &nbsp; &nbsp;tmpTable.Open;<br><br>&nbsp; &nbsp; &nbsp;tcount:=GetRecordCount(aQuery);<br>&nbsp; &nbsp; &nbsp;f_wait.ProgressBar1.Max:=tcount;<br>&nbsp; &nbsp; &nbsp;rno:=0;<br>&nbsp; &nbsp; &nbsp;j := 0;<br>&nbsp; &nbsp; &nbsp;n := 0;<br>&nbsp; &nbsp; &nbsp;aQuery.First;<br>&nbsp; &nbsp; &nbsp;aQuery.DisableControls;<br>&nbsp; &nbsp; &nbsp;While Not aQuery.Eof do begin<br>&nbsp; &nbsp; &nbsp; &nbsp; f_wait.ProgressBar1.Position:=rno;<br>&nbsp; &nbsp; &nbsp; &nbsp; Inc(rno);<br>&nbsp; &nbsp; &nbsp; &nbsp; inc(n);<br>&nbsp; &nbsp; &nbsp; &nbsp; f_wait.Refresh;<br>&nbsp; &nbsp; &nbsp; &nbsp;// While PeekMessage(msg,0,0,0,PM_REMOVE) do DispatchMessage(msg);<br>&nbsp; &nbsp; &nbsp; &nbsp; application.ProcessMessages;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; F_WAIT.Label2.Caption := IntToStr(rno);<br><br>&nbsp; &nbsp; &nbsp; &nbsp;// IF rno = 457856 then<br>&nbsp; &nbsp; &nbsp; &nbsp;// &nbsp; showmessage('11111111111111111111111');<br><br>&nbsp; &nbsp; &nbsp; &nbsp; if n = 200001 then<br>&nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tmpTable.Close;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if CreateTable(aQuery,destTableName+'_'+IntToStr(j)) then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; n := 0;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; inc(j);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tmpTable.Open;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end else<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Result := False;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmptable.close;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ErrorMessage('创建表'+destTableName+'时出错,备份失败!');<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SaveLog(tkeyword,toperation,ttitle,false);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Exit;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; end;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; tmpTable.Append;<br>&nbsp; &nbsp; &nbsp; &nbsp; For i:=0 to aQuery.FieldDefs.Count-1 do begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tmpname:=aQuery.FieldDefs.Name;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case aQuery.FieldDefs.DataType of<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ftBlob,ftOraBlob:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if Not TBlobField(aQuery.FieldByName(tmpname)).IsNull then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TBlobField(tmpTable.FieldByName(tmpname)).Assign(aQuery.FieldByName(tmpname));<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ftDateTime,ftDate,ftTime:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if Not aQuery.FieldByName(tmpname).IsNull then begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Try<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tmpTable.FieldByName(tmpname).AsdateTime:=aQuery.FieldByName(tmpname).AsDateTime;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Except<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tmpTable.FieldByName(tmpname).AsString:=aQuery.FieldByName(tmpname).AsString;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; end;//for i<br>&nbsp; &nbsp; &nbsp; &nbsp; Try<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.Post;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Except<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tmpTable.Close;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Result:=False;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ErrorMessage('向表'+desttableName+'中保存数据时出错,备份失败!'<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //Memo1.Lines.Add('向表'+desttableName+'中保存数据时出错,备份失败!');<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SaveLog(tkeyword,toperation,ttitle,false);<br>&nbsp; &nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp; &nbsp; &nbsp; aQuery.Next;<br>&nbsp; &nbsp; &nbsp;end;//while<br>&nbsp; &nbsp; &nbsp;aQuery.EnableControls;<br>&nbsp; &nbsp; &nbsp;tmpTable.Close;<br>end;<br><br>function Tf_main.CreateTable(aQuery: TQuery; destTableName: String): Boolean;<br>var<br>&nbsp; &nbsp;i,rno,tcount:Integer;<br>&nbsp; &nbsp;tmpdb,tmpname:string;<br>&nbsp; &nbsp;msg:TMsg;<br>&nbsp; &nbsp;tmpstr:String;<br>begin<br>&nbsp; &nbsp;Result:=True;<br><br>&nbsp; &nbsp;tmpdb:=ExtractFilePath(destTableName);<br>&nbsp; &nbsp;tmpname:=ExtractFileName(destTableName);<br><br>&nbsp; &nbsp;DelTable(tmpdb,tmpName);<br><br>&nbsp; &nbsp;tmpTable.Close;<br>&nbsp; &nbsp;tmpTable.DatabaseName:=tmpdb;<br>&nbsp; &nbsp;tmpTable.TableName:=tmpname;<br>&nbsp; &nbsp;tmpTable.TableType:=ttParadox;<br>&nbsp; &nbsp;tmpTable.FieldDefs.Clear;<br>&nbsp; &nbsp;For i:=0 to aQuery.FieldDefs.Count-1 do begin<br>&nbsp; &nbsp; &nbsp; case aQuery.FieldDefs.DataType of<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ftBlob,ftOraBlob:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.fielddefs.add(aQuery.FieldDefs.Name,ftBlob,0,aQuery.FieldDefs.Required);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ftDate,ftDateTime,ftTime:<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.fielddefs.add(aQuery.FieldDefs.Name,ftDateTime,0,aQuery.FieldDefs.Required);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;else begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tmpTable.fielddefs.add(aQuery.FieldDefs.Name,aQuery.FieldDefs.DataType<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,aQuery.FieldDefs.Size,aQuery.FieldDefs.Required);<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br>&nbsp; &nbsp; &nbsp; end;<br>&nbsp; &nbsp;end;//for i<br>&nbsp; &nbsp;tmpTable.IndexDefs.Clear;<br>&nbsp; &nbsp;Try<br>&nbsp; &nbsp; &nbsp; TmpTable.CreateTable;<br>&nbsp; &nbsp; &nbsp; except<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Result:=False;<br>&nbsp; &nbsp;end;<br><br>end;
 
看所提示的错误貌似为临时表的记录数有限制。<br>要不楼主先将创建表的语句提交,再去导入数据。<br>或者分几个数据集导。。。
 
后退
顶部