-
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;
/
-
What is the problem you are getting ? Do you get an error message or does it just not do the update ?
-
Hi
I am just not sure how to do it, I am not realy a programmer
-
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;
-
thanks I will give it a try
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|