Re: Export Oracle table (data) to 50000 records files (VBA conversion)

david.higgs_at_aldasys.demon.co.uk (David M Higgs) wrote in message news:<d1e250c4.0301131009.22ac7635_at_posting.google.com>...
> Dear All
>
> I have a table of employees (~320,000 records) that I need to export
> to text files in multiples of 50,000 records. (The reason: the
> destination system crashes when you try and load too much data in one
> go!!)
> The routine below (VBA) works fine via Access and ODBC but I would
> love to have it as an SQL script.
> Any takers? I've been trying for hours now and can't get it to work.
>
> Thanks very much in advance for all help.
>
> --
> Regards,
> David
>
> Sub ExportTable()
>
> 'declare variables
> Dim db As Database
> Dim rst As Recordset
> Dim sSQL As String
> Dim lngRecord As Long
> Dim iFileIndex As Integer
> Dim sFilename As String
>
> Set db = CurrentDb
>
> 'declare recordset
> sSQL = "SELECT * FROM EMPLOYEES;"
> Set rst = db.OpenRecordset(sSQL)
>
> rst.MoveFirst
> iFileIndex = 1
>
> On Error GoTo ExportSpodMart_Err
>
> While Not rst.EOF 'loop through all records
> sFilename = "I:\spod\spodmartdata\file-" & Format(iFileIndex,
> "00") & ".txt"
> Open sFilename For Output As #1
> For lngRecord = 1 To 50000
> Print #1, rst!FIRSTNAME & "|" & rst!LASTNAME & "|" &
> rst!TELEPHONE & "|" & rst!EMAIL
> rst.MoveNext
> Next lngRecord
> Close #1 'close file
> iFileIndex = iFileIndex + 1 'increment index
> Wend
>
> 'close recordset and release objects
> rst.Close
> Set rst = Nothing
> Set db = Nothing
>
> Exit Sub
>
> ExportSpodMart_Err:
> 'crashed out - run out of records!
> Close #1
>
> 'close recordset and release objects
> rst.Close
> Set rst = Nothing
> Set db = Nothing
>
> End Sub

Here is one untested way. Not sure how slow or fast it will be. Need to have utl_file_dir specified in init.ora.

DECLARE

   hFile_       UTL_FILE.FILE_TYPE;
   file_        VARCHAR2(25);
   directory_   VARCHAR2(255) := 'C:\Oracle\Oradata\Test\utlfile';
   counter_     NUMBER := 1;
   file_count_  NUMBER := 1;
   buffer_      VARCHAR2(200);
   CURSOR cur_ IS
      SELECT * FROM EMPLOYEES;

      IF counter_ = 1 THEN
         file_ :=  'out_file' || TO_CHAR(file_count_) || '.txt';
         hFile_ := UTL_FILE.FOPEN(directory_, file_, 'a');
      END IF;
      IF UTL_FILE.ISOPEN(hFile_) THEN
         buffer_ := rec_.firstname||'|'||rec_.lastname||'|'||rec_.telephone||'|'||rec_.email;
         UTL_FILE.PUT_LINE(hFile_, buffer_);
      END IF;
      counter_ := counter_ + 1;
      IF counter_ = 50000 THEN
         IF UTL_FILE.ISOPEN(hFile_) THEN
            UTL_FILE.FCLOSE(hFile_);
         END IF;
         counter_ := 1;
         file_count_ := file_count_ + 1;
      END IF;

      UTL_FILE.FCLOSE(hFile_);
   END IF;
END;
/

Regards
/Rauf Sarwar Received on Tue Jan 14 2003 - 01:13:03 CET