Coping with solumn width of user-defined functions in selects

From: Ole C Meldahl <meldahl_at_pvv.org>
Date: Thu, 20 May 1999 11:37:24 +0200
Message-ID: <3743D7D4.CD8F7308@pvv.org>

Has anybody got a good strategy for coping with the fact that user-defined functions in selects returns a width of 2000?

Example:
SQL> CREATE OR REPLACE VIEW TEST
  2 AS
  3 select mypackage.myfunc( 'Value') col1,   4 substr( mypackage.myfunc( 'Value'), 1, 10) col2   5 from dual
  6 /

View created.

SQL> desc test

 Name                            Null?    Type
 ------------------------------- -------- ----
 COL1                                     VARCHAR2(2000)
 COL2                                     VARCHAR2(10)

I need the colum width to be more realistic, and the strategy of COL2 is the best I've found. Unfortunatly it is cumbersome, having to add the substr all the time. I can live with this, but the magical number 10 annoys me. Tried to reference a package constant/variable, but no luck. Not even a parameterless functio helped. Am I plain stupid or isn't it possible?

Who do you solve this problem?

ole c Received on Thu May 20 1999 - 04:37:24 CDT