Thursday, June 26, 2008

ORA-02315: incorrect number of arguments for default constructor

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This is a rather trivial error that is raised when you forget to specify some attribute in the list of values that makes up the object constructor:
drop table test_tab cascade constraints;
drop type tab_column_type;
drop type column_type;

create or replace
type column_type as object (
col_name varchar2(30),
col_comment varchar2(4000)
);
/

create or replace type tab_column_type as table of column_type;
/

create table test_tab (
table_name varchar2(30),
table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

insert into test_tab
values ('TEST_TAB', tab_column_type(column_type('TABLE_NAME','name of table',0),
column_type('TABLE_COLUMNS','list of columns',0)));

ORA-02315: incorrect number of arguments for default constructor
Column_type object definition contains two attributes (COL_NAME and COL_COMMENT), however is specified three values inside the constructor (in red color the exceeding value).

It's enough to remove the exceeding values (or add the missing ones...) to make the statement work.

Addendum posted on june, 27.
As per Gary's comment, one can prevent ORA-02315 by means of a user-defined constructor (in green color) and this technique is available on all oracle version since 9iR2 (i didn't check on earlier releases):
drop table test_tab cascade constraints;
drop type tab_column_type;
drop type column_type;

create or replace
type column_type as object (
col_name varchar2(30),
col_comment varchar2(4000),
constructor function column_type(p_name VARCHAR2) return self as result);
/

create or replace type body column_type
is
constructor function column_type(p_name VARCHAR2) return self as result
is
begin
self.col_name := p_name;
self.col_comment := null;
return;
end;
end;
/

create or replace type tab_column_type as table of column_type;
/

create table test_tab (
table_name varchar2(30),
table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;


insert into test_tab
values ('TEST_TAB', tab_column_type(column_type('TABLE_NAME'),
column_type('TABLE_COLUMNS')));

This time no ORA-02315 will be returned because the constructor inserts nulls into the COL_COMMENT attribute.
Note also that you can overload these constructors in case you want to allow a user to selectively populate attributes.
You can read more about this in the linked document.


See message translations for ORA-02315 and search additional resources.

2 comments:

Gary Myers said...

Worth pointing out that where you have a type with lots of attributes, it can be worth creating a non-default constructor that uses defaults for some of the values.

I have a type with 19 attributes (for addresses) and in 11g I can do, for example,
select type_address(country => 'France', street_name => 'Rue d''Italie') addr from dual;
and all the other attributes default to null.

Byte64 said...

Thanks for pointing this out Gary, i added an example on the technique you mentioned.

Cheers,
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles