This discussion is archived
10 Replies Latest reply: Oct 4, 2013 1:11 AM by tony.g RSS

Pagination in SQL*Plus

tony.g Journeyer
Currently Being Moderated

Hi

 

I am trying to manage the pages sizes in my sql*plus report and having an issue, so I have created an emp and dept version to show my question.

 

I have a query, where I am breaking on a field, then want to skip to the next page using the break command.  I was expecting sqlplus to either use formfeed for the skip to next page (if I have set newpage 0) or to put CR/LF in for the appropriate number of lines depending on the size of the pagesize I have set (if I have newpage set to non 0).  I am finding the formfeed works OK, but I do not want to use this, I want to use the CR/LF  option (that I think exists!!).

 

My example code is this:

 

clear columns
clear breaks

set lines 80
set pages 100
set newpage none


column deptno format 00
break on deptno skip page

ttitle -
left   'Left ' -
center 'TEST TEST ' -
right  'Page ' format 999 sql.pno skip2


select deptno, empno
from emp
order by deptno, empno


spool $HOME/test.log
/
spool off

 

 

The issue I have is the the output looks like this:

 

Left                               TEST TEST                           Page    1

DEPTNO      EMPNO
------ ----------
    10       7782
             7839
             7934
Left                               TEST TEST                           Page    2

DEPTNO      EMPNO
------ ----------
    20       7369
             7566
             7788
             7876
             7902
Left                               TEST TEST                           Page    3

DEPTNO      EMPNO
------ ----------
    30       7499
             7521
             7654
             7698
             7844
             7900

14 rows selected.

 

 

As I have pagesize set to 100, I would have expected that there should be 100 lines between each Left in the ttile, but you can see that this is not true.

 

Can anyone please tell me why this isn't the case?

 

Many thanks

Tony

  • 1. Re: Pagination in SQL*Plus
    EdStevens Guru
    Currently Being Moderated

    tony.g wrote:

     

    Hi

     

    I am trying to manage the pages sizes in my sql*plus report and having an issue, so I have created an emp and dept version to show my question.

     

    I have a query, where I am breaking on a field, then want to skip to the next page using the break command.  I was expecting sqlplus to either use formfeed for the skip to next page (if I have set newpage 0) or to put CR/LF in for the appropriate number of lines depending on the size of the pagesize I have set (if I have newpage set to non 0).  I am finding the formfeed works OK, but I do not want to use this, I want to use the CR/LF  option (that I think exists!!).

     

    My example code is this:

     

    clear columns
    clear breaks

    set lines 80
    set pages 100
    set newpage none


    column deptno format 00
    break on deptno skip page

    ttitle -
    left   'Left ' -
    center 'TEST TEST ' -
    right  'Page ' format 999 sql.pno skip2


    select deptno, empno
    from emp
    order by deptno, empno


    spool $HOME/test.log
    /
    spool off

     

     

    The issue I have is the the output looks like this:

     

    Left                               TEST TEST                           Page    1

    DEPTNO      EMPNO
    ------ ----------
        10       7782
                 7839
                 7934
    Left                               TEST TEST                           Page    2

    DEPTNO      EMPNO
    ------ ----------
        20       7369
                 7566
                 7788
                 7876
                 7902
    Left                               TEST TEST                           Page    3

    DEPTNO      EMPNO
    ------ ----------
        30       7499
                 7521
                 7654
                 7698
                 7844
                 7900

    14 rows selected.

     

     

    As I have pagesize set to 100, I would have expected that there should be 100 lines between each Left in the ttile, but you can see that this is not true.

     

    Can anyone please tell me why this isn't the case?

     

    Many thanks

    Tony

    No, it does not mean to insert 'n' blank lines.

    PAGESIZE n tells sqlplus to insert a hard page break (using the host os convention for such) after 'n' lines of output since the last page break.  Your own page breaks (result of your BREAK directive) each reset the count back to zero.

     

    Why would you want to insert 100 blank lines in any report?

  • 2. Re: Pagination in SQL*Plus
    tony.g Journeyer
    Currently Being Moderated

    Hi

     

    You can imagine a situation where you don't want to use a form feed to have the data for the next page start at the top of the page.

     

    I agree 100 is a bit much, but you can get 60 lines on a portrait page of A4 or Letter, this was just an example.

     

    I have other sql*plus reports where I can alter the page size up or down by one and it changes how many rows of data and blank lines it tries to fit onto a page.

     

    Thanks for your help.

    Tony

  • 3. Re: Pagination in SQL*Plus
    Priyasagi Pro
    Currently Being Moderated

    Hi Tony,

     

    Change your skip command as,

     

    break on deptno skip 2 on page skip 2

     

    then your problem will be solved.

  • 4. Re: Pagination in SQL*Plus
    tony.g Journeyer
    Currently Being Moderated

    >> break on deptno skip 2 on page skip 2

     

    sorry, that doesn't seem to help at all for me.

     

    I want to skip to the start of a new page when the deptno changes, and that doesn't do it.

     

    rgds
    Tony

  • 5. Re: Pagination in SQL*Plus
    Priyasagi Pro
    Currently Being Moderated

    Try this,

     

    set newpage 0

    break on deptno skip page

     

    Hope this will help.

  • 6. Re: Pagination in SQL*Plus
    tony.g Journeyer
    Currently Being Moderated

    Hi

     

     

     

  • 7. Re: Pagination in SQL*Plus
    EdStevens Guru
    Currently Being Moderated

    tony.g wrote:

     

    Hi

     

    You can imagine a situation where you don't want to use a form feed to have the data for the next page start at the top of the page.

     

    I agree 100 is a bit much, but you can get 60 lines on a portrait page of A4 or Letter, this was just an example.

     

    I have other sql*plus reports where I can alter the page size up or down by one and it changes how many rows of data and blank lines it tries to fit onto a page.

     

    Thanks for your help.

    Tony

     

    You can imagine a situation where you don't want to use a form feed to have the data for the next page start at the top of the page.

     

    Actually, no I cannot imagine such a situation. If I want "what follows" to be on the next page .. well, that's what formfeed was invented for.

    Trying to force the next page by insertion of line feeds is dependent on the size of the paper being printed on (letter? Legal? A4?  A post card? A page of Avery labels?), the size of the font being used, and how far down the page you've already come ...

  • 8. Re: Pagination in SQL*Plus
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    It looks like PAGESIZE is a maximum, not a fixed, number of lines, so no padding is added at the end of a query.

     

    If you add a bottom title, then SQL*Plus will pad every page, including the last page of a query, up to that point.  When you specify the TTITLE, give a BTITLE as well:

     

    BTITLE  LEFT ' '
  • 9. Re: Pagination in SQL*Plus
    Priyasagi Pro
    Currently Being Moderated

    Yes Frank,

     

    Its working fine.  Nice solution.

  • 10. Re: Pagination in SQL*Plus
    tony.g Journeyer
    Currently Being Moderated

    Hi Frank,

     

    Thanks for much for your very helpful suggestion.

     

    regards

    Tony


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points