Home » SQL & PL/SQL » SQL & PL/SQL » Extracting data from Oracle for use with SQL Loader
Extracting data from Oracle for use with SQL Loader [message #10290] Tue, 13 January 2004 12:04 Go to next message
LT
Messages: 29
Registered: August 2003
Junior Member
Hi All

can anyone help me with this. I want to extract data from one oracle database table to be loaded into an identical table in another oracle database. My SQL script looks like this...

spool c:adr.txt
set heading off
set feedback off
select '"'||user_name||'"' || ',' || report_date || ',' || '"'||member_code ||'"'|| ',' || total_outstanding || ',' || future_amount || ',' || current_amount || ',' || thirty_amount || ',' || sixty_amount || ',' || ninety_amount || ',' || due_now || ',' || '"'||out_of_sync||'"'|| ',' ||'"'|| policy_id||'"' || ','
from aged_debtors_report
where report_date like '01/JAN/04'
/

spool off

exit;

This is the result I want, however SQL puts a line break in say every thirteen records - so I get thirteen records one after the other then a line break and this continues all throughout the file. I am querying approximately 160,000 records so you can appreciate that it is not feasible for me to go through the file and remove the line breaks. It will take me ages.

Does anyone know how to stop SQL from putting the line breaks in?
Re: Extracting data from Oracle for use with SQL Loader [message #10291 is a reply to message #10290] Tue, 13 January 2004 19:51 Go to previous messageGo to next message
S.Rajesh Kannan
Messages: 26
Registered: September 2003
Junior Member
Hi,

can you please try "set pagesize 50000" also.

Regards
S.Rajesh Kannan
Re: Extracting data from Oracle for use with SQL Loader [message #10343 is a reply to message #10290] Thu, 15 January 2004 11:55 Go to previous message
Eric
Messages: 78
Registered: April 1999
Member
Or you can check OraExport from www.AgileInfoSoftware.com to export, it is multiple thread and export data in many formats including SQL*Loader.
Previous Topic: convert clob to blob
Next Topic: how to define a relationship between two tables in SQL?
Goto Forum:
  


Current Time: Fri Mar 29 03:51:29 CDT 2024