How to insert a row of record in SQL Developer with invalid character?

Question

I found that there is a record inserted in a table of our client's production database. I cannot select one field of that record but I can select other fields by SELECT SQL statement.

A_TABLE
-------------------
|COL_A|COL_B|COL_C|
-------------------
|.....|.....|.....|
-------------------
|.....|.....|.....|
-------------------
|.....|.....|.....|
-------------------

"Invalid character encountered in" is shown as the query result by running this SQL statement:

select * from A_TABLE where COL_A = 2;

Query result: Invalid character encountered in

However, this statement could be run successfully:

select COL_A, COL_C from A_TABLE where COL_A = 2;

Query result:
-------------
|COL_A|COL_C|
-------------
|  2  |.....|
-------------

There should be invalid character encountering in the COL_B field of A_TABLE that the COL_A value is 2. Do you have idea why the COL_B contains invalid character? I would like to try to create a row of record which is similar with the row which COL_A value is 2 in A_TABLE. What should I do by INSERT or other method?

Actually, I have other questions before I ask this question...

More detailed version: However, the information about the table of this question has been simplified. You may see the almost original information is mentioned as below but I have renamed the names of the table and fields for confidential reason for protecting our client.

A_TABLE (almost original version)
---------------------------------------
|COL_A|COL_B|.............|COL_R|COL_S|
---------------------------------------
|.....|.....|.............|.....|.....|
---------------------------------------
|.....|.....|.............|.....|.....|
---------------------------------------
|.....|.....|.............|.....|.....|
---------------------------------------

Here is the CREATE SQL for the A_TABLE table (but I copied and amended from the SQL in the UAT database. It should be the same as the production but I am not sure):

  CREATE TABLE "DBA"."A_TABLE" 
   (    "COL_A" NUMBER(10,0) NOT NULL ENABLE, 
    "COL_B" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
    "COL_C" VARCHAR2(3 BYTE), 
    "COL_D" NUMBER(4,0) NOT NULL ENABLE, 
    "COL_E" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "COL_F" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "COL_G" VARCHAR2(122 BYTE), 
    "COL_H" VARCHAR2(160 BYTE), 
    "COL_I" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "COL_J" NUMBER(6,0) NOT NULL ENABLE, 
    "COL_K" VARCHAR2(16 BYTE) NOT NULL ENABLE, 
    "COL_L" VARCHAR2(50 BYTE), 
    "COL_M" DATE, 
    "COL_N" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "COL_O" "SYS"."XMLTYPE"  NOT NULL ENABLE,
    "COL_P" DATE NOT NULL ENABLE, 
    "COL_Q" DATE NOT NULL ENABLE, 
    "COL_R" VARCHAR2(16 BYTE) NOT NULL ENABLE, 
    "COL_S" VARCHAR2(3 BYTE), 
     CONSTRAINT "A_TABLE_PK" PRIMARY KEY ("COL_K")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_X"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_S" 
 XMLTYPE COLUMN "COL_O" STORE AS BASICFILE CLOB (
  TABLESPACE "TABLESPACE_S" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

  CREATE INDEX "DBA"."A_TABLE_IDX1" ON "DBA"."A_TABLE" ("COL_A", "COL_B") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_X" ;

  CREATE INDEX "DBA"."A_TABLE_IDX2" ON "DBA"."A_TABLE" ("COL_P") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_X" ;

  CREATE INDEX "DBA"."A_TABLE_IDX3" ON "DBA"."A_TABLE" ("COL_B", "COL_D") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_X" ;

  CREATE UNIQUE INDEX "DBA"."A_TABLE_IDX4" ON "DBA"."A_TABLE" ("COL_B", "COL_D", "COL_E") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_X" ;

  CREATE INDEX "DBA"."A_TABLE_IDX5" ON "DBA"."A_TABLE" ("COL_Q", "COL_E") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TABLESPACE_X" ;

Therefore, some information of my question should be changed here for aligning to the information in almost original version:

"Invalid character encountered in" is shown as the query result by running this SQL statement:

select * from A_TABLE where COL_B = 00000002;

Query result: Invalid character encountered in

However, this statement could be run successfully:

--this statement selects all fields of the table except COL_O
select COL_A, COL_B, ..., COL_R, COL_S from A_TABLE where COL_A = 00000002;

Query result (without COL_O field):
------------------------------------------
|COL_A| COL_B  |.............|COL_R|COL_S|
------------------------------------------
|.....|00000002|.............|.....|.....|
------------------------------------------

I should rephrase my question here (in the more detailed part): There should be invalid character encountering in the COL_O field of A_TABLE that the COL_B value is 00000002. Do you have idea why the COL_O contains invalid character? I would like to try to create a row of record which is similar with the row which COL_B value is 00000002 in A_TABLE. What should I do by INSERT or other method?

Can you include your original create table statement for the `A_TABLE` ? — Tim Biegeleisen, Jun 22 '20 at 03:30
@TimBiegeleisen I have just edited my question for providing more information. I'm sorry that the presentation is probably a bit cumbersome. — ABCman, Jun 22 '20 at 04:34

score 0 · Answer 1 · answered Jun 22 '20 at 05:36

As col_o is xmltype, it seems that it contains something invalid. Might be something like a promile sign, Greek alphabet letter, who knows what - some character that isn't supported by your database's character set.

Unfortunately, it looks like you didn't post entire error message you got. Full error stack - something like this:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00216: invalid character 0 (0x13)                  --> this
Error at line 10682

would help as you could fix that. What would that 0x13 be? Google knows:

Decimal Octal Hex   Binary    Character 
------- ----- ----  --------  ----------------------------- 
    019   023 0x13  00010011  DC3  (XOFF)(Device Control 3)

OK, now that you know what to look for, let's see an example of how to fix it.

My XML doesn't contain anything "invalid", but CR (Carriage Return, CHR(13)) "represents" it.

SQL> create table a_table (id number, col_o xmltype);

Table created.

SQL> insert into a_table (id, col_o) values
  2  (1, '<?xml version="1.0" encoding="UTF-8"?>
  3          <note>
  4            <body>Do ' || chr(13) || 'not do that!</body>      --> here it is
  5          </note>');

1 row created.

SQL> select * from a_table;

        ID COL_O
---------- --------------------------------------------------
         1 <?xml version="1.0" encoding="WINDOWS-1250"?>
           <note>
             <body>Do                             --> causes line break
           not do that!</body>
           </note>

In order to fix it, run an update:

SQL> update a_table set
  2    col_o = replace(replace(col_o, chr(13), ''), chr(10), '')
  3  where id = 1;

1 row updated.

SQL> select * from a_table;

        ID COL_O
---------- --------------------------------------------------
         1 <?xml version="1.0" encoding="WINDOWS-1250"?>
           <note>
             <body>Do not do that!</body>             --> line isn't broken any more
           </note>


SQL>

So: if you find what's wrong with it, you might be able to fix it. Good luck!

Thanks for your reply! Unfortunately, I can only see "Invalid character encountered in" as the Query Result in Oracle SQL Developer by running the SELECT statement mentioned in my question. It was run in the production database. I have not noticed that if there any messages in the log part of SQL Developer. It is a bit inconvenient to check again at this moment... but I could see some information from the back-end server log that showed the Exception while running that SELECT statement through online service. — ABCman, Jun 22 '20 at 08:40
The log has been posted in this question: https://stackoverflow.com/questions/62446963/caused-by-java-sql-sqlexception-invalid-character-encountered-in — ABCman, Jun 22 '20 at 08:40
Actually, every row of records of A_TABLE is inserted through online service. Users need to upload an XML file and inputting some information while using the online service. Validations have been applied to limit they to upload a valid XML file and input acceptable characters/words. Probably, special characters in the uploaded XML file cannot be checked by the current validation rules. — ABCman, Jun 22 '20 at 09:13
For simulating the error existed in the production, I tried to uploaded an XML file that contains some characters which are probably invalid in UAT environment. The value of COL_O is from the uploaded XML file and some inputted data. However, I could see a new inserted record and the value of COL_O after submitting online. I still cannot generate an error record to the UAT database successfully. Even though I have tried to add a CHR(13) into the value of COL_O by UPDATE and commit, the value of COL_O can still be shown after updating. — ABCman, Jun 22 '20 at 09:13
Probably, there are difference between the Production and the UAT Databases... such as database's supported character set... I don't know how I can check about the supported character set. — ABCman, Jun 22 '20 at 09:13

How to insert a row of record in SQL Developer with invalid character?

1 Answers1