The main difference in these two methods is the approach used in the individual line setup. Method 1 (Listing 22.13) uses the SQL command RPAD (see line 6) in combination with LINESIZE (line 2) to create an output line. The RPAD is a SQL function that is used to fill the line with blanks to position 80, and with LINESIZE set at 80 will cause the formatted line to appear on a line by itself. Method 2 (listing 22.14) uses the column command with the option NEWLINE specified in conjunction with a field alias name (see lines 6 and 22). The column command with the NEWLINE option will cause the formatted line to appear on a line by itself.

---

Note:  
Listing 22.13 uses lines 28 through 31 to skip to the top of a new page, where Listing 22.14 uses a break command to skip to a new page after each row of data from the database. The entire select command of each example formats one row of information from the database.

---

Listing 22.13. Method 1: fixed position formatting SQL*Plus report code.

1:    define  TICKET_ROWID = &1

2:    set LINESIZE 80

3:    set  HEADINGS OFF

4:    set FEEDBACK OFF

5:    spool TICKET

6:    select RPAD(‘----------------------------------------------  -’ ||

---------------

7:          null,80),

8:    RPAD(‘                       Customer Contact Survey’ || null,80),

9:    RPAD(‘-------------------------------------------------------------

--------------’ || null,80),

10:   RPAD(‘ Customer Name:  ‘ || CUSTOMER_NAME || ‘ PHONE#: ‘ || PHONE ||

null,80),

11:   RPAD(‘ Customer Address:  ‘ || CUSTOMER_ADDRESS  || null,80),

12:   RPAD(‘                                      ‘ || CUSTOMER_CITY ||

CUSTOMER_STATE ||

13:         CUSTOMER_ZIP  || null,80),

14:   RPAD(‘--------------------------------------------------------------

-

--------------’ || null,80),

15:   RPAD(‘ ‘ || TO_CHAR(CONTACT_DATE,’mm/dd/yy HH:MI || ‘  Caller: ‘ ||

CALLER ||

16:         null,80),

17:   RPAD(‘--------------------------------------------------------------

-

--------------’ || null,80),

18:   RPAD(‘  Home Phone? ‘ ||  HPHONE_YN  || ‘Best Time to call:  ‘ ||

CALL_TIME ||

19:   null,80),

20:   RPAD(‘   ‘Has Catalog? ‘ || CATALOG_YN || ‘Desire Future Calls? ‘ ||

FUTURE_YN ||

21:   null,80),

22:   RPAD(‘--------------------------------------------------------------

---------------’ || null,80),

23:   RPAD(‘PRINTED:  ‘ || TO_CHAR(SYSDATE,’mm/dd/yy HH:MI || ‘BY:  ‘ ||

24:

      OPERATOR || null,80)

25:   from CUSTOMER_TABLE

26:   where ROWID = ‘&&TICKET_ROWID’

27:   /

28:   set PAGESIZE 1

29:   set  NEWPAGE 0

30:   select  null from dual;

31:   set PAGESIZE 0

32:   spool OUT

33:   exit

Listing 22.14. Method 2: fixed position formatting SQL*Plus report code.

1:    define TICKET_ROWID = &1

2:    set PAGESIZE 55

3:    set LINESIZE 80

4:    set HEADINGS OFF

5:    set FEEDBACK OFF

6:    column LINE1 JUSTIFY LEFT NEWLINE

7:    column LINE2 JUSTIFY LEFT NEWLINE

8:    column LINE3 JUSTIFY LEFT NEWLINE

9:    column LINE4 JUSTIFY LEFT NEWLINE

10:   column LINE5 JUSTIFY LEFT NEWLINE

11:   column LINE6 JUSTIFY LEFT NEWLINE

12:   column LINE7 JUSTIFY LEFT NEWLINE

13:   column LINE8 JUSTIFY LEFT NEWLINE

14:   column LINE9 JUSTIFY LEFT NEWLINE

15:   column LINE10 JUSTIFY LEFT NEWLINE

16:   column LINE11 JUSTIFY LEFT NEWLINE

17:   column LINE12 JUSTIFY LEFT NEWLINE

18:   column LINE13 JUSTIFY LEFT NEWLINE

19:   column LINE14 JUSTIFY LEFT NEWLINE

20:   break ON ROW SKIP PAGE

21:   SPOOL TICKET

22:   select ‘------------------------------------------------------------

----------------’ || null LINE1,

23:   ‘                       Customer Contact Survey’ || null LINE2,

24:   ‘-------------------------------------------------------------------

---------’ || null LINE3,

25:   ‘ Customer Name:  ‘ || CUSTOMER_NAME || ‘ PHONE#: ‘ || PHONE || null

LINE4,

26:   ‘ Customer Address:  ‘ || CUSTOMER_ADDRESS  || null LINE5,

27:   ‘                                      ‘ || CUSTOMER_CITY ||

CUSTOMER STATE ||

28:             CUSTOMER_ZIP  || null LINE6,

29:   ‘-------------------------------------------------------------------

---------’ || null LINE7,

30:   ‘ ‘ || TO_CHAR(CONTACT_DATE,’mm/dd/yy HH:MI || ‘  Caller: ‘ ||

CALLER || null

31:         LINE8,

32:   ‘-------------------------------------------------------------------

---------’ || null LINE9,

33:   ‘  Home Phone? ‘ ||  HPHONE_YN  || ‘Best Time to call:  ‘ || CALL_

TIME || null

34:         LINE10,

35:   ‘   ‘Has Catalog? ‘ || CATALOG_YN || ‘Desire Future Calls? ‘ ||

FUTURE_YN || null

36:         LINE11,

37:   ‘-------------------------------------------------------------------

---------’ || null LINE12,

38:   ‘PRINTED:  ‘ || TO_CHAR(SYSDATE,’mm/dd/yy HH:MI || ‘BY:  ‘ ||

OPERATOR || null

39:         LINE13,

40:   ‘-------------------------------------------------------------------

---------’ || null LINE14

41:   from CUSTOMER_TABLE

42:   where ROWID = ‘&&TICKET_ROWID’

43:   /

44:   spool OUT

45:   exit

Listing 22.15. Output of Listing 22.13 and 22.14: fixed position formatting SQL*Plus report.

      ------------------------------------------------------------------

-

--------

            Customer Contact Survey

    ---------------------------------------------------------------------

-

--------

      Customer Name:  John Smith   PHONE#: 515 123-4567

      Customer Address:  123 Oak Street

                                     Anytown  VA 12345

      ------------------------------------------------------------------

-

--------

      31-Aug-95 10:05  Caller:   DHotka

      ------------------------------------------------------------------

-

--------

         Home Phone?    Y     Best Time to call:  8pm

         Has Catalog?   Y       Desire Future Calls?   N

      ------------------------------------------------------------------

-

--------

      PRINTED: 3-Apr-98 8:25   BY:  DHotka

      ------------------------------------------------------------------

-

--------