This discussion is archived
2 Replies Latest reply: Feb 2, 2013 1:14 PM by rp0428 RSS

ORA-01722: invalid number

user522961 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points