Clobs

[truma1]

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;
/

[Scorby]

What is the problem you are getting ? Do you get an error message or does it just not do the update ?

[truma1]

Hi
I am just not sure how to do it, I am not realy a programmer

[Scorby]

You'd probably be best off writing some PL/SQL. Something along the following lines should get you fairly close:

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;

[truma1]

thanks I will give it a try

[truma1]

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

[truma1]

sorry I initilized the variables and that seed to do the trick thanks