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

Last updated: May 19, 2020 - 11:06 am UTC

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

Viewed 1000+ 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 Connor 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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 15, 2020 - 4:16 pm UTC

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
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

A reader, May 18, 2020 - 8:29 pm UTC

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


Connor McDonald
May 19, 2020 - 11:06 am UTC

glad it worked out
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

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