Home » SQL & PL/SQL » SQL & PL/SQL » ora-01722 when using to_number (merged)
ora-01722 when using to_number (merged) [message #268740] Wed, 19 September 2007 09:42 Go to next message
sandudana
Messages: 48
Registered: March 2006
Member
Hello everybody,


Database version: Oracle 10gR2

I have this views:


CREATE OR REPLACE VIEW V_ESAMI
(MATRICOLA, SCD_DWT_ANA_STD, C_INS, AA_ESAME, C_SESSIONE,
DT_ESAME, VOTO, F_LODE, GIUDIZIO, AA_FREQ,
ANNO_CORSO, UNIV_SOST, C_CDL_STD, C_IND_STD, C_CDL_INS,
TIPO_ESAME, SOST_ESAME, PROVENIENZA, D_CUSTOM1, D_CUSTOM2,
CFU, ACORSO_RIF, COD_PST_RIF, N_FC_RIF, COD_ATENEO,
STATO_ESA, COD_ERR_ESA)
AS
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME","C_SESSIONE",
"DT_ESAME","VOTO","F_LODE","GIUDIZIO","AA_FREQ","ANNO_CORSO","UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2","CFU",
"ACORSO_RIF","COD_PST_RIF","N_FC_RIF","COD_ATENEO","STATO_ESA","COD_ERR_ESA"
from dwm.DWT_ESAMI
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",
to_date("DT_ESAME",'yyyymmdd'),to_number(voto),"F_LODE","GIUDIZIO",
"AA_FREQ",to_number("ANNO_CORSO"),"UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2",to_number("CFU"),
to_number("ACORSO_RIF"),"COD_PST_RIF",to_number("N_FC_RIF"),"COD_ATENEO",
"STATO_ESA","COD_ERR_ESA"
from dwh.DWT_ESAMI_SOST_RAW
union all
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME","C_SESSIONE","DT_ESAME","VOTO",NULL, "GIUDIZIO","AA_FREQ","ANNO_CORSO",NULL,
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL,
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwm.DWT_ESAMI_NEG
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",to_date("DT_ESAME",'yyyymmdd'),to_number(voto), NULL, "GIUDIZIO","AA_FREQ",to_number("ANNO_CORSO"),NULL,
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL,
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwh.DWT_ESAMI_NEG_SOST_RAW;


In tables DWT_ESAMI e DWT_ESAMI_NEG, columns VOTO e CFU are of type NUMBER.
In tables DWT_ESAMI_SOST_RAW e DWT_ESAMI_NEG_SOST_RAW, columns VOTO e CFU are of type VARCHAR2, this is why I used TO_NUMBER to convert them to NUMBER.

When I do an 'select * from v_esami' in SQL or TOAD everything is OK.
Why I try to create a view, or another table or a materialzed view from V_ESAMI:

create or replace view v_test as select * from v_esami;
create table t_test as select * from v_esami;
... I get ora-01722 error

If I do a
create or replace view v_test as select matricola from v_esami;
the view is created.

As sson as I add the filds CFU e VOTO, I get the ora-01722.
Why?

Any help will be very appreciated.
Thank you!

Daniela
Re: ora-01722 [message #268741 is a reply to message #268740] Wed, 19 September 2007 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
The unformatted post is too much of a challenge for me to read & decipher.
Please read & follow posting guidelines as stated in #1 STICKY post at top of forum including use of <code tags>.
Re: ora-01722 [message #268743 is a reply to message #268740] Wed, 19 September 2007 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

ORA-01722: invalid number

Fix it.

Regards
Michel
icon9.gif  ora-01722 when using to_number (formatted version) [message #268744 is a reply to message #268740] Wed, 19 September 2007 10:16 Go to previous messageGo to next message
sandudana
Messages: 48
Registered: March 2006
Member
Hello everybody,

Sorry for the previous unformatted post Sad . Here it is the formatted version.

Database version: Oracle 10gR2

I have this view:

CREATE OR REPLACE VIEW V_ESAMI
(MATRICOLA, SCD_DWT_ANA_STD, C_INS, AA_ESAME, C_SESSIONE, 
DT_ESAME, VOTO, F_LODE, GIUDIZIO, AA_FREQ, 
ANNO_CORSO, UNIV_SOST, C_CDL_STD, C_IND_STD, C_CDL_INS, 
TIPO_ESAME, SOST_ESAME, PROVENIENZA, D_CUSTOM1, D_CUSTOM2, 
CFU, ACORSO_RIF, COD_PST_RIF, N_FC_RIF, COD_ATENEO, 
STATO_ESA, COD_ERR_ESA)
AS 
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME","C_SESSIONE",
"DT_ESAME","VOTO","F_LODE","GIUDIZIO","AA_FREQ","ANNO_CORSO","UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2","CFU",
"ACORSO_RIF","COD_PST_RIF","N_FC_RIF","COD_ATENEO","STATO_ESA","COD_ERR_ESA" 
from dwm.DWT_ESAMI 
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",
to_date("DT_ESAME",'yyyymmdd'),to_number(voto),"F_LODE","GIUDIZIO",
"AA_FREQ",to_number("ANNO_CORSO"),"UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2",to_number("CFU"),
to_number("ACORSO_RIF"),"COD_PST_RIF",to_number("N_FC_RIF"),"COD_ATENEO",
"STATO_ESA","COD_ERR_ESA"
from dwh.DWT_ESAMI_SOST_RAW
union all
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME",
"C_SESSIONE","DT_ESAME","VOTO",NULL, "GIUDIZIO","AA_FREQ",
"ANNO_CORSO",NULL, 
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL, 
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwm.DWT_ESAMI_NEG
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",
to_date("DT_ESAME",'yyyymmdd'),to_number(voto), NULL, "GIUDIZIO","AA_FREQ",
to_number("ANNO_CORSO"),NULL, 
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL, 
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwh.DWT_ESAMI_NEG_SOST_RAW;



In tables DWT_ESAMI e DWT_ESAMI_NEG, columns VOTO e CFU are of type NUMBER.
In tables DWT_ESAMI_SOST_RAW e DWT_ESAMI_NEG_SOST_RAW, columns VOTO e CFU are of type VARCHAR2, this is why I used TO_NUMBER to convert them to NUMBER.

When I do a
select * from v_esami

in SQL or TOAD everything is OK.

Why I try to create a view, or another table or a materialized view from V_ESAMI:

create or replace view v_test as select * from v_esami;
create table t_test as select * from v_esami;

... I get ora-01722 error

If I do a

create or replace view v_test as select matricola from v_esami;

the view is created.

As soon as I add fields CFU e VOTO, I get the ora-01722.
Why?

Any help will be very appreciated.
Thank you!

Daniela

[Updated on: Wed, 19 September 2007 12:37] by Moderator

Report message to a moderator

Re: ora-01722 when using to_number (formatted version) [message #268745 is a reply to message #268744] Wed, 19 September 2007 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't call this formatted.

Regards
Michel
Re: ora-01722 when using to_number (formatted version) [message #268753 is a reply to message #268745] Wed, 19 September 2007 10:39 Go to previous messageGo to next message
sandudana
Messages: 48
Registered: March 2006
Member
Database version: Oracle 10.2.0.2.0

I have this view:
CREATE OR REPLACE VIEW V_ESAMI
(MATRICOLA, some_fields_1, VOTO,CFU, some_fields_2)
AS 
select  "MATRICOLA", some_fields_1, 
        "VOTO","CFU", 
        some_fields_2
from dwm.DWT_ESAMI 
union all
select  "MATRICOLA", some_fields_1, 
        to_number(voto) "VOTO",to_number("CFU") "CFU",    
        some_fields_2
from dwh.DWT_ESAMI_SOST_RAW
union all
select "MATRICOLA", some_fields_1,
       "VOTO", NULL "CFU",
       some_fields_2
from dwm.DWT_ESAMI_NEG
union all
select "MATRICOLA",some_fields_1,
       to_number(voto) "VOTO", NULL "CFU",
       some_fields_2
from dwh.DWT_ESAMI_NEG_SOST_RAW;



In tables DWT_ESAMI e DWT_ESAMI_NEG, columns VOTO e CFU are of type NUMBER.
In tables DWT_ESAMI_SOST_RAW e DWT_ESAMI_NEG_SOST_RAW, columns VOTO e CFU are of type VARCHAR2, this is why I used TO_NUMBER to convert them to NUMBER.

When I do a
select * from v_esami
in SQL or TOAD everything is OK.
Why I try to create a view, or another table or a materialized view from V_ESAMI:

create or replace view v_test as select * from v_esami;
create table t_test as select * from v_esami;
... I get ora-01722 error

I know that ora-01722 means invalid number, cause:
Quote:
The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal
. I know the error is because of the CFU and VOTO fields.

I just want to understand why is behaving like this, because (as stated above) when doing
select * from v_esami; or select cfu, voto from v_esami
I get no errors!

Thank you!

Daniela
Re: ora-01722 when using to_number (formatted version) [message #268922 is a reply to message #268753] Thu, 20 September 2007 02:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you do a SELECT * FROM .... in TOAD or SQL Developer, it doesn't get all the records - it just gets the first 100 or so rows.
Try doing
select * from v_esami order by 1 desc
this should fetch all the rows in order to do the sort.
I suspect that that will error.

You could also try something like this. Create a function to check if a value is a number:
CREATE OR REPLACE FUNCTION is_number(p_vc in varchar2) return varchar2 as
  v_num  number;
begin
  v_num := to_number(p_vc);
  return 'Y';
exception
  when others then
  return 'N';
end;
/


Then use this function to check the data in the tables DWT_ESAMI_SOST_RAW and DWT_ESAMI_NEG_SOST_RAW - one of these must have a value in it that errors when converted into a number.
SELECT VOTO 
      ,is_number(voto)
      ,CFU
      ,is_number(cfu)
FROM   DWT_ESAMI_SOST_RAW
WHERE  is_number(voto) = 'N' or is_number(cfy) = 'N'

SELECT VOTO 
      ,is_number(voto)
      ,CFU
      ,is_number(cfu)
FROM   DWT_ESAMI_NEG_SOST_RAW
WHERE  is_number(voto) = 'N' or is_number(cfy) = 'N'
Previous Topic: Ora-6502 on inserting a clob via pl/sql
Next Topic: ORA-06533 While returning collection from function
Goto Forum:
  


Current Time: Tue Dec 06 08:27:47 CST 2016

Total time taken to generate the page: 0.23017 seconds