invalid number error when inserting a row in oracle

Question

I need to insert a row to a table in oracle.

insert into policy_tab values ('4325','29-APR-98','29-APR-2007',32424,(select ref(a) from agent_tab a where a.nic='242424v'),claim_ntty(
claim_t('25-APR-2005','25-JUN-2005'),
claim_t('26-APR-2005','26-JUN-2005')
));

But when I executed it will show this error. "ORA-01722: invalid number ORA-06512: at "SYS.DBMS_SQL", line 1721"

create type policy_ty as object(
 pid char(5),
 sDate date,
 eDate date,
 premium number(10,2),
 agent ref agent_ty,
 claims claim_ntty
);

create table policy_tab of policy_ty(
 pid primary key,
 agent SCOPE IS agent_tab
)
nested table claims store as claim_nttab;

create type claim_t AS OBJECT(
 eDate date,
 amount number(10,2)
);
create type claim_ntty as table of claim_t;

create type agent_ty as object(
 nic char(10),
 name varchar(50),
 address varchar(50),
 contactNo contactNo_vaty
) NOT FINAL;

create table agent_tab of agent_ty(
 nic primary key
);

So how to resolve it?

XING XING · Accepted Answer · 2017-10-30T09:15:03

From the first look you need to use like below. However the information provided is not sufficient to resolve the issue. Related Objects defintion as well needed.

This has to be changed - '29-APR-98' to '29-APR-1998'

INSERT INTO policy_tab
        VALUES (
                  '4325',
                  '29-APR-1998',
                  '29-APR-2007',
                  32424,
                  (SELECT REF (a)
                     FROM agent_tab a
                    WHERE a.nic = '242424v'),
                  claim_ntty (claim_t ('25-APR-2005', '25-JUN-2005'),
                              claim_t ('26-APR-2005', '26-JUN-2005')));

Edit:

Second observation. You created the below object:

create type claim_t AS OBJECT(
eDate date,
amount number(10,2)
);

And using it like:

claim_ntty (claim_t ('25-APR-2005', '25-JUN-2005'),
                                  claim_t ('26-APR-2005', '26-JUN-2005')));

Second Argument should be number not date.

So your insert should be:

INSERT INTO policy_tab
        VALUES (
                  '4325',
                  '29-APR-1998',
                  '29-APR-2007',
                  32424,
                  (SELECT REF (a)
                     FROM agent_tab a
                    WHERE a.nic = '242424v'),
                  claim_ntty (claim_t ('25-APR-2005', 123), claim_t ('26-APR-2005', 456)));

invalid number error when inserting a row in oracle

1 Answers