2 Replies Latest reply: Feb 2, 2013 3:14 PM by rp0428 RSS

    ORA-01722: invalid number

    user522961
      Hi all,
      thank for any help
      SQL> select TO_CHAR(sql_exec_start,'dd/mm/yy') from v$active_session_history WHERE sql_id = '9dbt4f5wbhnm0' AND sql_exec_id = 16777
      216 and rownum<=2 ;
      
      TO_CHAR(
      --------
      02/02/13
      02/02/13
      
      SQL> select TO_CHAR(sql_exec_start,'dd/mm/yy') from v$active_session_history WHERE sql_id = '9dbt4f5wbhnm0' AND sql_exec_id = 16777
      216 and rownum<=2 and sql_exec_start=TO_CHAR('02/02/13','dd/mm/yy') ;
      select TO_CHAR(sql_exec_start,'dd/mm/yy') from v$active_session_history WHERE sql_id = '9dbt4f5wbhnm0' AND sql_exec_id = 16777216 a
      nd rownum<=2 and sql_exec_start=TO_CHAR('02/02/13','dd/mm/yy')
      
                                              *
      ERROR at line 1 :
      ORA-01722: invalid number
      
      
      SQL> select TO_CHAR(sql_exec_start,'dd/mm/yy') from v$active_session_history WHERE sql_id = '9dbt4f5wbhnm0' AND sql_exec_id = 16777
      216 and rownum<=2 and sql_exec_start=TO_DATE('02/02/13','dd/mm/yy') ;
      
      no rows selected
        • 1. Re: ORA-01722: invalid number
          P.Forstmann
          You need
          1. to avoid comparing directly DATE and VARCHAR2 column even if Oracle makes automatic type conversion
          2. to remove hour/minute/seconds from DATE data type with SQL TRUNC function if you want to compare only with day/month/year:
          SQL> alter session set nls_date_format='DD/MM/YY';
          
          Session altered.
          
          SQL> select sysdate from dual;
          
          SYSDATE
          --------
          02/02/13
          
          SQL> select count(sql_id)
            2  from v$active_session_history
            3  where sql_exec_start = to_date('02/02/13');
          
          COUNT(SQL_ID)
          -------------
                      0
          
          SQL> select count(sql_id)
            2  from v$active_session_history
            3   where trunc(sql_exec_start) = to_date('02/02/13');
          
          COUNT(SQL_ID)
          -------------
                    321
          • 2. Re: ORA-01722: invalid number
            rp0428
            >
            thank for any help
            >
            What is it you need help with?

            Your last query showed the proper way to compare a DATE column such as 'SQL_EXEC_START'.

            But since DATE columns can include time and your date literal has no time component you will likely not find any exact matches; which your last query also shows.

            Just use TRUNC(SQL_EXEC_START) for the comparison.

            See V$ACTIVE_SESSION_HISTORY in the Database reference
            http://docs.oracle.com/cd/E11882_01/server.112/e24448/dynviews_1007.htm
            >
            SQL_EXEC_START DATE Time when the execution of the SQL started
            >
            Note that the doc says 'DATE Time' and not just date.