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

Rauf
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_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0301140913.4a34e9d0_at_posting.google.com>...


> 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

> SQL> FROM employees;

> SQL> SPOOL OFF

> 

> Hope this helps.

> /Rauf Sarwar