CODEWITHSUNDEEP
sqloracle-database
nop
nop

Reputation: 6323

Oracle Object Relational Database inserting records into a table with reference to a derived table

I have the following structure below and I want to add a record into project table. The problem with the following INSERT is that on the place of customer_t I have to select a record from user table while inserting but I want to select only customers (and not staff members). Basically customer_t and staff_t inherit from user_t (table: user).

You can look at same insert but for service_t, it works fine because I'm selecting from a table, but in this case, there is no table for customer.

I'm not so experience with this ORM. Any idea how to solve it?

INSERT INTO project VALUES (
  1,
  'Project name',
  'Description',
  ( SELECT REF(u) AS customer_t
    FROM   "USER" u
    WHERE  idno = 1
    AND    VALUE(u) IS OF TYPE (customer_t)
  ),
  ( SELECT REF(s) FROM service s WHERE serviceno = 1)
);

Error message:

Error starting at line : 1 in command -
INSERT INTO project VALUES (1, 'Project name', 'Logo', (SELECT REF(u) AS customer_t FROM "USER" u WHERE idno = 1 AND VALUE(u) IS OF TYPE (customer_t)), (SELECT REF(s) FROM service s WHERE serviceno = 1))
Error at Command Line : 1 Column : 72
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected REF C##EX_TEST.CUSTOMER_T got REF C##EX_TEST.USER_T
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

SQL:

CREATE TYPE name_t AS OBJECT (
  first_name     VARCHAR2(32),
  last_name      VARCHAR2(32)
);

CREATE TYPE address_t AS OBJECT (
  province       VARCHAR2(32),
  street         VARCHAR2(32),
  city           VARCHAR2(32),
  postal_code    VARCHAR2(10)
);

CREATE TYPE user_t AS OBJECT (
  idno           NUMBER,
  email          VARCHAR2(40),
  password       VARCHAR2(32),
  name           name_t,
  address        address_t,
  phone          VARCHAR2(15),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER
) NOT FINAL;

CREATE TYPE rank_t AS OBJECT (
  rankno       NUMBER,
  name         VARCHAR2(40),
  description  VARCHAR2(60)
);

CREATE TABLE "RANK" OF rank_t (
  PRIMARY KEY (rankno),
  UNIQUE (name)
);

INSERT INTO "RANK" VALUES (1, 'User', 'Simple a user');
INSERT INTO "RANK" VALUES (2, 'Manager', 'Manager');
INSERT INTO "RANK" VALUES (3, 'Administrator', 'Admin');

CREATE TYPE customer_t UNDER user_t (

);

CREATE TYPE staff_t UNDER user_t (
  salary       NUMBER(7,2),
  rank         REF rank_t
);

CREATE TABLE "USER" OF user_t (
  PRIMARY KEY (idno),
  UNIQUE (email));

CREATE TYPE service_t AS OBJECT (
  serviceno    NUMBER,
  name         VARCHAR2(40),
  description  VARCHAR2(60)
);

CREATE TABLE service OF service_t (PRIMARY KEY (serviceno));

CREATE TYPE project_t AS OBJECT (
  projectno    NUMBER,
  name         VARCHAR2(40),
  description  VARCHAR2(60),
  customer     REF customer_t,
  service      REF service_t
);

CREATE TABLE project OF project_t (PRIMARY KEY (projectno));

Upvotes: 0

Views: 268

Answers (1)

MT0
MT0

Reputation: 167981

You don't need a separate table for a customers; just insert the types into the USER table.

If you want to put a customer in then insert a customer_t type:

INSERT INTO "USER" VALUES(
  customer_t(
    1,
    '[email protected]',
    'abcdefg',
    name_t( 'One', 'Uno' ),
    address_t( 'province','street', 'city', '12345' ),
    '0123456789'
  )
);

If you want to insert a user then use user_t or insert values without a type:

INSERT INTO "USER" VALUES(
    2,
    '[email protected]',
    'abcdefg',
    name_t( 'Two', 'Dos' ),
    address_t( 'province','street', 'city', '12345' ),
    '0123456789'
);

If you want to insert a staff member then use staff_t:

INSERT INTO "USER" VALUES(
  staff_t(
    3,
    '[email protected]',
    'abcdefg',
    name_t( 'Three', 'Tres' ),
    address_t( 'province','street', 'city', '12345' ),
    '0123456789',
    12345.67,
    (SELECT REF(r) FROM "RANK" r WHERE rankno = 2 )
  )
);

Then the data will have the correct types (which you can extract using SYS.ANYDATA) and use the TREAT function to convert a super-type to a sub-type:

SELECT idno,
       email,
       SYS.ANYDATA.getTypeName( SYS.ANYDATA.convertObject( VALUE(u) ) ) As type,
       TREAT(VALUE(u) AS staff_t).salary AS salary,
       TREAT(VALUE(u) AS staff_t).rank.name AS rank
FROM   "USER" u

outputs:

IDNO | EMAIL                        | TYPE                                   |   SALARY | RANK   
---: | :--------------------------- | :------------------------------------- | -------: | :------
   1 | [email protected] | FIDDLE_KPHUNVHPVRKOYQBWVAQS.CUSTOMER_T |     null | null   
   2 | [email protected]     | FIDDLE_KPHUNVHPVRKOYQBWVAQS.USER_T     |     null | null   
   3 | [email protected]    | FIDDLE_KPHUNVHPVRKOYQBWVAQS.STAFF_T    | 12345.67 | Manager

and:

SELECT idno,
       email
FROM   "USER" u
WHERE  VALUE(u) IS OF TYPE (customer_t)

outputs:

IDNO | EMAIL                       
---: | :---------------------------
   1 | [email protected]

and your insert needs to convert the REF from the user_t super-type to the customer_t sub-type using the TREAT function:

INSERT INTO project VALUES (
  1,
  'Project name',
  'Description',
  ( SELECT TREAT( REF(u) AS REF customer_t )
    FROM   "USER" u
    WHERE  idno = 1
    AND    VALUE(u) IS OF TYPE (customer_t)
  ),
  ( SELECT REF(s) FROM service s WHERE serviceno = 1)
);

db<>fiddle here

Upvotes: 1

Related Questions