3 Replies Latest reply: Feb 8, 2013 3:53 AM by Paul Horth RSS

    ora-00911 invalid character

    abdul:
      Hi there,

      As i run this query on toad it showing error:
      update XYZ set duration_before=(TO_DATE('08/22/2012 17:00:00','mm/dd/yyyy hh24:mi:ss')- STARTDATE)*24*60*60, 
      duration_after=((STARTDATE)+duration/86400-TO_DATE('08/22/2012 17:00:00','mm/dd/yyyy hh24:mi:ss'))*24*60*60,value=2
      WHERE ((startdate)+duration/86400)>(TO_DATE('08/22/2012 17:00:00','mm/dd/yyyy hh24:mi:ss')) and startdate<(TO_DATE('08/22/2012 17:00:00','mm/dd/yyyy hh24:mi:ss'))
      and trim(slab)='A2A' AND DEDICATEDACCUSED IS NULL and teleservicecode ='0' and duration<='120' and serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79');
      
      update XYZ set duration_before=(TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss')- STARTDATE)*24*60*60, 
      duration_after=((STARTDATE)+duration/86400-TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss'))*24*60*60,value=3
      WHERE ((startdate)+duration/86400)>(TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss')) and startdate<(TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss'))
      and trim(slab)='A2A' AND DEDICATEDACCUSED IS NULL and teleservicecode ='0' and and duration<='120' serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79');
      
      
      update XYZ set actual_rate=1.652 where trim(slab) ='A2A' and to_char(startdate,'hh24') IN('06','07','08','09','10','11','12','13','14','15','16','22','23','00','01','02','03','04','05') 
      and serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79') and teleservicecode='0' and duration<='120';
      update XYZ set actual_rate=((1.652*120)+(0.13216*(duration-120)))/duration where trim(slab) ='A2A' and to_char(startdate,'hh24') IN('06','07','08','09','10','11','12','13','14','15','16','22','23','00','01','02','03','04','05') 
      and serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79') and teleservicecode='0' and duration>'120';
      update XYZ set actual_rate=2.645 where trim(slab) ='A2A' and to_char(startdate,'hh24') IN('17','18','19','20','21') 
      and serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79') and teleservicecode='0' and duration<='120';
      update XYZ set actual_rate=((2.645*120)+(0.13216*(duration-120)))/duration where trim(slab) ='A2A' and to_char(startdate,'hh24') IN('17','18','19','20','21') 
      and serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79') and teleservicecode='0' and duration>'120';
      I get: ORA-00911: invalid character

      what's wrong?
        • 1. Re: ora-00911 invalid character
          793996
          Its syntactical error dear on line# 9:

          and trim(slab)='A2A' AND DEDICATEDACCUSED IS NULL and teleservicecode ='0' and duration<='120' and serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79');

          ...Viv
          • 2. Re: ora-00911 invalid character
            AlbertoFaenza
            Hi,

            since you have 6 update statements, could you at least identify which one is giving you error by running them one by one?

            Without table structure we cannot say too much but usually this error comes when you are trying to update a field using a special character.
            You can google for this error to find more info but I show here below an example:
            SQL> create table mytable
            ( col1 int)
            Table created.
            
            SQL> insert into mytable values(1)
            1 row created.
            
            SQL> update mytable
            set col1=?
            
            Error at line 7
            ORA-00911: invalid character
            Regards.
            Al
            • 3. Re: ora-00911 invalid character
              Paul  Horth
              update XYZ set duration_before=(TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss')- STARTDATE)*24*60*60,
              duration_after=((STARTDATE)+duration/86400-TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss'))*24*60*60,value=3
              WHERE ((startdate)+duration/86400)>(TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss')) and startdate<(TO_DATE('08/22/2012 22:00:00','mm/dd/yyyy hh24:mi:ss'))
              and trim(slab)='A2A' AND DEDICATEDACCUSED IS NULL and teleservicecode ='0' and and duration<='120' serviceclass not in ('10','11','12','15','20','25','35','71','72','78','79');