Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    68

    Unanswered: data format in database

    Hi to all,

    i have one table table name is
    test_table
    name varchar(30)

    in test_table i inserted data for name
    values are 1,2,3,4 etc........
    i inserted only number in name filed.

    when i select values from test_table its working fine.
    how it is working , varchar value should in single quotes?

    select * from test_table where name in (1,2,3);

    my some other doubts

    1.suppose if i insert data in one table through c,java program how it will manage the format. As per my understanding high level language foramt is ASCII and database format is UNICODE.(is it correct or not).
    And data from database to c,java program how it will manage?


    please help me.


    regards
    Jagan

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Different data types (wether in Oracle or any programming language) are stored in different formats, so in java a number is not stored in the same way as a string. Also both programming languages and Oracle can do automatic data type conversion so assigning a number to string can cause automatic conversion of the number into a string without you having to convert it (say in Oracle using to_char).

    In your case Oracle knows it can safely convert any number into a string so thats whats its done for you. NOTE both programming languages and Oracle can get automatic type conversion wrong in certain cases in which case it will throw an error.

    Also you should be using VARCHAR2 not varchar.

    Alan

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Also, one might get into trouble when relying onto the implicit datatype conversion. Things that used to work once might stop working.

    Here is an example: first, we'll create a table which should store characters. Our business rules were such that one of those values is a number. Using this value in queries (in the WHERE clause) works quite fine:
    Code:
    SQL> create table test (id varchar2(1));
    
    Table created.
    
    SQL> insert into test values (1);
    
    1 row created.
    
    SQL> select * from test where id = 1;
    
    I
    -
    1
    One day, you'll run out of possible numeric values (0, 1, ..., 9) for this VARCHAR2(1) column and - as all your forms, reports, etc. have this column formatted as such (length = 1) - you'll decide to use letters. After all, it is a character column, isn't it?
    Code:
    SQL> insert into test values ('A');
    
    1 row created.
    Suddenly, you'll get a panic phone call from one end user, then another one, and all of them will say that they receive a strange error, but they didn't do anything different than yesterday. Guess what? Developer who guessed that 1 = '1', i.e. that Oracle will implicitly convert 1 into '1', guessed wrong.
    Code:
    SQL> select * from test where id = 1;
    ERROR:
    ORA-01722: invalid number
    
    
    
    no rows selected
    
    SQL>
    At the same time, this works correctly:
    Code:
    SQL> select * from test where id = '1';
    
    I
    -
    1
    
    SQL>
    Such a story repeats quite often, especially with dates.

Posting Permissions

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