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.0301140319.347c3689_at_posting.google.com>...
> 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

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