Home » SQL & PL/SQL » SQL & PL/SQL » What does Set Pages do?
What does Set Pages do? [message #227609] Wed, 28 March 2007 15:23 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I am not able to understand the behaviour of set pages.
I have a simple query that uses the following parameters.

col sys_name new_value sname noprint

set lines 300
set pages 999
set termout off
set heading off
set feedback off
set echo off
set trimspool on
set verify off

ttitle col 1 '- Computer name - \\'sname skip 1 -
col 1 'UserName FullName AccountType'
break on sname skip page

spool c:\x.txt

select username||chr(9)||full_name||chr(9)||account_type, lower(system_name) sys_name
from accesreview
order by lower(system_name);
spool off
--------
My output varies according to the pages value. If i put 200, then I get the output list of computername say for eg
- Computer name - \\alertsapp10
.
.
.
- Computer name - \\alertsmd38app1
.
.
- Computer name - \\alertsmd38uapp1

and so on. But moment i increase the page size to 999, then some computername are not displayed at all and some gets skipped.

Not sure what the problem could be?
Re: What does Set Pages do? [message #227615 is a reply to message #227609] Wed, 28 March 2007 15:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Controls your pagesize on screen
You can see the first output by default takes 2 pages (default pagesize is 14) and later with an increased pagesize, in the one page.
SQL>ttitle center 'list of datafiles' right 'Page:' format 999 sql.pno
SQL> btitle center 'end of report'
SQL> select name from v$datafile;

                                list of datafiles                      Page:   1
NAME
--------------------------------------------------------------------------------
/sand/sand/system01.dbf
/sand/sand/undotbs01.dbf
/sand/sand/sysaux01.dbf
/sand/sand/users01.dbf
/sand/sand/data01.dbf
/sand/sand/indx01.dbf
/sand/sand/gen.dbf
/sand/sand/tf70.dbf
/sand/sand/logan.dbf
                                  end of report

                                list of datafiles                      Page:   2
NAME
--------------------------------------------------------------------------------
/sand/sand/gen02.dbf
/sand/sand/data02.dbf
/sand/sand/indx02.dbf






                                  end of report

12 rows selected.

SQL> set pagesize 25
SQL> /

                                list of datafiles                      Page:   1
NAME
--------------------------------------------------------------------------------
/sand/sand/system01.dbf
/sand/sand/undotbs01.dbf
/sand/sand/sysaux01.dbf
/sand/sand/users01.dbf
/sand/sand/data01.dbf
/sand/sand/indx01.dbf
/sand/sand/gen.dbf
/sand/sand/tf70.dbf
/sand/sand/logan.dbf
/sand/sand/gen02.dbf
/sand/sand/data02.dbf
/sand/sand/indx02.dbf








                                  end of report

12 rows selected.

Re: What does Set Pages do? [message #227617 is a reply to message #227615] Wed, 28 March 2007 15:44 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I dont think that is the problem I am facing. I understand the set pages sets the page size. what I dont understand is why it should affect the output.
I have a table of 45000 rows and there are approx 350-800 values for each computername. So I BREAK on each computername with pagesize 900.
But I see ttitle only for some computernames and rest are completely missing.
If I set pages 50, then I get the computernames that were missing but I will have multiple ttitles for the same computername.

T
Re: What does Set Pages do? [message #227811 is a reply to message #227617] Thu, 29 March 2007 08:15 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
break command by default will not display repeating values. You have to add the keyword DUP.
Search for help on the BREAK command.
Previous Topic: Delete all the tables within a schema
Next Topic: Left Outer Join in Oracle
Goto Forum:
  


Current Time: Wed Dec 07 12:29:12 CST 2016

Total time taken to generate the page: 0.10392 seconds