DROP TABLE voyagers
/
CREATE TABLE voyagers
(
    id NUMBER,
    details CLOB
);
ALTER TABLE voyagers ADD CONSTRAINT PK_VOYAGERS PRIMARY KEY(id);

INSERT ALL
    INTO voyagers(id, details) VALUES (1, 'aaaaaaaaaaa')
    INTO voyagers(id, details) VALUES (2, 'abbbbbbbbbbbbffffffeee')
    INTO voyagers(id, details) VALUES (100, 
'The ship has left san diego http:/localhost/icons/sandiego.png to okinawa on nov 10, 2011.')
SELECT * FROM DUAL;


Table dropped.


Table created.


Table altered.


3 rows created.


SQL> SELECT * FROM voyagers;

	ID DETAILS
---------- --------------------------------------------------------------------------------
	 1 aaaaaaaaaaa
	 2 abbbbbbbbbbbbffffffeee
       100 The ship has left san diego http:/localhost/icons/sandiego.png to okinawa on nov

SQL> 

DECLARE
    PROCEDURE replaceStr
    (
        param_id        IN  NUMBER,
        param_searchStr IN  VARCHAR2,
        param_newStr    IN  VARCHAR2
    )
    IS
    BEGIN
        UPDATE VOYAGERS 
        SET details = 
            REGEXP_REPLACE
            (
                details          , 
                param_searchStr  ,
                param_newStr     ,
                1                ,
                0                
            )
        WHERE id = param_id;
    END;
BEGIN
    replaceStr
    (
        100,
        'http:/localhost/icons/sandiego.png',
        'http:/localhost/icons/okinawa.png'
    );
END;
/

SELECT * FROM voyagers;

PL/SQL procedure successfully completed.


	ID DETAILS
---------- --------------------------------------------------------------------------------
	 1 aaaaaaaaaaa
	 2 abbbbbbbbbbbbffffffeee
       100 The ship has left san diego http:/localhost/icons/okinawa.png to okinawa on nov

SQL>