Home » RDBMS Server » Server Utilities » Urgent - Exporting Data to Flat file
Urgent - Exporting Data to Flat file [message #368161] Mon, 18 September 2000 05:23 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #368322 is a reply to message #368164] Fri, 10 November 2000 21:27 Go to previous messageGo to next message
Al Martin
Messages: 1
Registered: November 2000
Junior Member
HELP - How would I do this in UNIX? that is run a procedure that SELECTS and returns the result into a log file?
Re: Urgent - Exporting Data to Flat file [message #368325 is a reply to message #368164] Mon, 13 November 2000 08:10 Go to previous message
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
Previous Topic: SQL*Loder Problem
Next Topic: Backup while Databse wasn't shutdown
Goto Forum:
  


Current Time: Thu Dec 09 08:39:18 CST 2021