CODEWITHSUNDEEP
databaseoracle-databaseoracle10g
Anant Kurapati
Anant Kurapati

Reputation: 128

Throwing Invalid number error in Oracle while executing OR

My Table structures are..

A_TEXT(ID number,name varchar2)
POLICY_INFO (PI_ID number,relation_id varchar2)

I have query like this.

SELECT * FROM A_TEXT A  WHERE   a.name  = 'test' OR  a.ID IN 
            (SELECT  TO_NUMBER(RELATION_ID )
             FROM POLICY_INFO 
             )

When I try to execute query I got Invalid Number exception. If you modified query little bit Like :

SELECT * FROM A_TEXT A  WHERE a.name  = 'test' OR  TO_CHAR(a.ID) IN 
           (SELECT  RELATION_ID
             FROM POLICY_INFO 
             )

It executes well.

Upvotes: 0

Views: 1360

Answers (2)

Srini V
Srini V

Reputation: 11365

Possible reason is that the column RELATION_ID contains non numeric characters (Anything apart from 0-9, NULL)

Remember that all NUMBER's can be converted into VARCHAR and not the vice versa.

To check the column RELATION_ID use this below function and validate your non numeric strings.

CREATE OR REPLACE FUNCTION isnumeric(p_string in varchar2)
RETURN BOOLEAN
AS
    l_number number;
BEGIN
    l_number := p_string;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
/

FOLLOW UP:

WITH POLICY_INFO
    AS (SELECT
             1 AS PI_ID,
             '1' AS RELATION_ID
        FROM
             DUAL),
    A_TEXT
    AS (SELECT
             1 AS ID,
             'testA' AS NAME
        FROM
             DUAL
        UNION ALL
        SELECT
             2 AS ID,
             'test' AS NAME
        FROM
             DUAL)
SELECT
      *
FROM
      A_TEXT A
WHERE
      A.NAME = 'test'
      OR A.ID IN (SELECT TO_NUMBER ( RELATION_ID ) FROM POLICY_INFO);

This works fine. Please check this. If you still face issue, let us know the complete dataset

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30815

Either you have non-numbers in your database column, or you have a number format problem. To debug this situation, you can do this (a slight variation of @realspirituals answer):

declare
  l_dummy number;
begin
  for cur in (select relation_id from policy_info)
  loop
     begin
        l_dummy := to_number(cur.relation_id);
     exception
        when others then dbms_output.put_line(cur.relation_id);
     end;
  end loop;
end;

This will print all offending values from policy_info.relation_id;

Upvotes: 0

Related Questions