Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Question Unanswered: Quick datatype/null type question

    Hi all. Converting an ASP from SQL server to Oracle. There is an insert statement on one of the pages that puts " " into a column of the number datatype. In oracle, when I try to insert " " into a number column, I get the ORA-01722: invalid number error. (Of course that isn't very surprising) So my question is, what is best practice when it comes to something like this? Should I just have it populated with "0" if the column to be inserted is null? Or should I change the datatype to varchar or something of that nature so I can insert " " 's? As far as I can see, there is no type of indexing or constraints on this column...Is changing the datatype a bad move?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would fix the program. It has no business trying to store a text string in a NUMBER column!

  3. #3
    Join Date
    Feb 2004
    Posts
    108
    Besides this "Numeric_Column = NULL" may have different meaning from "Numeric_Column = 0"

  4. #4
    Join Date
    Dec 2003
    Posts
    148

    question..

    Okay, so my original question aside...if you have a number column in an oracle table, and you do not want to insert a value into the column (of "number" data type) what do you insert? (if i try to insert "NULL" i get the same error)

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You should not be getting an error with null:
    Code:
    SQL> create table t1 (x number);
    
    Table created.
    
    SQL> insert into t1 (x) values (null);
    
    1 row created.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •