Nested tables - ORA-00902: invalid datatype

Question

I have a problem when I want to identify data type for my variable "price" as object type "rsd". When I create the table it retuns SQL Exception: ORA-00902: invalid datatype

CREATE OR REPLACE TYPE  "RSD" AS OBJECT (
currency number(9,2),
MEMBER FUNCTION get_currency RETURN number)
INSTANTIABLE NOT FINAL;

CREATE OR REPLACE TYPE BODY "RSD" AS
MEMBER FUNCTION get_currency RETURN number IS
    BEGIN
        RETURN SELF.currency;
    END;
END;

CREATE TABLE bill_item(
bill_ID number,
rb number,
price rsd,
item_id number,
CONSTRAINT scpk PRIMARY KEY (bill_id));

Can you please help me?

Answer 1

I suspect you executed it as a script, and you must have got a compilation error for the TYPE BODY. You have not closed its scope.

Don't execute all the steps together as a script. Execute them one at a time. If you do it step-by-step, it will succeed.

Or, put a forward slash at end of each block and then execute as a script.

SQL> CREATE OR REPLACE TYPE  "RSD" AS OBJECT (
  2  currency number(9,2),
  3  MEMBER FUNCTION get_currency RETURN number)
  4  INSTANTIABLE NOT FINAL
  5  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY "RSD" AS
  2  MEMBER FUNCTION get_currency RETURN number IS
  3      BEGIN
  4          RETURN SELF.currency;
  5      END;
  6  END;
  7  /

Type body created.

SQL>
SQL> CREATE TABLE bill_item(
  2  bill_ID number,
  3  rb number,
  4  price rsd,
  5  item_id NUMBER,
  6  CONSTRAINT scpk PRIMARY KEY (bill_id))
  7  /

Table created.    

SQL> desc bill_item;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------
 BILL_ID                                   NOT NULL NUMBER
 RB                                                 NUMBER
 PRICE                                              RSD
 ITEM_ID                                            NUMBER

SQL>
SQL> select * from bill_item;

no rows selected