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