Creating a simple TOC
======================================================================================
1. Create table in SQL*Plus

SQL> create table toc_example (topic varchar2(100), page number);


2. SQL Query for Group Above Report

SELECT ALL COUNTRIES.COUNTRY_NAME,
       CUSTOMERS.CUST_LAST_NAME,
       CUSTOMERS.CUST_FIRST_NAME,
       CUSTOMERS.CUST_EMAIL
FROM CUSTOMERS, COUNTRIES
WHERE (CUSTOMERS.COUNTRY_ID = COUNTRIES.COUNTRY_ID)

3. After Parameter Form Trigger

function AfterPForm return boolean is
begin
srw.set_format_order(srw.main_section, srw.header_section, srw.trailer_section);  
  return (TRUE);
end;

4. Format Trigger on F_COUNTRY_NAME

function F_COUNTRY_NAMEFormatTrigger return boolean is
pageNum number;
begin
-- get current page number
srw.get_page_num(pageNum);
-- insert row into table
insert into toc_example
values (:country_name, pageNum);  
  return (TRUE);
end;

5. Second Query

SELECT TOPIC, MIN(PAGE) FROMPAGE,
       MAX(PAGE) TOPAGE
FROM TOC_EXAMPLE
GROUP BY TOPIC

6. TOC_pages Formula Column

function CF_1Formula return Char is
begin
  if :fromPage = :toPage then
  return (:fromPage);
  else
  	return (:fromPage ||'-'|| :toPage);
  end if;
end;

=====================================================================================

Creating an index for the report

1. Create a table using SQL*Plus

SQL> create table index_example (term varchar2(100), page number);

2. F_CUST_LAST_NAME Format Trigger

function F_CUST_LAST_NAMEFormatTrigger return boolean is
  PageNum number;
begin
  -- get pagenumber
  srw.get_page_num(pageNum);
  -- insert into table
  insert into index_example
  values (:Cust_last_name||', '||:Cust_first_name, PageNum);
  return (TRUE);
end;

3. SQL Query for Index

SELECT SUBSTR(TERM,1,1) INITIAL_LETTER, TERM, PAGE FROM INDEX_EXAMPLE ORDER BY TERM



