?
Solved

create text files from oracle

Posted on 2006-04-13
19
Medium Priority
?
2,457 Views
Last Modified: 2012-06-27
Hi,

How can I create text file from oracle?

0
Comment
Question by:davidlars99
  • 7
  • 3
  • 3
  • +4
19 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 600 total points
ID: 16450335

Use UTL_FILE package.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16450338

-- OR --

use SPOOL command in SQL*Plus
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16450412
>>How can I create text file from oracle

     In which situation? Not clear about your question.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 19

Expert Comment

by:actonwang
ID: 16450665
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 200 total points
ID: 16451623
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
 
LVL 4

Accepted Solution

by:
Harish_Rajani earned 400 total points
ID: 16452759
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
 
LVL 13

Author Comment

by:davidlars99
ID: 16452954
what if oracle database is on Solaris and target directory is on windows? perhaps there are some network tricks....????
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16453291
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
 
LVL 13

Author Comment

by:davidlars99
ID: 16454633
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
 
LVL 16

Assisted Solution

by:RCorfman
RCorfman earned 600 total points
ID: 16454778
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
 
LVL 17

Assisted Solution

by:ram_0218
ram_0218 earned 200 total points
ID: 16455832
>>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
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16456830
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
 
LVL 13

Author Comment

by:davidlars99
ID: 16457766
I think I'll go with oracle built in ftp
0
 
LVL 13

Author Comment

by:davidlars99
ID: 16457773
it will be a scheduled job to run every three hours and send created file over ftp.
0
 
LVL 13

Author Comment

by:davidlars99
ID: 16457787
thanks!
0
 
LVL 13

Author Comment

by:davidlars99
ID: 16457840
my init.ora file has no "utl_file_dir" parameter in it. how do i create one?
0
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16461776

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
 
LVL 13

Author Comment

by:davidlars99
ID: 16465550
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16465624
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question