CODEWITHSUNDEEP
oracle-databasetypesinsertoverloading
user1949713
user1949713

Reputation: 101

Oracle own type overloaded constructors in insert sql

I created an own type, with constructor overloading:

CREATE TYPE foo_type AS OBJECT(
       foo_type INTEGER,
       foo_number NUMBER(28, 28),
       foo_varchar2 VARCHAR2(4000 CHAR),

       CONSTRUCTOR FUNCTION foo_type (data NUMBER) RETURN SELF AS RESULT,
       CONSTRUCTOR FUNCTION foo_type (data VARCHAR2) RETURN SELF AS RESULT
)

CREATE TYPE BODY foo_type AS
  CONSTRUCTOR FUNCTION foo_type (data NUMBER) RETURN SELF AS RESULT
  IS
  BEGIN
       foo_type := 1;
       foo_number := data;
       return;
  END;

  CONSTRUCTOR FUNCTION foo (data VARCHAR2) RETURN SELF AS RESULT
  IS
  BEGIN
       foo_type := 2;
       foo_varchar2 := data;
       return;
  END;
END;

While I wasn't overload as long as it is works:

insert into test_table (field) values ( foo_type(1, 2.2, 'bar') )

But when I wrote the constructors it isn't works not at all:

insert into test_table (field) values ( foo_type(2.2) )

or

insert into test_table (field) values ( foo_type('bar') )

at this time oracle says: "ORA-06553: too many declarations 'foo_type' match this call" and thus:

insert into test_table (field) values ( foo_type(foo_varchar2 => 'bar') )

says: "ORA-009007: missing right parenthesis".

What should I change that it is works?

Thanks.

Upvotes: 0

Views: 1954

Answers (1)

Justin Cave
Justin Cave

Reputation: 231691

If I correct the syntax errors so that the declaration and the body of the object compile

  • You cannot have a member variable with the same name as the object type
  • The second constructor in your object body needs to be named foo_type not foo-- constructors always take the name of the object.

leaving me with something like

CREATE TYPE foo_type AS OBJECT(
       foo_int INTEGER,
       foo_number NUMBER(28, 28),
       foo_varchar2 VARCHAR2(4000 CHAR),
       CONSTRUCTOR FUNCTION foo_type (data NUMBER) RETURN SELF AS RESULT,
       CONSTRUCTOR FUNCTION foo_type (data VARCHAR2) RETURN SELF AS RESULT
);
/

 CREATE OR REPLACE TYPE BODY foo_type AS
   CONSTRUCTOR FUNCTION foo_type (data NUMBER) RETURN SELF AS RESULT
   IS
   BEGIN
        foo_int := 1;
        foo_number := data;
        return;
   END;
   CONSTRUCTOR FUNCTION foo_type (data VARCHAR2) RETURN SELF AS RESULT
   IS
   BEGIN
        foo_int := 2;
        foo_varchar2 := data;
        return;
   END;
 END;

and if I change to pass in a number that is valid for a NUMBER(28,28) column (which 2.2 is not), the overloading works just fine

SQL> ed
Wrote file afiedt.buf

  1   declare
  2     l_foo foo_type;
  3   begin
  4     l_foo := foo_type( 0.2 );
  5     dbms_output.put_line( l_foo.foo_int );
  6     l_foo := foo_type( 'bar' );
  7     dbms_output.put_line( l_foo.foo_int );
  8*  end;
SQL> /
1
2

PL/SQL procedure successfully completed.

or inserting into a table

SQL> create table test_table( foo_column foo_type );

Table created.

SQL> insert into test_table values( foo_type( 0.2 ) );

1 row created.

SQL> insert into test_table values( foo_type( 'bar' ) );

1 row created.

It seems highly unlikely to me that you would really want foo_number to be a NUMBER(28, 28) particularly when you try to assign a value of 2.2 in your examples. If your actual object type were declared like that, your default constructor would have failed.

Upvotes: 4

Related Questions