Export Oracle table (data) to 50000 records files (VBA conversion)
David M Higgs
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
Rauf Sarwar
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
David M Higgs
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_a...@hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.03011...@posting.google.com>...
Rauf Sarwar
> 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
David,
The above procedure will write a file on the server where database is
running. utl_file_dir=<Any directory on the server> is a static (Pre
9i.. dynamic as of 9i) parameter in the init.ora file which tells
Oracle on startup the directory to use with UTL_FILE package calls.
This directory has to be local to the server so if you want the files
on your machine...you would have to copy/ftp them to your D: drive
afterwards. Check with your DBA about utl_file_dir parameter on the
database. You can also see the value of this or any other parameter by
simply typing,
SQL> show parameter <parameter name>
If this is a one time thing then it is OK but if you want to
consistently write files to your local client drive and do not want to
copy/ftp them from the database server... then you can use some third
party tools like Toad from Quest Software or PL/SQL developer to
create files for you.
BTW... You can also use Sqlplus SPOOL keyword to create a file on your
local drive. However, it will dump everything in one file. e.g.
SQL> SET PAGESIZE 0
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SET TERMOUT OFF
SQL> SPOOL D:\MyLocalDir\spoolfile.spl
SQL> SELECT firstname||'|'||lastname||'|'||telephone||'|'||email
SQL> FROM employees;
SQL> SPOOL OFF
Hope this helps.
/Rauf Sarwar
David M Higgs
Thanks very much for all your help.
It works perfectly!
That is; I've got it to work on the NT plaform and exporting to where
I want it!
Now all I have to do is see if I can do the same on the Unix box - I'm
sure it will work - but I think I' going to have fun 'finding' where I
export the data to!
Thanks again.
--
Regards,
David
rs_a...@hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.03011...@posting.google.com>...