Home » SQL & PL/SQL » Client Tools » Exporting Result to Text file through Query
Exporting Result to Text file through Query [message #572522] Wed, 12 December 2012 22:36 Go to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Hi Experts,

I'm running this query on sql developer trying to export large file but its not executing.

set head off
spool c:\myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name where rownum<'10';
spool off
set head on


Error:- line 1: SQLPLUS Command Skipped: set head on

Kindly guide me please

Thanks and Regards
Re: Exporting Result to Text file through Query [message #572523 is a reply to message #572522] Wed, 12 December 2012 22:48 Go to previous messageGo to next message
sajeel_87
Messages: 3
Registered: July 2012
Location: Chennai
Junior Member

i am not sure why you have assigned the rownum as string,it should be like below
where rownum<10 

Re: Exporting Result to Text file through Query [message #572524 is a reply to message #572523] Wed, 12 December 2012 22:52 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Please do not consider rownum i'm just trying to export small file 1st then will hit large file....
set head off
spool c:\myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name;
spool off
set head on


Thanks and regards
Re: Exporting Result to Text file through Query [message #572526 is a reply to message #572524] Wed, 12 December 2012 23:00 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

How many records is there in the table?
Re: Exporting Result to Text file through Query [message #572527 is a reply to message #572526] Wed, 12 December 2012 23:12 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
sorry i'm not sure whether no. of records varies for exporting.... may be less than 20 lakhs records

[Updated on: Wed, 12 December 2012 23:12]

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572528 is a reply to message #572526] Wed, 12 December 2012 23:13 Go to previous messageGo to next message
sajeel_87
Messages: 3
Registered: July 2012
Location: Chennai
Junior Member

Run the above script in sqlplus. and try like below,
set head off
set spool on
spool c:\myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name;
spool off
set head on
Re: Exporting Result to Text file through Query [message #572563 is a reply to message #572528] Thu, 13 December 2012 04:28 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
i have only sql developer sql plus disabled in my pc
Re: Exporting Result to Text file through Query [message #572565 is a reply to message #572563] Thu, 13 December 2012 04:36 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
srikanth_d5 wrote on Thu, 13 December 2012 11:28
i have only sql developer sql plus disabled in my pc


This is VERY VERY BAD.

Anyway, did you try UTL_FILE package? (creating file at server side)

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_file.htm#BABGGEDF


Regards,
Dariyoosh

[Updated on: Thu, 13 December 2012 04:39]

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572566 is a reply to message #572565] Thu, 13 December 2012 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
  • run the select statement in SQL Developer
  • right-click the result and choose "Export"
  • set output format (such as CSV) as well as other parameters
  • export the result
Re: Exporting Result to Text file through Query [message #572609 is a reply to message #572566] Thu, 13 December 2012 23:59 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Sir,

when i'm doing like this for sample 100000 records

1.run the select statement in SQL Developer
2.right-click the result and choose "Export"
3.set output format (such as CSV) as well as other parameters
4.exporting the result

its fetching 100000 records but in file its inserting only around 50000 records.

Thanks and Regards

Re: Exporting Result to Text file through Query [message #572610 is a reply to message #572609] Fri, 14 December 2012 00:04 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
how can we reproduce what you claim?
Re: Exporting Result to Text file through Query [message #572611 is a reply to message #572610] Fri, 14 December 2012 00:11 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
i didn't understand, is my sql developer is not working properly ?
Re: Exporting Result to Text file through Query [message #572617 is a reply to message #572611] Fri, 14 December 2012 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe yes maybe not, how can we say?
As BlackSwan said: "how can we reproduce what you claim?".
What is your OS (version, service pack, edition, bit)?
What is your file system type (FAT, NTFS...)?
What is the size of the output file you got?
...

Regards
Michel
Re: Exporting Result to Text file through Query [message #572621 is a reply to message #572617] Fri, 14 December 2012 01:45 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Hello ,

Windows Vista
Service Pack 1
Processor=Intel(R)Core(TM)2 Duo CPU E8400 @ 3.00ghz
Memory(RAM)=2 gb
File System=NTFS
System type=32bit operating System

Oracle SQL Developer 1.2.1.32.13
when right click output excel or text file it shows in between 20 to 30 mb depends and varies upon table.

Thanks and regards
Re: Exporting Result to Text file through Query [message #572645 is a reply to message #572621] Fri, 14 December 2012 08:12 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh! SQL Developer 1.2.1?!? Maybe you should upgrade to the recent version (which is 3.2.2 at the moment). Your problems might automagically disappear.

Also, which Excel version do you use? Are you aware of restriction of number of records per sheet in older MS Excel versions (only 64K of records)?
Re: Exporting Result to Text file through Query [message #572957 is a reply to message #572645] Tue, 18 December 2012 23:45 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
so i should upgrade my sql developer. if i upgrade sql developer should i re-establish the connections/path etc,because i never installed in my PC.

Excel version is Microsoft Excel 2007.

Thanks and Regards
Re: Exporting Result to Text file through Query [message #572960 is a reply to message #572957] Wed, 19 December 2012 00:53 Go to previous message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Excel version is OK, so I'd go with SQL Developer upgrade.

I don't know what will it look like; normally, I'd expect upgraded version to retain previous version's settings. However, your version is much lower than the one you'd upgrade to, so ... no idea. Google a little bit, maybe you'll find the answer. Or, wait some more - someone here might know better.

[Updated on: Wed, 19 December 2012 00:53]

Report message to a moderator

Previous Topic: Not able to connect from client to Oracle Database in Unix
Next Topic: How to insert data from Sql server to oracle server.
Goto Forum:
  


Current Time: Fri Apr 18 13:57:57 CDT 2014

Total time taken to generate the page: 0.25339 seconds