1

I have the following code

create table Department2 (
  2  deptID number (15) primary key,
  3  deptName char (45),
  4  deptExt department1,
  5  DivisionID references division1); 

insert into department2 values (311, 'IT', (69193929489, 27222844821, 17897532567), 211);

I get the following error

ERROR at line 1: ORA-00907: missing right parenthesis

How to solve the problem?

APC
  • 144,005
  • 19
  • 170
  • 281
SlimmyBear
  • 15
  • 3
  • 1
    Check the following, it might provide a solution: https://stackoverflow.com/a/24587240/5143122 – heilala Jul 30 '20 at 07:21
  • Thanks i corrected it now its working – SlimmyBear Jul 30 '20 at 08:05
  • 1
    @AlexPoole - it's not necessary to include a datatype for a column which is a foreign key. See my answer. – APC Jul 30 '20 at 08:22
  • 1
    @APC - well that's my (basic!?) lesson for today, thanks. I assume that always goes to PK in that case; and if you ref a different UK then you have to specify. But I'll do some reading. (I also hadn't realised the error was from the insert, d'oh...) – Alex Poole Jul 30 '20 at 08:29
  • @AlexPoole - I prefer verbose declarations over minimal but I like the rigour of inheriting the datatype. – APC Jul 30 '20 at 08:37
  • @apecPoole the thing was i forgot to add the array datatype that i created lmfao – SlimmyBear Jul 30 '20 at 08:57

1 Answers1

3

Here is a solution based on a couple of assumptions:

  • department1 is a SQL nested table type
  • division1 is a table with a primary key

So we can create the table like this:

create table Department2 (
deptID number (15) primary key,
deptName char (45),
deptExt department1,
 DivisionID references division1)
       NESTED TABLE deptExt STORE AS deptExt_tab
 ;

Note that it not necessary to further define DivisionID: it inherits its datatype from the primary key column of the referenced table. The NESTED TABLE clause is required to create a nested table column, and its absence from the question is troubling.

Having created the table like this the posted insert statement does indeed hurl ORA-00907: missing right parenthesis. This is due to the collection of department IDs: we are instantiating a type and that means we need to reference the collection in the VALUES clause:

insert into department2 
values (311, 'IT', department1(69193929489, 27222844821, 17897532567), 211);

I have posted a working demo on db<>fiddle here

APC
  • 144,005
  • 19
  • 170
  • 281