Skip to Main Content
  • Questions
  • ORA-01722 - INSERT A VARCHAR2 IN A VARCHAR2 COLUMN

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, FLAVIA NORIKO FUKUDA.

Asked: May 14, 2020 - 4:23 pm UTC

Answered by: Connor McDonald - Last updated: May 19, 2020 - 11:06 am UTC

Category: Database Development - Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

Viewed 100+ times

You Asked

Hi Tom!

I have this table:
COLUMN_NAME     DATA_TYPE        DATA_LENGTH
COER_DT          DATE            7
COER_SESSION     VARCHAR2         30
COER_COPA_SQ     NUMBER         22
COER_LOGIN     VARCHAR2         60


When I try to INSERT:

INSERT INTO tmp_conta_erro (coer_dt, coer_session,   coer_copa_sq, coer_login) VALUES (TRUNC (SYSDATE),  'A',  7693605,  'TEST');


I receive this error: ora-17222 invalid number.

If I try this way:
INSERT INTO tmp_conta_erro (coer_dt, coer_session,   coer_copa_sq, coer_login) VALUES (TRUNC (SYSDATE),  '1',  7693605,  'TEST');


the insert is successfully done.
The only thing I´ve changed is the value in the coer_session. And I don´t know why I cant insert a char, since it´s a VARCHAR2 column.

I queried the values in the table, and they are all "numbers", without any chars.
And if I query like:
select * from tmp_conta_erro where coer_session = '1'

, with/without quotes, I receive no errors.

Thanks in advance.



and we said...

Check to see if

a) you have a trigger on the table
b) you have a function based index on the table

Either of these could have code within them that is trying to convert your value to a number in some way.

If that is not the cause, we need to see the DDL for the table and full top to bottom example of the issue so we can replicate here.

and you rated our response

  (2 ratings)

Reviews

May 15, 2020 - 4:16 pm UTC

Reviewer: A reader

I looked for mviews, triggers, function based indexes, virtual columns, constraints... and nothing. Its just a basic table, with a few rows.
Actually, this table works like a temporary table (but is not a temporary). We store some data just to process a report and delete after.
So, it increases no more than 3 rows.


  CREATE TABLE "SIFH"."TMP_CONTA_ERRO" 
   ( "COER_DT" DATE NOT NULL ENABLE, 
 "COER_SESSION" VARCHAR2(30) NOT NULL ENABLE, 
 "COER_COPA_SQ" NUMBER NOT NULL ENABLE, 
 "COER_LOGIN" VARCHAR2(60) NOT NULL ENABLE, 
 "COER_ST_PENDENCIA_IMPORTADA" VARCHAR2(1), 
 "COER_TP_ADMISSAO" VARCHAR2(2), 
  CONSTRAINT "COER_PK" PRIMARY KEY ("COER_DT", "COER_SESSION", "COER_COPA_SQ")
  USING INDEX PCTFREE 10 INITRANS 5 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SI3_FATI"  ENABLE, 
  CONSTRAINT "COER_COPA_FK" FOREIGN KEY ("COER_COPA_SQ")
   REFERENCES "SIFH"."CPA_CONTA_PACIENTE" ("COPA_SQ") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 5 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SI3_FATDA" 


Do you think perhaps its a problem with the data dictionary?
Connor McDonald

Followup  

May 18, 2020 - 2:56 am UTC

Sorry I can't reproduce

SQL> CREATE TABLE "TMP_CONTA_ERRO"
  2     ( "COER_DT" DATE NOT NULL ENABLE,
  3   "COER_SESSION" VARCHAR2(30) NOT NULL ENABLE,
  4   "COER_COPA_SQ" NUMBER NOT NULL ENABLE,
  5   "COER_LOGIN" VARCHAR2(60) NOT NULL ENABLE,
  6   "COER_ST_PENDENCIA_IMPORTADA" VARCHAR2(1),
  7   "COER_TP_ADMISSAO" VARCHAR2(2),
  8    CONSTRAINT "COER_PK" PRIMARY KEY ("COER_DT", "COER_SESSION", "COER_COPA_SQ")
  9    USING INDEX PCTFREE 10 INITRANS 5 MAXTRANS 255 COMPUTE STATISTICS
 10    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 11    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 12    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 13    TABLESPACE "USERS"  ENABLE
 14    --,
 15    --CONSTRAINT "COER_COPA_FK" FOREIGN KEY ("COER_COPA_SQ")
 16    -- REFERENCES "SIFH"."CPA_CONTA_PACIENTE" ("COPA_SQ") ENABLE
 17     ) SEGMENT CREATION IMMEDIATE
 18    PCTFREE 10 PCTUSED 40 INITRANS 5 MAXTRANS 255
 19   NOCOMPRESS LOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 23    TABLESPACE "USERS"
 24  /

Table created.

SQL>
SQL>
SQL> INSERT INTO tmp_conta_erro (coer_dt, coer_session,   coer_copa_sq, coer_login) VALUES (TRUNC (SYSDATE),  '1',  7693605,  'TEST');

1 row created.

SQL> INSERT INTO tmp_conta_erro (coer_dt, coer_session,   coer_copa_sq, coer_login) VALUES (TRUNC (SYSDATE),  'A',  7693605,  'TEST');

1 row created.



Next step would be look at dropping-recreating it and seeing if the issues continues. Keep us posted.

Resolved

May 18, 2020 - 8:29 pm UTC

Reviewer: A reader

Thanks.
I´ve recriated the object and everything is working now.


Connor McDonald

Followup  

May 19, 2020 - 11:06 am UTC

glad it worked out

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.