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

Rauf
Thanks very much indeed.
I'm fairly new to Oracle - could you explain what you mean by 'Need to have utl_file_dir specified in init.ora.' If it helps: My machine (at work) only has the client files installed. I wish to export the data from a table in an Oracle database on another machine to text files on the D: drive on my computer.

--
Regards,
David


rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0301131613.6c284a00_at_posting.google.com>...


> david.higgs_at_aldasys.demon.co.uk (David M Higgs) wrote in message news:<d1e250c4.0301131009.22ac7635@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;

> BEGIN   

>    FOR rec_ IN cur_ LOOP

>       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;

>    END LOOP;

>    IF UTL_FILE.ISOPEN(hFile_) THEN

>       UTL_FILE.FCLOSE(hFile_);

>    END IF;   

> END;

> /

> 

> 

> Regards

> /Rauf Sarwar