Previous Table of Contents Next


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


Previous Table of Contents Next