OraFAQ Forum: Client Tools » Exporting Result to Text file through Query

Home » Other » Client Tools » Exporting Result to Text file through Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Exporting Result to Text file through Query [message #572522]

Wed, 12 December 2012 22:36

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572523 is a reply to message #572522]

Wed, 12 December 2012 22:48

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572524 is a reply to message #572523]

Wed, 12 December 2012 22:52

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572526 is a reply to message #572524]

Wed, 12 December 2012 23:00

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

How many records is there in the table?

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572527 is a reply to message #572526]

Wed, 12 December 2012 23:12

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

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572563 is a reply to message #572528]

Thu, 13 December 2012 04:28

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

i have only sql developer sql plus disabled in my pc

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572565 is a reply to message #572563]

Thu, 13 December 2012 04:36

dariyoosh
Messages: 538
Registered: March 2009
Location: 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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572609 is a reply to message #572566]

Thu, 13 December 2012 23:59

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572610 is a reply to message #572609]

Fri, 14 December 2012 00:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

how can we reproduce what you claim?

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572611 is a reply to message #572610]

Fri, 14 December 2012 00:11

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

i didn't understand, is my sql developer is not working properly ?

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572617 is a reply to message #572611]

Fri, 14 December 2012 01:26

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572621 is a reply to message #572617]

Fri, 14 December 2012 01:45

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572645 is a reply to message #572621]

Fri, 14 December 2012 08:12

Littlefoot
Messages: 21823
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)?

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572957 is a reply to message #572645]

Tue, 18 December 2012 23:45

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

Report message to a moderator

Re: Exporting Result to Text file through Query [message #572960 is a reply to message #572957]

Wed, 19 December 2012 00:53

Littlefoot
Messages: 21823
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 00:52:25 CST 2025