x

What's wrong with this type?

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?

more ▼

asked Oct 28, 2009 at 01:28 PM in Default

Kastaka gravatar image

Kastaka
219 8 9 11

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

There are a couple of issues here;

  1. The parameter param requires a datatype
  2. 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 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.
more ▼

answered Nov 03, 2009 at 11:59 AM

mathewbutler gravatar image

mathewbutler
116 1 1 2

The parameter was a copying error, but adding a dummy data attribute worked a treat - thanks!
Nov 03, 2009 at 12:48 PM Kastaka
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x50
x3
x1

asked: Oct 28, 2009 at 01:28 PM

Seen: 1776 times

Last Updated: Oct 28, 2009 at 01:28 PM