This discussion is archived
4 Replies Latest reply: Jan 21, 2013 1:20 AM by BluShadow RSS

BLOB Value Replace and Built a String.

985232 Newbie
Currently Being Moderated
Hi,

I have a blob value passed to my procedure and I need to find a value in the blob and replace with a different value and then convert the entire string to BLOB and then insert this value to a table.

Ex: BLOB_String = 'ATRTYOFPTE9TYTUTUTUOE9TY'

Procedure Convert_blob(BLOB_String BLOB)
(
Con_BLOB_String BLOB

-- Read the Blob_String and check for '9' and replace with '-1'
Con_BLOB_String := to_blob( 'ATRTYOFPTE-1TYTUTUTUOE-1TY')

-- Insert the Value to the table
Insert Into Blob values(Con_BLOB_String);

)

Thanks in advance
Sam
  • 1. Re: BLOB Value Replace and Built a String.
    jeneesh Guru
    Currently Being Moderated
    Read at AskTom
  • 2. Re: BLOB Value Replace and Built a String.
    BluShadow Guru Moderator
    Currently Being Moderated
    Why are you wanting to store character based data (strings) in a BLOB?
    BLOB's are designed for storing binary data. You should use CLOB's to store character data (the clue is in the name... C haracter L arge OB ject, rather than B inary L arge OB ject)
  • 3. Re: BLOB Value Replace and Built a String.
    985232 Newbie
    Currently Being Moderated
    Actually my BLOB data has binary numbers

    '00101001011000000'

    I need to get this converted to_number - '86765' - Now I need to replace the occurance of 6 with 1 and then convert back to binary and insert to a different table.

    Thanks
  • 4. Re: BLOB Value Replace and Built a String.
    BluShadow Guru Moderator
    Currently Being Moderated
    982229 wrote:
    Actually my BLOB data has binary numbers

    '00101001011000000'

    I need to get this converted to_number - '86765' - Now I need to replace the occurance of 6 with 1 and then convert back to binary and insert to a different table.

    Thanks
    I don't think you understand the difference between binary and character data.

    '00101001011000000' is a string of '0' and '1' characters, not binary data.

    86765 as a binary string is 10101001011101101
    which as binary data is 3 bytes (00000001,01010010,11101101) with decimal values of 1, 82, 237 (hex: 01,52,ED)

    So, if the value of 86765 were stored in a BLOB datatype I would expect it to be 3 bytes of data...
    SQL> create table t (x blob);
    
    Table created.
    
    SQL> insert into t (x) values (utl_raw.cast_to_raw(chr(1)||chr(82)||chr(237)));
    
    1 row created.
    
    SQL> select * from t;
    
    X
    -------------------------------------------------------------------------------
    0152ED
    This is binary data correctly stored in a BLOB.

    If the data is in fact '10101001011101101' then that is a string and should be stored in a CLOB...
    SQL> create table t2 (x clob);
    
    Table created.
    
    SQL> insert into t2 (x) values ('10101001011101101');
    
    1 row created.
    
    SQL> select * from t2;
    
    X
    -----------------------------------------------------
    10101001011101101
    So in your first post where you said:
    BLOB_String = 'ATRTYOFPTE9TYTUTUTUOE9TY'
    This was clearly wrong. Strings are not stored in BLOB's, because they store binary data (bytes) that, when queried, will typically (depends on the interface) be shown as hexidecimal as they are retrieved in their Raw format. That 'string' is certainly neither binary data, or a hexidecimal representation.

    Let's assume you really do have a string of binary digits (and that string was correctly stored in a CLOB)... you could achieve your 'replacement' of the decimal digits something like:
    SQL> create table t2 (x clob);
    
    Table created.
    
    SQL>
    SQL> insert into t2 (x) values ('10101001011101101');
    
    1 row created.
    
    SQL>
    SQL> with b as (select x as bin from t2)
      2      ,b2(last, bits, bin, val) as (
      3                      -- Convert a binary string to a decimal number
      4                      -- anchor query
      5                      select 0 as last, 0 as bits, bin, 0 as val from b
      6                      union all
      7                      -- recursive query
      8                      select decode(b2.bits+1,length(b.bin),1,0)
      9                            ,bits+1
     10                            ,substr(b2.bin,2)
     11                            ,(b2.val*2)+to_number(substr(b2.bin,1,1))
     12                      from b, b2
     13                      where b2.bits+1 <= length(b.bin)
     14                     )
     15  --
     16    ,bin_to_num as (select b.bin, b2.bits, b2.val
     17                    from   b, b2
     18                    where  b2.last = 1)
     19    ,replace_digits as (select o.bin, o.bits, o.val
     20                              ,replace(to_char(o.val),'6','1') as val_repl  -- Replace digit '6' with digit '1'
     21                              ,to_char(replace(to_char(o.val),'6','1'),'fm0000000X') as hex_val_repl
     22                        from bin_to_num o)
     23    ,n2b(last, digits, val, bin) as (
     24                -- Convert a decimal number to a binary string - will do up to 4 bytes
     25                -- anchor query
     26                select 0 as last, 0 as digits, hex_val_repl as val, cast('' as varchar2(32)) as bin from replace_digits
     27                union all
     28                -- recursive query
     29                select decode(n2b.digits+1,length(replace_digits.hex_val_repl),1,0)
     30                      ,digits+1
     31                      ,substr(n2b.val,2)
     32                      ,n2b.bin||case substr(n2b.val,1,1)
     33                                when '0' then '0000'
     34                                when '1' then '0001'
     35                                when '2' then '0010'
     36                                when '3' then '0011'
     37                                when '4' then '0100'
     38                                when '5' then '0101'
     39                                when '6' then '0110'
     40                                when '7' then '0111'
     41                                when '8' then '1000'
     42                                when '9' then '1001'
     43                                when 'A' then '1010'
     44                                when 'B' then '1011'
     45                                when 'C' then '1100'
     46                                when 'D' then '1101'
     47                                when 'E' then '1110'
     48                                when 'F' then '1111'
     49                                else null end
     50               from replace_digits
     51                    ,n2b
     52               where n2b.digits+1 <= 8
     53               )
     54  select ltrim(bin,'0') as bin
     55  from   n2b
     56  where  n2b.last = 1;
    
    BIN
    ------------------------------------
    10011111100110011
    (Personally I wouldn't trim the binary data, I'd have it with leading 0's to make whole bytes, however your original binary string was truncated, so I've given you the same back)

    If that's not the case an you insist that the data is stored in a BLOB, please provide us with some example data including an example create table and insert statements to set up that data, and show us what the expected result should also be.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points