1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with datatype checking

Discussion in 'SQL PL/SQL' started by vinee_15, Feb 10, 2011.

  1. vinee_15

    vinee_15 Guest

    Hi,
    I am using Oracle 10g. This is oracle specific problem. My problem is with datatype checking while working with oracle.
    My java program generates a complex query dynamically.The column COLLEGE_CODE is of CHAR(10) and I am adding where clause WHERE COLLEGE_CODE = 001.
    If there are records present in the table, it will give ORA-01722: invalid number error. This error is valid because I am adding a where clause with number for CHAR type column.
    If records not present in the table, it will not give any error.
    This where clause is wrong and it should give error everytime for this situation.
    Could anyone please tell me what is the problem with Oracle. This is only oracle specific.
    Is it like Oracle is not checking datatype of where clause column, if there is no records present in the table?

    Thanks in advance,
    Warm regards,
    Vinee
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Short answer, use NVL to return numbers for Null values to always throw the error.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What, exactly, is the purpose of this exercise? Why do you code a WHERE clause you know will return an error??
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is no such case (error) as per your example.

    Code (SQL):

    SQL> DESC test1
     Name                                                                                                                                                     NULL?    TYPE
     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     COLLEGE_CODE                                                                                                                                              CHAR(10)
     COLLEGE_CODE_V                                                                                                                                            VARCHAR2(10)

    SQL>  SELECT * FROM test1 WHERE COLLEGE_CODE='0001';

    SQL> SELECT * FROM test1 WHERE COLLEGE_CODE='0001';

    COLLEGE_CO COLLEGE_CO
    ---------- ----------
    0001       0001

    SQL>  SELECT * FROM test1 WHERE COLLEGE_CODE_V='0001';

    COLLEGE_CO COLLEGE_CO
    ---------- ----------
    0001       0001

    SQL>  SELECT * FROM test1 WHERE COLLEGE_CODE=0001;

    COLLEGE_CO COLLEGE_CO
    ---------- ----------
    0001       0001

    SQL>  SELECT * FROM test1 WHERE COLLEGE_CODE_V=0001;

    COLLEGE_CO COLLEGE_CO
    ---------- ----------
    0001       0001

    SQL> SELECT * FROM test1 WHERE COLLEGE_CODE=1;

    COLLEGE_CO COLLEGE_CO
    ---------- ----------
    0001       0001

    SQL>  SELECT * FROM test1 WHERE COLLEGE_CODE_V=1;

    COLLEGE_CO COLLEGE_CO
    ---------- ----------
    0001       0001

    SQL> DELETE FROM test1;

    2 ROWS deleted.

    SQL> commit;

    Commit complete.

    SQL> SELECT * FROM test1 WHERE COLLEGE_CODE=1;

    no ROWS selected

    SQL> SELECT * FROM test1 WHERE COLLEGE_CODE_V=1;

    no ROWS selected

    SQL> SELECT * FROM test1 WHERE COLLEGE_CODE=001;

    no ROWS selected

    SQL> SELECT * FROM test1 WHERE COLLEGE_CODE_V=001;

    no ROWS selected

    SQL>