create text files from oracle

Hi,

How can I create text file from oracle?

LVL 13
davidlars99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:

Use UTL_FILE package.
0
MikeOM_DBACommented:

-- OR --

use SPOOL command in SQL*Plus
0
actonwangCommented:
>>How can I create text file from oracle

     In which situation? Not clear about your question.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

konektorCommented:
utl_file creates a file on database server, the location of created file must be included into init.ora paramater utl_file_dir
text_io creates file on client but only in oracle forms
spool creates file on client, but only from SQL+
0
Harish_RajaniCommented:
As Mike said, there are two ways

1) Uses UTL_FILE Package if you want to generate file on the server. I am giving below an example

FUNCTION generate_test_file RETURN NUMBER
IS
CURSOR  curEmployee IS
SELECT      employee_id,
     employee_name
     employee_salary,
FROM  Emp;
--Line to output
        sOutputLine     VARCHAR2(4000);
        uFileHndl       UTL_FILE.FILE_TYPE;
        sFileName       VARCHAR2(30);
     sEmployeeid varchar2(30);
     sEmployeeName varchar2(50);
     sEmployeeSalary varchar2(30);
     sOutputLine varchar2(120);
     -- The directory you want file to be generated
        gs_TestDir   CONSTANT VARCHAR2(50):='c:\testdata\';
     c_comma VARCHAR2(2) :=',' ; -- Delimiter
BEGIN
OPEN curEmployee
sFileName := 'test.csv';
uFileHndl  := UTL_FILE.FOPEN(gs_TestDir,sFileName,'w');
LOOP
FETCH curEmployee INTO sEmployeeId,sEmployeeName,sEmployeeSalary;
EXIT WHEN curEmployee%NOTFOUND;
     sOutputLine  :=     sEmployeeId || c_comma ||
               sEmployeeName || c_comma ||
               sEmployeeSalary;
UTL_FILE.PUT_LINE(uFileHndl,sOutputLine);
END LOOP;
CLOSE   curEmployee;
-- Close the output file
     UTL_FILE.FCLOSE(uFileHndl);
RETURN 0;
EXCEPTION
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
          UTL_FILE.FCLOSE(uFileHndl);
          CLOSE   curEmployee;
                RETURN SQLCODE;
END generate_test_file;


Remember the directory that you want to create file in must be included in the parameter 'utl_file_dir'.

You can see this parameter by typing following command on SQL prompt
SQL > show parameters;

2) SPOOL command can be used to generate file both on Server as well as your PC. Use SQLPLUS for this purpose

SQL> spool c:\testfile.txt

SQL> select emp_id || ',' || emp_name || ',' || emp_salary from emp;

SQL > spool off;

This will create a file on your local harddisk with the name testfile.txt in C drive.

Rgds,
HR

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidlars99Author Commented:
what if oracle database is on Solaris and target directory is on windows? perhaps there are some network tricks....????
0
RCorfmanCommented:
If you can use tools on Solaris to MOUNT the windows directory, then you can do this using utl_file just by specifying the correct mount points for the file.  Also, you can use FTP to move the file in a pinch.  10G has FTP tools built into the database. Prior to that, you can use Java routines in the database, or there is a purely PL/SQL version using utl_tcp available at:
http://www.oracle-base.com/dba/miscellaneous/ftp.pks
http://www.oracle-base.com/dba/miscellaneous/ftp.pkb

Lastly, the above solutions are assuming you need to do this from INSIDE the database. You can also use tools like sqlplus and toad to generate files if you are running a client and just want to produce a file from withing the client. sqlplus does this without a problem using the spool command (as mentioned by Harish_Rajani first). It does this from wherever you are running the client to wherever you have mounted file systems or network drives.
0
davidlars99Author Commented:
RC, you mentioned 10G FTP tools. Are they in those links, or you are trying to say that I have three choices..?

1. 10G's FTP Tools
2. Java routines in the database
3. Purely PL/SQL version using utl_tcp

I'm sorry if I sound little buggy... :)
0
RCorfmanCommented:
3 Options that I could come up with off the top of my head (I'm flying out in a few minutes so I won't be online again till tonight at the earliest... sorry).  I have never used the 10g tools, but it is my understanding that there is a package (probably something like utl_ftp, but I can't look....) that does manage FTP from within pl/sql.

You would still write the files out using utl_file as mentioned in the prior posts, then you can use the various FTP methods to move them around. It is also possible, using Java routines to run OS commands, and you can get to FTP that way too, though I've never done it. For the Java methods, you use Java in the database instead of pl/sql (something else I haven't done, but I've seen many, many examples of doing that....).

The easiest way will be if you can get a system admin to mount the windows file system and just write out to it, but I'm not a unix/solaris expert so I'm not even sure how/if this is possible... but I suspect it is with the correct 3rd party tools.

Using the ftp routines on oracle-base is probably the easiest method if you can't get windows mounted at the os level.
0
ram_0218Commented:
>>what if oracle database is on Solaris and target directory is on windows? perhaps there are some network tricks....????

Doesnt matter. Just from your windows machine, open sqlplus fire those queries and spool them. You'll get them in your windows box.

I think HR already has given example to spool the output.
0
Harish_RajaniCommented:
As Rcorfman has suggested, you can mount the Windows filesystem on Unix using Samba.
Flavours of Samba are freely downloadable and installation instructions are available on sites like www.samba.org.
But I guess this job is best left to experts, if you have got a Unix Administrator, leave it to him.

Also using the SPOOL command is the easiest way of creating the text files on the local PC from the data in Oracle.

If the text file is to be generated using scheduled jobs ( as in crontab), and then you want the business user to be able to open the file on his PC, consider the MS-DOS FTP  batch file that would automatically connect to Unix Server and download the file on to the local machine.



Rgds,
HR
0
davidlars99Author Commented:
I think I'll go with oracle built in ftp
0
davidlars99Author Commented:
it will be a scheduled job to run every three hours and send created file over ftp.
0
davidlars99Author Commented:
thanks!
0
davidlars99Author Commented:
my init.ora file has no "utl_file_dir" parameter in it. how do i create one?
0
Harish_RajaniCommented:

try:

See if you have the priveleges to execute following:

alter system set utl_file_dir='/home/userA','/home/userB/' scope=spfile

Rgds,
HR


0
davidlars99Author Commented:
Thanks HR!

RCorfman,
I don't think that there's a built in FTP tool in 10g, but those SQL examples are awsome.  I mean UTL_TCP... it's a regular ftp client tool, which supports regular ftp commads like "SEND", "PUT" and so on... If you have ever programmed ftp, you can do it!

0
RCorfmanCommented:
Not my tool... The author is listed there. I had heard there was FTP package in 10g, but I guess not. I had heard it at OracleWorld before 10g came out that it would be there.... I guess it didn't emerge.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.