Re: function / varchar width... varchar2(4000)?

A copy of this was sent to "W. Scott Moore" <sirws_at_hotmail.com> (if that email address didn't require changing) On Tue, 28 Dec 1999 11:17:46 -0700, you wrote:

>Thanks for the help. Out of curiousity, why did it use 4000?
>

4000 = maximum possible length of a VARCHAR2 type in the database. The return values from the functions are of unknown length -- hence it maxes them out.

>thanks,
>scott
>
>"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>news:vuth6skqhpb2ek2e7gd01ms6pp00bqs2hv_at_4ax.com...
>> A copy of this was sent to "W. Scott Moore" <sirws_at_hotmail.com>
>> (if that email address didn't require changing)
>> On Tue, 28 Dec 1999 10:30:19 -0700, you wrote:
>>
>> >I have a function:
>> >
>> >function APN_NUM_KEY_F (bookmap IN NUMBER, width IN NUMBER)
>> > return varchar2
>> > is
>>
>> [snip]
>>
>> >
>> >and when I call it like:
>> >
>> >select apn_num_key_f(apn_map, 3) from lb_parcel_pnt
>> >
>> >it works fine. The same goes when I use it in a view like:
>> >
>> >CREATE OR REPLACE VIEW LB_PARCEL_POLY_V ( PPOLYID,
>> >APN_BOOK, APN_MAP, PARCEL_ID, APN
>> > ) AS select a.ppolyid, c.apn_book, c.apn_map, c.parcel_id,
>>
>>
>> substr( apn_num_key_f(c.apn_book, 3) || apn_num_key_f(c.apn_map, 2) ||
>> c.parcel_id, 1, 10 )
>>
>> >as APN from lb_parcel_poly a,
>> > lb_parcel_pnt_poly_join b,
>> > lb_parcel_pnt c
>> >where b.ppolyid = a.ppolyid and b.ppntid = c.ppntid
>> >
>> >however, when I am using toad to look at the data, it says it is a
>> >varchar2(4000), when I would like it to be a varchar2(10).
>> >
>> >Any ideas would be greatly appreciated.
>> >
>>
>> substr'ing it will work.
>>
>> >Thanks,
>> >Scott
>> >
>>
>>
>> --
>> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
>Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated June
>21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Dec 28 1999 - 13:16:57 CST