Home » SQL & PL/SQL » SQL & PL/SQL » Spooling every 25 rows in excel (Oracle 10g)
Spooling every 25 rows in excel [message #568385] Thu, 11 October 2012 05:00 Go to next message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
Hi,
I need to create a program wherein it will create an excel files through spool command for every 25 records only.
So if my select statement will return 100 records, it will create 4 excel files, 25 records for each excel file.
i have these sample codes,it should be the logic, but then i'm having syntax error. hope you can assists me.
set serveroutput on size 1000000
SET PAGESIZE 0
SET LINESIZE 299
SET FEEDBACK OFF
execute DBMS_OUTPUT.ENABLE(1000000)
/

declare
	ctr 	number;
	page_ctn number;
	page_totl number;
begin
	page_ctn := 1;
	ctr := 0;
	
	
spool c:\Filename_&page_ctn1_of_&page_tot1..xlsx

				 
for rec in (select * from table)
loop		
		dbms_output.put_line(rec.);
		ctr := ctr + 1;
		
		if ctr = 25 
		then
                     spool off;
			ctr := 0
			page_ctn := page_ctn + 1;
			
			spool c:\Filename_page_ctn1_of_page_tot1..xlsx
		end if;
		
end loop;
end;
/

spool off;

Re: Spooling every 25 rows in excel [message #568389 is a reply to message #568385] Thu, 11 October 2012 05:06 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
U cannot use spool command inside a PL/SQL block , its not a sql cmd
Re: Spooling every 25 rows in excel [message #568390 is a reply to message #568385] Thu, 11 October 2012 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Spool" is a sqlplus command NOT a PL/SQL statement.

Use UTL_FILE to write into a file and open a new one every 25 records.

Regards
Michel
Re: Spooling every 25 rows in excel [message #568391 is a reply to message #568389] Thu, 11 October 2012 05:08 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

if you have unix use split command it's easy way ...
Re: Spooling every 25 rows in excel [message #568394 is a reply to message #568391] Thu, 11 October 2012 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no "split" command on Windows.
Once again a useless and irrelevant answer.

Regards
Michel
Re: Spooling every 25 rows in excel [message #568395 is a reply to message #568394] Thu, 11 October 2012 05:15 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member


see my comment Quote:
you have unix
Re: Spooling every 25 rows in excel [message #568398 is a reply to message #568395] Thu, 11 October 2012 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59757
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I see your comment BUT as OP is on Windows, we don't care about your comment and answer.
As I already said to you, "why don't you post the recipe for tiramisu" it is as relevant as your answer and should be more useful for many.

Regards
Michel
Re: Spooling every 25 rows in excel [message #568399 is a reply to message #568395] Thu, 11 October 2012 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 19773
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is there MS Excel for Unix?
Re: Spooling every 25 rows in excel [message #568451 is a reply to message #568385] Thu, 11 October 2012 20:25 Go to previous message
bhibe_17
Messages: 24
Registered: July 2012
Junior Member
I see. So I need to use UTL_FILE instead. Thanks for all your response.
Previous Topic: Query to convert data in single column
Next Topic: Data value size exceeding 4000 characters
Goto Forum:
  


Current Time: Mon Nov 24 10:44:08 CST 2014

Total time taken to generate the page: 0.06836 seconds