CODEWITHSUNDEEP
oracle-databaseuser-defined-types
BartmanDilaw
BartmanDilaw

Reputation: 415

Oracle Type issue

I'm trying to create a table using Oracle TYPES that uses TYPES, but get this compilation warning while compiling type body of "TYPE_USRDTE".

Warning: Type body created with compilation errors

The compilation errors are :

Compilation errors for TYPE BODY STRACC.TYPE_USRDTE
#13#10Error: PLS-00539: subprogram 'TYPE_USRDTE' is declared in an object type body and must be defined in the object type specification
Line: 2
Text: CONSTRUCTOR FUNCTION TYPE_USRDTE( pUsr varchar2 default user, pDte date default sysdate ) RETURN SELF AS RESULT IS

Here is the total code :

DROP TABLE TAB_MAIN
/
DROP TYPE TYPE_SYSCOL
/
DROP TYPE TYPE_USRDTE
/
DROP SEQUENCE SEQ_GENERIQUE 
/
CREATE SEQUENCE SEQ_GENERIQUE NOCACHE
/

CREATE TYPE TYPE_USRDTE AS OBJECT(
USR VARCHAR2(30),
DTE DATE,
CONSTRUCTOR FUNCTION TYPE_USRDTE( pUsr varchar2 default user, pDte date default sysdate ) RETURN SELF AS RESULT
)
/

CREATE TYPE BODY TYPE_USRDTE IS
  CONSTRUCTOR FUNCTION TYPE_USRDTE( pUsr varchar2 default user, pDte date default sysdate ) RETURN SELF AS RESULT IS
    BEGIN
       SELF.USR := pUsr;
       SELF.DTE := pDte;
    END;
END;
/


CREATE TYPE TYPE_SYSCOL AS OBJECT(

IDT NUMBER ,
CRE TYPE_USRDTE,
MDF TYPE_USRDTE,

CONSTRUCTOR FUNCTION TYPE_SYSCOL(IDT number default -1) RETURN SELF AS RESULT
)
/

CREATE OR REPLACE TYPE BODY TYPE_SYSCOL IS
CONSTRUCTOR FUNCTION TYPE_SYSCOL (IDT number default -1) RETURN SELF AS RESULT IS
BEGIN
  SELF.IDT := SEQ_GENERIQUE.NEXTVAL;
  SELF.CRE := NEW TYPE_USRDTE(user, sysdate);
  SELF.MDF := NEW TYPE_USRDTE(user, sysdate);
RETURN;
end;
END;
/
CREATE TABLE TAB_MAIN  ( COLSYS TYPE_SYSCOL, COD VARCHAR2(30) )
/

INSERT INTO TAB_MAIN(COD, COLSYS) VALUES('TESTCOL1', TYPE_SYSCOL())
/

INSERT INTO TAB_MAIN(COD, COLSYS) VALUES('TESTCOL2', TYPE_SYSCOL())
/

INSERT INTO TAB_MAIN(COD, COLSYS) VALUES('TESTCOL3', TYPE_SYSCOL())
/

INSERT INTO TAB_MAIN(COD, COLSYS) VALUES('TESTCOL4', TYPE_SYSCOL())
/

INSERT INTO TAB_MAIN(COD, COLSYS) VALUES('TESTCOL5', TYPE_SYSCOL())
/

COMMIT
/

When I select from TAB_MAIN, I get rows I expected. Can anybody help me to understand and correct this issue ?

Upvotes: 0

Views: 123

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

As well as the missing RETURN, your bespoke constructor has the same arguments as the default constructor, which is leading to your "PLS-00307: too many declarations of 'TYPE_USRDTE' match this call" error. When you call:

SELF.CRE := NEW TYPE_USRDTE(user, sysdate);

the compiler can't tell which of the two very similar constructors you mean. You have to make it clear which you want; these will all call your bespoke constructor:

SELF.CRE := NEW TYPE_USRDTE(pUsr => user, pDte => sysdate);
SELF.CRE := NEW TYPE_USRDTE(pusr => user);
SELF.CRE := NEW TYPE_USRDTE(pDte => sysdate);
SELF.CRE := NEW TYPE_USRDTE(user);
SELF.CRE := NEW TYPE_USRDTE(sysdate);

The first one is the equivalent of your original call, but specifies the argument names, so it can be matched with the specific constructors. The second and third do the same but only pass one of the arguments, allowing the other to be defaulted. The third and fourth do the same but as only the bespoke constructor has the defaults, you don't need the argument name; it's clearer if you include it anyway though. And if you have more than one argument with the same data type or there is any ambiguity about how they are supplied then you need the names anyway.

To call the default constructor instead you would use the attribute names:

SELF.CRE := NEW TYPE_USRDTE(usr => user, dte => sysdate);

and both arguments are always needed.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

This may or may not be the actual issue but one obvious issue with your type body is: missing RETURN

CREATE TYPE BODY TYPE_USRDTE IS
  CONSTRUCTOR FUNCTION TYPE_USRDTE( pUsr varchar2 default user, pDte date default sysdate ) RETURN SELF AS RESULT IS
    BEGIN
       SELF.USR := pUsr;
       SELF.DTE := pDte;
       RETURN; -- this is missing in your code
    END;
END;
/

Cheers!!

Upvotes: 1

Related Questions