Clobs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Clobs

  1. #1
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52

    Question Clobs

    I have a problem where I need to update a clob field.
    I have an exising value in the field which I need to convert and re update the field. I am having no luck. The data in the field currently looks like this
    [2003/10/20 09:35:46][User_name] blah blah blah [2003/10/20 09:35:01][User_name] blah blah blah [2003/10/20 09:34:32][User_name] blah blah blah

    I need to convert it too
    1066642546.User_name.blah blah blah.1066642501.User_name.blah blah blah1066642472.User_name.blah blah blah

    the "." between each field is chr('46')
    The number is the date converted to epoch (19700101000000)
    I use a stored proc to convert that
    CREATE OR REPLACE FUNCTION date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
    /**
    * Converts an Oracle DATE to a UNIX timestamp
    */
    unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
    max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
    min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
    unix_ts PLS_INTEGER;

    BEGIN

    IF oracle_date > max_date THEN
    RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
    ELSIF oracle_date < min_date THEN
    RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
    ELSE
    unix_ts := (oracle_date - unix_epoch) / (1/86400);
    END IF;

    RETURN (unix_ts);

    END;
    /

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    What is the problem you are getting ? Do you get an error message or does it just not do the update ?

  3. #3
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52
    Hi
    I am just not sure how to do it, I am not realy a programmer

  4. #4
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    You'd probably be best off writing some PL/SQL. Something along the following lines should get you fairly close:

    Code:
    declare 
      v_string clob := 
      '[2003/10/20 09:35:46][User_name] blah blah blah [2003/10/20 09:35:01][User_name] blah blah blah [2003/10/20 09:34:32][User_name] blah blah blah';
      v_outstring clob;
      v_substring1 varchar2(1000);
      v_substring2 varchar2(1000);  
      v_counter integer := 1;
      v_pos1     integer;
      v_pos2     integer;
    begin
      loop
         v_pos1 := instr(v_string, '[', 1, v_counter);
         v_pos2 := instr(v_string, ']', 1, v_counter);
         v_substring1 := substr(v_string, v_pos1+1, v_pos2 - v_pos1-1);
         v_substring1 := date_to_unixts(to_date(v_substring1, 'YYYY/MM/DD HH24:MI:SS'));  
         v_pos1 := v_pos2 + 1;
         v_counter := v_counter + 2;
         v_pos2 := instr(v_string, '[', 1, v_counter);     
         if v_pos2 = 0 then
            v_pos2 := length(v_string)+1;
         end if;
         v_substring2 := translate(substr(v_string, v_pos1, v_pos2-v_pos1), '[]', '..')||'.';
         v_outstring := v_outstring||v_substring1||v_substring2;
         exit when v_pos2 = length(v_string)+1;
      end loop;
      dbms_output.put_line(v_outstring);
    end;

  5. #5
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52

    Talking

    thanks I will give it a try

  6. #6
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52
    It works for the forst record
    when I read it through a cursor it only works for the first record and then seems to bomb on the rest

  7. #7
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52
    sorry I initilized the variables and that seed to do the trick 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
  •  



Click Here to Expand Forum to Full Width