Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Research Triangle Park, NC
    Posts
    3

    Angry Unanswered: how to insert RAW and Timestamp data into Oracle

    Hi Everyone,

    Disclaimer - I am a complete Oracle idiot

    so I have a script that works great in DB2 to insert data into two columns - a GUID as binary data into one column (char(16) for bit data) of a table (example: x'b1462f30c87de329560e53d3f2a1847d'), and in the same record, insert a GMT (using current timestamp - current timezone) timestamp as the create timestamp for the record.

    those don't exactly port nicely over to Oracle, as i'm quickly learning.

    so i made a Oracle table with two columns ( my_guid RAW(16) not null), my_crt_ts timestamp(0) ).

    What's the right magic incantation i need to do to my data script (that contains thousands of inserts) to get the binary data and a GMT timestamp into the Oracle table? I'm more than happy to change the Oracle table, but try to keep me clear from Oracle triggers and procedures if possible.

    Thanks!!!!!
    -jacob

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is time to educate yourself by RTFM
    http://download-west.oracle.com/docs...a96540/toc.htm
    TO_DATE function might help with timestamp
    RAWTOHEX might help with GUID.
    It appears that you are using DB2 specific function previously
    and not using "standard" SQL functions.
    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.

  3. #3
    Join Date
    Mar 2004
    Location
    Research Triangle Park, NC
    Posts
    3
    yes i understand that i'm not using standard SQL in the DB2 script for the GMT timestamp, hence the post to see what i need to change to get it to work in Oracle

    will RAWTOHEX preserve the data as binary? It's important for me to keep the binary data (GUID) as binary in Oracle, but i can't find anywhere that says (in Oracle) how to insert binary data.

    Thanks!!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In my CSC 101 class I learned that no real difference exists between
    binary and hexadecimal. Grouping 4 binary digits together automagically "converts" them to one hexadecimal digit/character.

    Bits are bits; regardless of whether they are present to a human
    as only ones and zeros or are presented as 0-9 plus A-F characters.
    Now if you really want to dive into the details, you'll need to be conerned about the differences between Big-Endian & Little-Endian
    hardware architectures.

    Is the most significant bit at the right or left side of the binary string?

    Are you dealing with signed or unsigned binary values?

    Does it matter if you are going from a 32-bit to a 64-bit OS/DB?
    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.

  5. #5
    Join Date
    Mar 2004
    Location
    Research Triangle Park, NC
    Posts
    3
    well, my question is focused around exactly what Oracle does once i insert the data. This is so i know if i need to modify the data in any way once i get it out of the record and store it as a byte array. If Oracle converts my data and stores the binary as a string, i'd really like to know that.

    I'm aware of the knowledge you present about bits, binary and hexadecimal, but thank you.

    It appears that after searching in another site, the HEXTORAW() function will actually translate the binary data correctly. So i need to surround my byte data with that function in order to work correctly (example: HEXTORAW('b7458f80c87d3e2591ee53d3f2a1847d') ).

    The comment on using RAWTOHEX actually does the opposite of what is needed.

    It would be appreciated if anyone knows of the necessary steps needed to convert the DB2 version of a GMT Timestamp (current timestamp - current timezone) to the correct insert statement into a Oracle table.

    Thanks!

Posting Permissions

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