VARCHAR2 column formatting in spool file

Question

I'm working on an assignment and the spool output is getting some weird formatting for VARCHAR2 function returns. The class uses Oracle SQL 11g.

I have this PL/SQL script:

SET echo on
SET wrap off
SET trimspool on
SET linesize 80
SET colsep '|'
SET serveroutput on

spool @assignment.txt

CREATE OR REPLACE FUNCTION dollar_fmt_sf(
    p_num NUMBER
)RETURN VARCHAR2 IS
    lv_amt_txt VARCHAR2(20);
BEGIN
    lv_amt_txt := to_char(p_num, '$99,999.99');
    RETURN lv_amt_txt;
END;
/

SELECT
    idbasket,
    dollar_fmt_sf(shipping),
    dollar_fmt_sf(total)
FROM
    bb_basket
WHERE
    idbasket = 3;

spool off

This produces an output like this in assignment.txt where the last two columns are on separate lines.

  IDBASKET
----------
DOLLAR_FMT_SF(SHIPPING)                                                         
--------------------------------------------------------------------------------
DOLLAR_FMT_SF(TOTAL)                                                            
--------------------------------------------------------------------------------
         3
                                                                           $5.00
                                                                          $32.40

I would like to have it look more like this with the headers on the same line:

  IDBASKET|  SHIPPING|     TOTAL
----------|----------|----------
         3|         5|      32.4

score 0 · Accepted Answer · answered Apr 04 '23 at 17:53

I don't have your table so I'll use Scott's emp sample table for that purpose.

Function is as is:

SQL> CREATE OR REPLACE FUNCTION dollar_fmt_sf(
  2      p_num NUMBER
  3  )RETURN VARCHAR2 IS
  4      lv_amt_txt VARCHAR2(20);
  5  BEGIN
  6      lv_amt_txt := to_char(p_num, '$99,999.99');
  7      RETURN lv_amt_txt;
  8  END;
  9  /

Function created.

Sample table:

SQL> select * from bb_basket;

  IDBASKET   SHIPPING      TOTAL
---------- ---------- ----------
      7499        300       1760
      7521        500       1375
      7654       1400       1375
      7698                  3135
      7844          0       1650
      7900                  1045

6 rows selected.

Query - as you said - doesn't look OK:

SQL> SELECT
  2      idbasket,
  3      dollar_fmt_sf(shipping),
  4      dollar_fmt_sf(total)
  5  FROM bb_basket;

  IDBASKET
----------
DOLLAR_FMT_SF(SHIPPING)
--------------------------------------------------------------------------------
DOLLAR_FMT_SF(TOTAL)
--------------------------------------------------------------------------------
      7499
    $300.00
  $1,760.00

      7521
    $500.00
  $1,375.00
<snip>

What to do? Set alias to columns returned by function calls and format them:

SQL> col shipping format a15
SQL> col total format a15
SQL>
SQL> SELECT
  2      idbasket,
  3      dollar_fmt_sf(shipping) as shipping,
  4      dollar_fmt_sf(total) as total
  5  FROM bb_basket;

  IDBASKET SHIPPING        TOTAL
---------- --------------- ---------------
      7499     $300.00       $1,760.00
      7521     $500.00       $1,375.00
      7654   $1,400.00       $1,375.00
      7698                   $3,135.00
      7844        $.00       $1,650.00
      7900                   $1,045.00

6 rows selected.

SQL>

Looks better, doesn't it? Include that into your script (where all those SETs are).

One more thing: maybe you'd want to consider modifying format model to $99,990.00; result might look *prettier*. — Littlefoot, Apr 05 '23 at 05:08

VARCHAR2 column formatting in spool file

1 Answers1