Why does the output of my SQL query display incorrectly when using a custom function in the SELECT statement?

Question

So I'm learning how to define custom functions in PL/SQL. When I use any of the functions I've defined in a regular SELECT statement the script output gets a ton of dashes added and the readability suffers. I'm using the latest version of SQL developer.

What I want it to look like:

SELECT dtstage, idstage
FROM bb_basketstatus
WHERE idbasket = 4;

DTSTAGE      IDSTAGE
--------- ----------
13-FEB-12          1
13-FEB-12          5

What I get:

SELECT dtstage, status_desc_sf(idstage) Description
FROM bb_basketstatus
WHERE idbasket = 4;

DTSTAGE
---------
DESCRIPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13-FEB-12 
Order submitted                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

13-FEB-12 
Shipped

Is there a setting in SQL developer or something I'm missing the function definition?

Are you sure this is SQL Developer? This looks like SQL Plus. — GMB
– GMB, Commented Nov 7, 2020 at 0:58

GMB · Accepted Answer · 2020-11-07 00:59:28Z

1

That's just a SQLPlus / SQLDeveloper script display issue.

You can manually set the width of the column with the column ... format command:

column description format a50

select dtstage, status_desc_sf(idstage) description
from bb_basketstatus
where idbasket = 4;

It might also be useful to increase the default width of the lines (which defaults to 80), eg:

set linesize 120

answered Nov 7, 2020 at 0:59

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EdStevens Over a year ago

worth noting that, in itself, increasing the linesize will be no help for viewing the sqlplus output on a fixed-width command window. At that point the command line window itself will force a line-wrap. If I really need a longer line, I spool the output so that I can view it in a proper text editor. Of course, the first line of attack is, as you said, to adjust the column width.

Collectives™ on Stack Overflow

Why does the output of my SQL query display incorrectly when using a custom function in the SELECT statement?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related