error- column not allowed here & too many values upon insert

Question

I have this new problem after creating the Subscriber table of Subscriber_T type and its function i cant seem to insert any value in Subscrber table. The error, i keep getting is column not allowed here. But where???

These are the scripts and an insert record.

CREATE OR REPLACE TYPE Surnames_T AS OBJECT (
  Surname varchar (20)
);
/

CREATE OR REPLACE TYPE listSurnames_T 
AS Varray(4) of Surnames_T;
/


CREATE OR REPLACE TYPE listTels as object(
  Tel   number (12)
);
/

CREATE OR REPLACE TYPE listTels_T as Varray(5) of listTels;
/


CREATE OR REPLACE TYPE ADDRESS_T AS OBJECT (
  NUM       number (6),
  STREET    varchar (20),
  TOWN      varchar (20)
);
/

CREATE or replace type TAddress 
as table of Address_T;
/

create or replace type Subscriber_T as object(
  num_s  number(6),
  sName varchar(30), 
  surname listSurnames_T,
  Adds TAddress,
  DateOfBirth  date,
  phoneNo   listTels_T,
  member function Age return number
);
/

create table Subscribers of Subscriber_T(
  CONSTRAINT subscriber_pk primary key (num_s)
)
nested table Adds store as Tab_Adds;
/
show errors


create or replace type body Subscriber_T as
member function Age return number is
calc_age number;
dob date;
diff  date;
begin
  select S.dateOfBirth into dob
  from Subscribers S
  where deref(S.num_s) = self

  diff := sysdate - dob  
  calc_age := to_Char(diff, 'YYYY')

  return cal_age;
end;
    end;
/
show errors


insert into Subscribers values (34, Chloe, listSurnames_T(Surnames_T('Dave'), Surnames_T('Camille'), Surnames_T('Jones')), 
TAddress(Address_T(10, 'ave Foch', 'Ravenwood'), Address_T(30, 'rue des pole', 'England')), 
'10-11-1976', 
listTels_T(listTels(5839550456), listTels(6834734567)));

I'm guessing the function is raising this problem but it shows no error on compilation.

You don't need the `;` ***and*** the `/`: http://stackoverflow.com/a/10207695/330315 — , Dec 21 '13 at 10:42

score 2 · Accepted Answer · edited May 23 '17 at 10:32

Some issues:

You don't need to do a SELECT in the member function age.
You haven't quoted the name Chloe so its not a string.
You haven't converted the '10-11-1976' to a date.
You should not use VARCHAR as a datatype - use VARCHAR2 instead.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE TYPE Surnames_T AS OBJECT (
  Surname varchar2 (20)
)
/

CREATE OR REPLACE TYPE listSurnames_T 
AS Varray(4) of Surnames_T
/


CREATE OR REPLACE TYPE listTels as object(
  Tel   number (12)
)
/

CREATE OR REPLACE TYPE listTels_T as Varray(5) of listTels
/


CREATE OR REPLACE TYPE ADDRESS_T AS OBJECT (
  NUM       number (6),
  STREET    varchar2 (20),
  TOWN      varchar2 (20)
)
/

CREATE or replace type TAddress as table of Address_T
/

create or replace type Subscriber_T as object(
  num_s        number(6),
  sName        varchar2(30), 
  surname      listSurnames_T,
  Adds         TAddress,
  DateOfBirth  date,
  phoneNo       listTels_T,
  member function Age return number
)
/


create or replace type body Subscriber_T as
  member function Age return number is
  begin
    return FLOOR( MONTHS_BETWEEN( SYSDATE, self.DateOfBirth )/12 );
  end;
end;
/

create table Subscribers of Subscriber_T(
  CONSTRAINT subscriber_pk primary key (num_s)
)
nested table Adds store as Tab_Adds
/

insert into Subscribers values (
  34,
  'Chloe',
  listSurnames_T(
    Surnames_T('Dave'),
    Surnames_T('Camille'),
    Surnames_T('Jones')
  ), 
  TAddress(
    Address_T(10, 'ave Foch', 'Ravenwood'),
    Address_T(30, 'rue des pole', 'England')
  ),
  TO_DATE( '10-11-1976', 'DD-MM-YYYY' ), 
  listTels_T(
    listTels(5839550456),
    listTels(6834734567)
  )
)
/

Query 1:

SELECT s.age() FROM subscribers s

Results:

| S.AGE() |
|---------|
|      37 |

Edit

I'm not sure why you are wrapping a VARCHAR2 in the Surnames_T type or a NUMBER in the ListTels type. You can omit these and just do:

CREATE OR REPLACE TYPE listSurnames_T 
AS Varray(4) of VARCHAR2(20)
/

CREATE OR REPLACE TYPE listTels_T as Varray(5) of NUMBER(12)
/

and:

insert into Subscribers values (
  34,
  'Chloe',
  listSurnames_T(
    'Dave',
    'Camille',
    'Jones'
  ), 
  TAddress(
    Address_T(10, 'ave Foch', 'Ravenwood'),
    Address_T(30, 'rue des pole', 'England')
  ),
  TO_DATE( '10-11-1976', 'DD-MM-YYYY' ), 
  listTels_T(
    5839550456,
    6834734567
  )
)
/

NP, if it has solved your problem then please accept the answer and/or upvote (same goes for your earlier questions). If it doesn't solve the problem then post what the further issues are. — MT0, Dec 21 '13 at 17:19
Hi MTO. If for example, i want to make an update on Subscriber to change Chloe's 1st Address and telephone how can i do this? Plz advice. Tnx — Ralph Blackk, Dec 21 '13 at 18:00
i'm sorry i wasn't allowed to vote up bcs i'm new here & so i don't hv enough reputation. I don't know how things work here. I beg ur pardon i'm still working on my rep.^^ — Ralph Blackk, Dec 21 '13 at 18:03
[SQLFIDDLE](http://sqlfiddle.com/#!4/b1f14/10) - shows `INSERT`, `UPDATE` and `DELETE` for entries in a nested table (your addresses). I don't use `VARRAY`s so don't know of any syntax for adding/updating/removing elements from them using SQL and from a quick search could only find the syntax to replace an entry in its entirety - see the final example in that SQLFIDDLE. — MT0, Dec 22 '13 at 00:43
Hi MTO. I have a problem getting around this issue. I want to find a way to insert a new record into Subscribers while fetching & assigning an address from a Subscriber who's in the DB already like Chloe. This is my code:- INSERT INTO Subscribers values (950, 'Chloe', listSurnames_T('Vinchy'), Address_T(SELECT a.num, a.street, a.town FROM Subscribers s, table (s.adds) WHERE s.num_s = 100 )) ) / Error says i'm missing an expression. Plz, what's it? & Ur SQLfiddle is of great help. Tnx for d link. — Ralph Blackk, Dec 22 '13 at 06:05

error- column not allowed here & too many values upon insert

1 Answers1