sql.pno keeps restarting at 1
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sql.pno keeps restarting at 1

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67

    Smile

    I'm writing a script which creates many small separate reports within the same SQL.

    Each time I set up a new title.

    The sql.pno restarts every time at 1, which is NOT what I want.

    Any way to alter this behavior? Or can I use a variable within my script (like variable page_no number). If yes, how can I use within TTITLE and then increment it?

    Thanks,

    Paul

    One more quicky. Can SQL and PL/SQL be mixed within the same script?

    For example

    variable page_no number
    begin
    age_no := age_no + 1;
    end;
    print page_no

    I couldn't get this to work.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mrpaulwass
    One more quicky. Can SQL and PL/SQL be mixed within the same script?

    For example

    variable page_no number
    begin
    :Page_no := :Page_no + 1;
    end;
    print page_no

    I couldn't get this to work.
    When you defined your host variable :PAGE_NO it has value of NULL. So you are adding 1 to a NULL value, which allways results in NULL. The following works:
    Code:
    SQL> variable page_no number 
    SQL> 
    SQL> begin
      2     :Page_no := NVL(:Page_no,0) + 1;
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> print page_no
    
       PAGE_NO
    ----------
             1
    You are right, you can't force SQL.PNO not to be reset after each query. So you are on the right track by trying to use host variable inside a TTITLE. But you have to use some tricks. Continuing from the above code snippet, here is an example how to maintain the current page number in TTITLE with many different selects. On the left side of the title you have what SQL.PNO returns (allways 1), on the right side you have the current page title:
    Code:
    SQL> column current_page new_value pg noprint
    SQL> TTITLE left 'SQL.PNO:' format 99 sql.pno center 'Current page:' format 99 pg
    SQL> 
    SQL> ttitle off
    SQL> select :Page_no as current_page from dual;
    
    SQL> ttitle on
    SQL> select 'My first page...' from dual;
    
    SQL.PNO:  1                     Current page:  1
    'MYFIRSTPAGE...'
    ----------------
    My first page...
    
    SQL> begin
      2     :Page_no := NVL(:Page_no,0) + 1;
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> ttitle off
    SQL> select :Page_no as current_page from dual;
    
    SQL> ttitle on
    SQL> select 'My second page...' from dual;
    
    SQL.PNO:  1                     Current page:  2
    'MYSECONDPAGE...'
    -----------------
    My second page...
    
    SQL> begin
      2     :Page_no := NVL(:Page_no,0) + 1;
      3  end;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> ttitle off
    SQL> select :Page_no as current_page from dual;
    
    SQL> ttitle on
    SQL> select 'My third page...' from dual;
    
    SQL.PNO:  1                     Current page:  3
    'MYTHIRDPAGE...'
    ----------------
    My third page...
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67
    Thank you for your help.

    Here's a followup question.

    Is there a way to tell if a report has run or not?
    I only want to increment the counter (as shown in the example above) if the report runs. If the SQL does not return any rows, the report does not print, and thus I do not want to increment the counter.

    Thanks again.

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width