| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Limiting size of function return value
A copy of this was sent to pat_800_at_my-dejanews.com
(if that email address didn't require changing)
On Tue, 06 Apr 1999 21:52:10 GMT, you wrote:
>Would anyone perhaps know how I might limit the size of a VARCHAR2 return
>value for a PL/SQL function called within a SQL statement? I have
>constrained the length of the internal function variable that is returned,
>yet any tool that I for querying (SQL*Plus, TOAD, SAS) always creates a field
>of the maximum allowable size for its text data type. This results in fields
>of 200 - 2000 bytes for a 20-byte-max value. The value is not padded to the
>right or left, so trimming is useless. Oracle just doesn't seem to know how
>large the value will be, so it allocates the max.
>
>Any thoughts on how I might limit this behavior would be greatly appreciated.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
The only way to do it is:
create view yourView
as
select substr( yourfunction(x), 1, 20 ) yourfunction, ....
from t;
that is -- select the substr of your function -- that will constrain the column type. It works from a select or from a create view statement.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |