Cannot commit an entry to database which has "e" character while other characters are number

Question

I am trying to add below values to the addrlookup table while user_name field in the database has varchar2(64 byte) data type;

insert into addrlookup (user_name, rsrc_id, primary_addr)     
values('10e359269010','3040','132');

and getting the below error from oracle;

SQL Error: ORA-00911: invalid character

ORA-06512: at "MCSDBSCHEMA.ADDTOADDRUNIQUENESSCHECK", line 113

ORA-06512: at "MCSDBSCHEMA.ADDRLOOKUP_AIFER", line 15

ORA-04088: error during execution of trigger 'MCSDBSCHEMA.ADDRLOOKUP_AIFER' 00911. 00000 - "invalid character"

Cause: identifiers may not start with any ASCII character other letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.

I have tried to add below values with 'e' and taken the same errors.

6e359269010,20e59269010

I did not observe this issue with the below users;

2059269e010,456e054144,e2059269011

And the trigger is below;

create or replace trigger addrlookup_aifer
after insert or update on addrlookup  for each row
declare
   root_domain       number;
   current_domain    number;
begin
    IF MCSDB_UTL.RCVR_REPLICATED_CHG = true
THEN
    RETURN;
END IF;

 user_pkg.newRows( user_pkg.newRows.count+1 ) := :new.rowid;
 select distinct u.domain_id into current_domain from userinfo u
   where :new.rsrc_id = u.rsrc_id;
 select root_domain_id into root_domain from domaininfo
   where rsrc_id = current_domain;
 addToAddrUniquenessCheck(:new.user_name, root_domain, :new.primary_addr);
end;

The problem is at the procedure addToAddrUniquenessCheck -line 113 in the addrlookup_aifer trigger but I could not understand what the root cause is. p_UNI_VALUE_NUM,num_dups are declared as number,p_UNI_VALUE is declared as varchar2 in the addToAddrUniquenessCheck procedure. When num_dups >0 application raises error,when no data found num_dups=0.

I am adding the code part below;

BEGIN
     p_UNI_VALUE_NUM := to_number(p_UNI_VALUE);         
     execute immediate 
          'SELECT COUNT (*) 
           FROM ADDR_UNIQ_RANGE_CHECK aurc 
           WHERE aurc.range_id NOT IN 
             (SELECT base_system_feature_xla_id 
                   FROM DNRANGE_SYS_FEATURE_XLA) 
              AND ' || p_UNI_VALUE_NUM || ' >= aurc.from_value and '
               || p_UNI_VALUE_NUM || ' <= aurc.to_value' 
    INTO num_dups;

"RANGE_ID","FROM_VALUE" and "TO_VALUE" are declared as NUMBER in the addr_uniq_range_check table. It has two constraint as below.

CONSTRAINT "PK_ADDR_UNIQ_RANGE_CHECK" 
    PRIMARY KEY ("RANGE_ID")
       USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
       STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "INDX"  ENABLE
     CONSTRAINT "FK_ADDR_UNIQ_RANGE_CHECK" FOREIGN KEY ("RANGE_ID")
  REFERENCES "MCSDBSCHEMA"."BASE_SYSTEM_FEATURE_XLA" ("BASE_SYSTEM_FEATURE_XLA_ID") ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MCP_DATA" ;

The error message is saying that the exception raised in addToAddrUniquenessCheck, at line 113. Please post a relevant extract of code from this procedure to help people understand the problem. — Aleksej
You are right. When I deleted the procedure I can commit that query to database successfully. @Aleksej — kaydemir
I have added the relevant extract of code from this procedure. — kaydemir

APC APC · Accepted Answer · 2017-01-02T12:26:22

The problem with the procedure side addToAddrUniquenessCheck() is the way you have written the dynamic SQL. You are concatenating the parameters with the boilerplate text but you haven't included escaped quote marks, so Oracle treats them as numbers not strings (because they appear to be in scientific notation; if they didn't have that single e, or had other characters Oracle would probably treat them as columns and hurl ORA-00904 i.e. invalid identifier).

Test case:

SQL> create or replace procedure ins_t23
  2    ( p_col1 in t23.col1%type ) 
  3  is
  4  begin
  5    execute immediate
  6        'insert into t23 values (t23_seq.nextval, '
  7            || p_col1 ||')';
  8  end;
  9  /
Procedure created.

SQL> exec ins_t23('20e59269010')
BEGIN ins_t23('20e59269010'); END;

*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "APC.INS_T23", line 5
ORA-06512: at line 1


SQL>

The correct way to include variables in dynamic SQL is to use placeholders, like this:

SQL> create or replace procedure ins_t23
  2    ( p_col1 in t23.col1%type ) 
  3  is
  4  begin
  5    execute immediate
  6        'insert into t23 values (t23_seq.nextval,  :1)'
  7        using p_col1;
  8  end;
  9  /
Procedure created.

SQL> exec ins_t23('20e59269010')

PL/SQL procedure successfully completed.

SQL>

As an aside, the use of dynamic SQL is completely unnecessary in your case. This would work:

SELECT COUNT (*)  
    INTO num_dups
FROM ADDR_UNIQ_RANGE_CHECK aurc 
WHERE aurc.range_id NOT IN 
             (SELECT base_system_feature_xla_id 
                   FROM DNRANGE_SYS_FEATURE_XLA) 
AND p_UNI_VALUE_NUM >= aurc.from_value 
and p_UNI_VALUE_NUM <= aurc.to_value;

Cannot commit an entry to database which has "e" character while other characters are number

2 Answers