10 Replies Latest reply: Oct 4, 2013 3:11 AM by tony.g RSS

    Pagination in SQL*Plus

    tony.g

      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

          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

            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

              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

                >> 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

                  Try this,

                   

                  set newpage 0

                  break on deptno skip page

                   

                  Hope this will help.

                  • 6. Re: Pagination in SQL*Plus
                    tony.g

                    Hi

                     

                     

                     

                    • 7. Re: Pagination in SQL*Plus
                      EdStevens

                      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

                        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

                          Yes Frank,

                           

                          Its working fine.  Nice solution.

                          • 10. Re: Pagination in SQL*Plus
                            tony.g

                            Hi Frank,

                             

                            Thanks for much for your very helpful suggestion.

                             

                            regards

                            Tony