This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Oct 30, 2013 4:07 AM by BluShadow Go to original post RSS
  • 15. Re: how to split the blob byte array and insert in oracle
    rp0428 Guru
    Currently Being Moderated

    I am trying to get the result either in Java or in Oracle itself. 

    And yet you haven't bothered to answer any of the questions you have been ask.

     

    The standard 'float' representation in binary uses 4 bytes; but you say your 'float' data can use 3 or even 5 bytes:

    here Column2 datatype is float but it will always have 3 byte information.

    where as Column3 datatype is also float but it will always have 5 byte information.

    No one is going to be able to help you unless you explain what is in those 3 or 5 bytes since a standard float uses 4 bytes.

     

    You need to explain the actual binary structure you are using for the other columns as well since they are not standard either.

  • 16. Re: how to split the blob byte array and insert in oracle
    936666 Newbie
    Currently Being Moderated

    here Column2 datatype is float but it will always have 3 byte information.

    the database column2 is of data type FLOAT

    where as incoming byte array is of length 3 which will be stored in oracle database in column2(which is of float data type)

    where as Column3 datatype is also float but it will always have 5 byte information.

    Error correction: It should be Column3 Double with 5 byte information.

    the database column3 is of data type DOUBLE.

    where as incoming byte array is of length 5 which will be stored in oracle database in column3(which is of double data type)

     

    Thanks!

  • 17. Re: how to split the blob byte array and insert in oracle
    936666 Newbie
    Currently Being Moderated

    Any suggestions !!     

  • 18. Re: how to split the blob byte array and insert in oracle
    Mike Kutz Expert
    Currently Being Moderated

    I was close...

    I totally forgot, if you have fixed width records with fixed width data fields, you do NOT need a preprocessor with an EXTERNAL TABLE.

    Ask Tom "External Table referencing fixed width fields"

     

    However, as others have pointed out, 3 (or 5) bytes IS NOT an IEEE float/double.

     

    Beyond that, I suggest you get the source of the data to give you data that you can easily parse.

     

    MK

  • 19. Re: how to split the blob byte array and insert in oracle
    thomaso Journeyer
    Currently Being Moderated

    If you are using CLOB to store loaded byte array and you know the structure ( position and length ) of each element you can use

    DBMS_LOB.Substr.

    OraFAQ Forum: SQL & PL/SQL » DBMS_LOB.SUBSTR

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm

    HTH

    T.

  • 20. Re: how to split the blob byte array and insert in oracle
    rp0428 Guru
    Currently Being Moderated
    here Column2 datatype is float but it will always have 3 byte information.

    the database column2 is of data type FLOAT

    where as incoming byte array is of length 3 which will be stored in oracle database in column2(which is of float data type)

    where as Column3 datatype is also float but it will always have 5 byte information.

    Error correction: It should be Column3 Double with 5 byte information.

    the database column3 is of data type DOUBLE.

    where as incoming byte array is of length 5 which will be stored in oracle database in column3(which is of double data type)

    Yes - you already told us the lengths. What you haven't told us is what is IN those 3 and 5 bytes. Like I said above:

    No one is going to be able to help you unless you explain what is in those 3 or 5 bytes since a standard float uses 4 bytes.

     

    You need to explain the actual binary structure you are using for the other columns as well since they are not standard either.

  • 21. Re: how to split the blob byte array and insert in oracle
    936666 Newbie
    Currently Being Moderated

    my apologies for the wrong understanding.

    If it is float it is 4 bytes

    if it is double it is 8 bytes

    OK.

    I need to read 1 byte and 2 byte respectively from the BLOB

    BLOB will be some thing like

    byte size are given below.

     

    484241248

     

    bytes represented

    ----------------------------

    4-float

    8-double

    4-float

    2-smallint

    4-int

    1-char

    2-smallint

    4-char

    8-double

    need to split using ult_raw package.

    i am able to split first float and double and again float where as not able to split 2 byte smallint

  • 22. Re: how to split the blob byte array and insert in oracle
    BluShadow Guru Moderator
    Currently Being Moderated

    How about you provide us with a create table statement and a few insert statements with some example 'blob' data, and then tell us what you expect the output to look like from that example data.  That'll help us understand exactly what you want.

     

    And be very clear... is the data binary data, or is it character data?  If it's character data and you're storing it in a blob then you're doing it wrong, as it should be in a clob.  If it's binary data we need to have your example insert statements so we have an exact idea what the data looks like.

     

    The sooner you provide detailed information the sooner people can help.

     

    Re: 2. How do I ask a question on the forums?

  • 23. Re: how to split the blob byte array and insert in oracle
    936666 Newbie
    Currently Being Moderated

    I am having a BLOB say BLOB looks like this i need to read the blob based on bytes

    "Çð¦“B¦t眥>m "

    here in the example i am having a float and double

    4 bytes and 8 bytes respectively...

     

    if i read the blob i will be getting some as

    utl_raw.cast_to_binary_float((BLOB COLUMN)) = -123213.1454989 (4 bytes)

    utl_raw.cast_to_binary_double((BLOB COLUMN))  =12345678910111.2134 (8 bytes)

     

    where as in real BLOB information it is the combination floats doubles and int and chars

    i know the size of the BLOB.(say here it is 4+8 =12)

    i need to split and insert into my table which will be having say create table mytable (c1 float, c2 number (20,18));

    same way in the BLOB there will be combinations of float, double, smallint, char and so and so.....

    it is not character it is binary

    Thanks for the understanding my requirement

  • 24. Re: how to split the blob byte array and insert in oracle
    BluShadow Guru Moderator
    Currently Being Moderated

    And how do I create that same data in my database to demonstrate how to do what you want?

    Where are the create table and insert statements I asked for?

     

    If you don't provide these then people will not help.  Read the FAQ link provided.

    If you don't help people to help you, then they give up and go help other people... the choice is yours.

  • 25. Re: how to split the blob byte array and insert in oracle
    936666 Newbie
    Currently Being Moderated

    Create table myblob (bval BLOB)

    this is the blob

    insert into myblob

    Select "Çð¦“B¦t眥>m"  from dual;


    create table mytable (c1 float, c2 number (20,18));

    insert into mytable

    Select utl_raw.cast_to_binary_float((bval)),

    utl_raw.cast_to_binary_double((bval))

    from myblob;


    this is the sample , where as in realtime it is the comnibation of all which i have stated in my previous post.

    Thanks!


  • 26. Re: how to split the blob byte array and insert in oracle
    BluShadow Guru Moderator
    Currently Being Moderated

    Close, but not working.  Apart from your string being surrounded by double quotes, even if I 'fix' that... the insert statement is dependent on a particular database characterset, because you are providing us with a string to insert rather than a binary data insert.... so on my database I get...

     

     

    SQL> ed
    Wrote file afiedt.buf

      1  insert into myblob
      2* Select 'êôBªtþ£Ñ>m'  from dual
    SQL> /
    Select 'êôBªtþ£Ñ>m'  from dual
           *
    ERROR at line 2:
    ORA-01465: invalid hex number

     

    Note how the copy/paste of your string translates differently on my local environment.

     

    Please provide a working insert statement that will insert the exact bytes of binary data into the blob, no a characterset reliant string.  I'm not wasting my time going off to look up the binary values of the characters you've posted on the forum (and even the forum may be representing them differently to how you intended).

  • 27. Re: how to split the blob byte array and insert in oracle
    BluShadow Guru Moderator
    Currently Being Moderated

    For example, one way is to provide the insert statement as a hexadecimal representation of the bytes...

     

    SQL> ed
    Wrote file afiedt.buf

      1  insert into myblob
      2* Select 'AB1DEEA3000C13'  from dual
    SQL> /

     

    1 row created.

     

    And then, when you've provided your example data, remember to tell us what the expected output is from that example data.

  • 28. Re: how to split the blob byte array and insert in oracle
    936666 Newbie
    Currently Being Moderated

    say if it is

     

    insert into myblob

    Select

    utl_raw.cast_from_binary_float((-123213.1454989)) /*4 bytes*/

    || utl_raw.cast_from_binary_double((12345678910111.2134))  /*8 bytes*/

    from dual;

     

    i need to split and insert based on the bytes;

    ----------------------------------------------------------------

     

    create table mytable (c1 float, c2 number (20,18));

    insert into mytable

    Select utl_raw.cast_to_binary_float((bval)),

    utl_raw.cast_to_binary_double((bval))

    from myblob;


    select * from mytable;


    c1                         c2

    --------------------      -----------------------------------

    -123213.1454989 12345678910111.2134


    SELECT dbms_lob.getlength(bval) FROM myblob;


    DBMS_LOB.GETLENGTH(bval)

    ----------------------

    12                    

  • 29. Re: how to split the blob byte array and insert in oracle
    Mike Kutz Expert
    Currently Being Moderated

    EXTERNAL TABLES!!!

     

    SETUP TO PROVIDE EXAMPLE DATA

    create directory rw_dir as 'C:\';
    declare
      l_x binary_integer := 42;
      l_y binary_float := 3.14159;
    l_file utl_file.file_type;
    begin
      l_file := utl_file.fopen( 'RW_DIR', 'test.dat', 'AB' );
      utl_file.put_raw( l_file,  utl_raw.cast_from_binary_integer(  l_x )  );
      utl_file.put_raw(l_file, utl_raw.cast_from_binary_float( l_y ) );
      utl_file.fclose( l_file );
    end;
    /
    

     

    EXTERNAL TABLE

    create table bin_ext
    (
      x integer,
      y number
    )
    organization external
    ( type oracle_loader
      default directory rw_dir
      access parameters (
        records fixed 8
        data is big endian -- you may have to mess with this
        fields
       (
          x unsigned integer (4), -- IEEE INT
          y float -- IEEE float
       ) )
      location ('test.dat')
    );
    


    RESULT

    SQL> select * from bin_ext;

     

             X          Y

    ---------- ----------

            42 3.14159012

     

    SQL>