What's wrong with this type?

Question

Trying to get a type to compile - here's the simplest form of it that still doesn't work:

CREATE TYPE SampleSchema.function_type AS OBJECT
(MEMBER FUNCTION member_func (param) RETURN number);
/

CREATE TYPE BODY SampleSchema.function_type AS
MEMBER FUNCTION member_func (param) RETURN number AS BEGIN RETURN 5; END;
END;
/

I've been through the documentation several times and this looks like valid syntax - so why is it still refusing to compile?

Answer 1

There are a couple of issues here;

The parameter param requires a datatype
It looks to be that an object requires at least one data attribute, otherwise the compile gives error PLS-00589

Code that compiles without errors/warnings;

CREATE TYPE test.function_type AS OBJECT
( dummy number,
  MEMBER FUNCTION member_func (param number ) RETURN number );
/

CREATE TYPE BODY test.function_type AS
MEMBER FUNCTION member_func (param number ) RETURN number AS BEGIN RETURN 5; END;
END;
/

The steps to getting here using sqlplus are listed below;

`Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

sqlplus test/test@ORA10GR2

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 3 14:35:41 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> CREATE TYPE test.function_type AS OBJECT
  2  (MEMBER FUNCTION member_func (param) RETURN number);
  3  /

Warning: Type created with compilation errors.

SQL> show errors
Errors for TYPE TEST.FUNCTION_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/36     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         in out <an identifier> <a double-quoted delimited-identifier>
         ... LONG_ double ref char time timestamp interval date binary
         national character nchar
         The symbol "<an identifier>" was substituted for ")" to continue.

SQL> CREATE TYPE test.function_type AS OBJECT
  2  (MEMBER FUNCTION member_func (param number ) RETURN number);
  3  /

Warning: Type created with compilation errors.

SQL> show errors
Errors for TYPE TEST.FUNCTION_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLS-00589: no attributes found in object type "FUNCTION_TYPE"
SQL>
SQL>
SQL> CREATE TYPE test.function_type AS OBJECT
  2  ( dummy number,
  3   MEMBER FUNCTION member_func (param number ) RETURN number
  4   );
  5  /

Type created.

SQL>
SQL> CREATE TYPE BODY test.function_type AS
  2  MEMBER FUNCTION member_func (param) RETURN number AS BEGIN RETURN 5; END;
  3  END;
  4  /

Warning: Type Body created with compilation errors.

SQL> show errors
Errors for TYPE BODY TEST.FUNCTION_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/35     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         in out <an identifier> <a double-quoted delimited-identifier>
         ... LONG_ double ref char time timestamp interval date binary
         national character nchar
         The symbol "<an identifier>" was substituted for ")" to continue.

SQL> CREATE TYPE BODY test.function_type AS
  2  MEMBER FUNCTION member_func (param number ) RETURN number AS BEGIN RETURN 5; END;
  3  END;
  4  /
CREATE TYPE BODY test.function_type AS
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> CREATE or replace TYPE BODY test.function_type AS
  2  MEMBER FUNCTION member_func (param number ) RETURN number AS BEGIN RETURN 5; END;
  3  END;
  4  /

Type body created.

What's wrong with this type?

Follow this question

Related Questions