create text files from oracle
Hi,
How can I create text file from oracle?
How can I create text file from oracle?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>How can I create text file from oracle
In which situation? Not clear about your question.
In which situation? Not clear about your question.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what if oracle database is on Solaris and target directory is on windows? perhaps there are some network tricks....????
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.
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.
ASKER
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... :)
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... :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
I think I'll go with oracle built in ftp
ASKER
it will be a scheduled job to run every three hours and send created file over ftp.
ASKER
thanks!
ASKER
my init.ora file has no "utl_file_dir" parameter in it. how do i create one?
try:
See if you have the priveleges to execute following:
alter system set utl_file_dir='/home/userA'
Rgds,
HR
ASKER
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!
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!
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.
-- OR --
use SPOOL command in SQL*Plus