Datatype of Function RETURN value

I have a stored function defined as follows:

FUNCTION get_compcode (maincomp_in)
RETURN VARCHAR2 AS
CURSOR comptype_cur IS
  SELECT compcode
  FROM comptype
  WHERE comptype.comptype = maincomp_in; comptype_rec comptype_cur%ROWTYPE;
BEGIN
  OPEN comptype_cur;
  FETCH comptype_cur INTO comptype_rec;   CLOSE comptype_cur;
  IF comptype_rec.compcode IS NULL THEN     RETURN NULL
  ELSE
    RETURN comptype_rec.compcode;
  END IF;
END; I use this function in the SELECT clause of a CREATE VIEW statement, as follows:

CREATE VIEW my_view AS
SELECT get_compcode(maincomp1) as compcode1, get_compcode(maincomp2) as compcode2,
[additional columns]
FROM my_table;

The compcode column in the comptype table has a datatype of VARCHAR2(4). The problem is that the datatype of the compcode1 and compcode2 columns in my_view is VARCHAR2(2000). Why is it not VARCHAR2(4), and how do I correct it?

I cannot use a simple join instead of the function in the CREATE VIEW statement because I apply the get_compcode function to multiple fields.

Thanks in advance for your help.

Todd Owers
toddo_at_gcr1.com Received on Sun May 09 1999 - 15:59:26 CDT