Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01722 "invalid number" error

705640Jul 8 2009 — edited Jul 8 2009
When I try to insert an new record in my table Polizza, I get ORA-01722 "invalid number" error, but I wonder why.
I've read that ORA-01722 means:

The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.
Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions.
Only numeric fields may be added to or subtracted from dates.

And I checked everywhere but I'm not able to find out the reason, for I don't insert a number anywhere or convert a string into a number.
Moreover, SQL Developer point out the error at the beginning of line 1 of the command, not pointing out a precise location.
I'm using 10g XE. I'll post here my code.

/* type */
create or replace TYPE t_polizza AS OBJECT(
cod_polizza NUMBER(8),
data_stipulazione DATE,
data_scadenza DATE,
tipo_scadenza CHAR(1),
tipologia REF t_tipologia,
cliente REF t_cliente,
mezzo REF t_mezzo,
clausole t_listaClausole,
MEMBER FUNCTION costoTotale RETURN NUMBER
)
/

/* table */
CREATE TABLE polizza OF t_polizza(
cod_polizza NOT NULL,
data_stipulazione NOT NULL,
data_scadenza NOT NULL,
tipo_scadenza NOT NULL,
tipologia SCOPE IS tipo_polizza,
cliente SCOPE IS cliente,
mezzo SCOPE IS mezzo,
PRIMARY KEY (cod_polizza),
CONSTRAINT date_valide CHECK (data_stipulazione < data_scadenza),
CONSTRAINT tipo_scad CHECK (tipo_scadenza = 'a' OR tipo_scadenza = 's')
) NESTED TABLE clausole STORE AS clausole_tab
RETURN AS VALUE
/

/* insert */
INSERT INTO polizza VALUES(
seqPolizza.NEXTVAL,
'30-MAG-2008',
'30-MAG-2009',
'a',
(SELECT REF(t) FROM tipo_polizza t WHERE t.cod_tipo='Furto'), /* t.cod_tipo is VARCHAR */
(SELECT proprietario FROM mezzo m WHERE m.num_targa='DE298MA'), /* m.num_targa is VARCHAR */
(SELECT REF(m) FROM mezzo m WHERE m.num_targa='DE298MA'),
t_listaClausole(((SELECT REF(c) FROM clausola c WHERE c.nome_clausola='Furto'))) /* c.nome_clausola is VARCHAR */
);
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 5 2009
Added on Jul 8 2009
9 comments
1,421 views