CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldevelopersqlplus
Jeff O.
Jeff O.

Reputation: 25

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

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?

Upvotes: 0

Views: 207

Answers (1)

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions