| Urgent - Exporting Data to Flat file [message #368161] |
Mon, 18 September 2000 05:23  |
Chella
Messages: 25
Registered: September 2000
|
Junior Member |
|
|
I have to export records off from a table to a flat file based on few conditions. Say the select statememt looks something like this;
SELECT TransCode,(Amount*100)
FROM tblMailBoxData
WHERE TransCode = 11;
This will be a scheduled job. I tried creating a script file but seem to be having problem running it.
What is the best way to export data from Oracle table to a flat file ? Help much appreciated as I am a begiiner to Oracle on the whole.
|
|
|
|
| Re: Urgent - Exporting Data to Flat file [message #368164 is a reply to message #368161] |
Tue, 19 September 2000 11:44  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi there,
Flat file mean ASCII text file?
If you want to parse the result of your query
into text file you can write a script like this.
This batch script is only for Windows NT.
Create a bat file 'exp.bat' with these lines.
C:\<ORACLE_HOME>\BIN\PLUS80W USER/PW@DB1 @C:\SQLSCRIPTS\DAILYEXP.SQL
create a sql script under the path you have mensioned in the exp.bat file
DAILYEXP.SQL
set head off;
set linesize 100;
set page size 1000;
spool c:\sqlscripts\dailyexp.txt
SELECT TransCode || ',' ||(Amount*100) FROM tblMailBoxData
WHERE TransCode = 11;
spool off;
Modify the directory paths and other set command values as per your requirement.
Now by clicking the bat file the results for your
query will be exported into to dailyexp.txt file in comma delimited text format.
Which can be used for reload or any other purpose.
If you want to schedule it you can use
Windows NT's AT command.
Is that what you want?
Bala
|
|
|
|
| Thank U. OEM or NT to schedule ? [message #368168 is a reply to message #368164] |
Wed, 20 September 2000 04:39 |
Chella
Messages: 25
Registered: September 2000
|
Junior Member |
|
|
Hi
Thank U again for the help. I have already done the text export but using the batch file can be useful for me when I have to do the scheduling bit. Should I use the OEM or NT (AT command) to perform the scheduling ? I have no knowledge about either of them. Would U be able to suggest any useful tips ?
Chella R
|
|
|
|
| Re: Thank U. OEM or NT to schedule ? [message #368169 is a reply to message #368164] |
Wed, 20 September 2000 07:42 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi
I haven't used OEM much so i don't know
how easy it is to schedule a job.
If you want to do OS commands as well like
changing directory, moving files etc, then
NT BAT files are best.
You can either use AT command or you will get
'schedule tasks' a gui wizard along with IE5.0
upgradation for Win NT.
Bala
|
|
|
|
|
|
| Re: Urgent - Exporting Data to Flat file [message #368325 is a reply to message #368164] |
Mon, 13 November 2000 08:10 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
If you want to spool the results from a procedure,
then you should use utl_file package, a oracle supplied package for os file handling.
This procedure will select empno and ename from table scott.emp and spool the results in emp.txt.
create or replace procedure select_rows is
file_id utl_file.file_type;
in_row varchar2(200);
h_file_name varchar2(30);
cursor emp_cur is
select * from scott.emp;
emp_rec emp_cur%rowtype;
--
begin
h_file_name := 'emp.txt';
file_id := utl_file.fopen('c:\oracle\admin\testdb01\utl_files',h_file_name,'w');
for r1 in emp_cur loop
emp_rec := r1;
utl_file.put_line(file_id, emp_rec.empno || emp_rec.ename);
exit when emp_cur%notfound;
end loop;
utl_file.fclose(file_id);
end;
/
since its a stored procedure, it can be executed from any platform unix or NT etc......
and if you want to schedule the procedure to run in a timely manner then you can use dbms_job package.
Thanks
Bala
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
