Home » SQL & PL/SQL » SQL & PL/SQL » How to update a clob value (oracle 11g)
How to update a clob value [message #570656] Tue, 13 November 2012 15:31 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi,

I the table VOYAGERS with the following data.

ID is of type number and DETAILS is of type CLOB.


ID DETAILS
--- --------
100 The ship has left san diego http:/localhost/icons/sandiego.png to okinawa on nov 10, 2011.

I need to update the record(id = 100) by replacing the url "http:/localhost/icons/sandiego.png" with "http:/localhost/icons/okinawa.png".

I need a procedure where I will pass the ID value, replace string(i.e http:/localhost/icons/sandiego.png) and replace with string (ie. http:/localhost/icons/okinawa.png).

Any help is greatly appreciated.


Thanks

Re: How to update a clob value [message #570657 is a reply to message #570656] Tue, 13 November 2012 16:25 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
Hello there,


You can try this

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> 



Then you can use the following procedure to update your table

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> 




And here you can find more information about the function REGEXP_REPLACE

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions149.htm#SQLRF06302


Regards,
Dariyoosh

[Updated on: Tue, 13 November 2012 23:45] by Moderator

Report message to a moderator

Re: How to update a clob value [message #570664 is a reply to message #570656] Tue, 13 November 2012 20:53 Go to previous message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Dariyoosh,

Thank you very much for your prompt reply.

This is great. I was struggling with DBMS_LOB package. It was updating old sring with new string to the extent of same number of characters as old one the rest of the characters were being ignored.

ex: if i try to replace okinawa.png with tokyo1234567.png
it would only update to tokyo12345 since okinawa.png has 11 characters so it would update only 11 characters and the rest were ignored( May be Iam doing something wrong. I was using dbms_log.writeappend).

Thanks a lot again.
Previous Topic: Subtract dates
Next Topic: Assistance with querying large table
Goto Forum:
  


Current Time: Sun Nov 23 14:29:58 CST 2014

Total time taken to generate the page: 0.07975 seconds