sql.pno keeps restarting at 1

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
:page_no := :page_no + 1;
end;
print page_no

I couldn't get this to work.

Quote:

---

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>

---

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