Results 1 to 12 of 12
  1. #1
    Join Date
    May 2012
    Posts
    16

    Unanswered: How to convert data to binary_double during insert statement.

    I have a table created with 3 columns using this code

    Code:
    CREATE TABLE float_point_demo
      (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);

    and inserted some value using this insert statement
    Code:
    INSERT INTO float_point_demo
      VALUES (1234.56,1234.56,1234.56);

    after insert the number inserted in bin_double should be converted to its binary_double equivalent but somehow it did not. The value inserted is still the same. I copied that code in oracle docs and the result there is different from the result that I had.

    How do I do this? did I do something wrong? any help would be very much appreciated.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Worked for me.

    >CREATE TABLE float_point_demo
    2 (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);

    Table created.

    >INSERT INTO float_point_demo
    2 VALUES (1234.56,1234.56,1234.56);

    1 row created.

    raydw@rayora1>commit;

    Commit complete.

    1>select * from float_point_demo;

    DEC_NUM BIN_DOUBLE BIN_FLOAT
    ---------- ---------- ----------
    1234.56 1.235E+003 1.235E+003

    All of the column types hold decimal positions so of course they will store the same number.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2012
    Posts
    16
    raydw@rayora1>commit;

    Commit complete.


    what does that lines do?
    I didnt do any commit maybe that's the reason why I get wrong results?
    Thank you for the response

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Commit just makes the transaction permanent. What version of the database are you using? type

    select * from v$version;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2012
    Posts
    16
    I am using 11g...

    DEC_NUM BIN_DOUBLE BIN_FLOAT
    ---------- ---------- ----------
    1234.56 1.235E+003 1.235E+003


    when I tried using the statements the result is like this

    DEC_NUM BIN_DOUBLE BIN_FLOAT
    ---------- ---------- ----------
    1234.56 1234.56 1234.56


    the inserted values didnt convert to its binary double and binary float equivalent

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What difference do you expect to actually see between the three values?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2012
    Posts
    16
    DEC_NUM BIN_DOUBLE BIN_FLOAT
    ---------- ---------- ----------
    1234.56 1.235E+003 1.235E+003


    I am expecting that result...instead of 1234.56 I am expecting 1.235E+003

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am expecting that result...instead of 1234.56 I am expecting 1.235E+003
    Your expectations don't match Oracle's reality.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2012
    Posts
    16
    but how come beilstwh gets this result

    DEC_NUM BIN_DOUBLE BIN_FLOAT
    ---------- ---------- ----------
    1234.56 1.235E+003 1.235E+003

    and mine is different?

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    18:49:23 SQL> @tt
    18:49:26 SQL> CREATE TABLE float_point_demo
    18:49:26   2  (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);
    
    Table created.
    
    18:49:27 SQL> 
    18:49:27 SQL> INSERT INTO float_point_demo
    18:49:27   2   VALUES (1234.56,1234.56,1234.56);
    
    1 row created.
    
    18:49:27 SQL> commit;
    
    Commit complete.
    
    18:49:27 SQL> select * from float_point_demo;
    
       DEC_NUM BIN_DOUBLE  BIN_FLOAT
    ---------- ---------- ----------
       1234.56 1.235E+003 1.235E+003
    
    18:49:28 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Its just the way my display settings are setup, I can get the same result by using a format mask.

    1 select to_char(dec_num,'9999.99') dec, to_char(bin_double,'9999.99') double,
    2 to_char(bin_float,'9999.99') flt
    3* from float_point_demo
    raydw@rayora1>/

    DEC DOUBLE FLT
    -------- -------- --------
    1234.56 1234.56 1234.56
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    May 2012
    Posts
    16
    Thank you very much..
    I think it was just the display settings...
    I tried selecting the records thru sql plus and
    found what i wanted to see...
    Thank you all for your help.

Posting Permissions

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