Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have tried: UPDATE "SCHEMA".TABLE SET LOB_COLUMN = (SELECT LOB_COLUMN FROM "SCHEMA".TABLE)

I have tried:

CREATE OR REPLACE PROCEDURE COPY_LOB_DATA IS
    OLD_BLOB BLOB;
    NEW_BLOB BLOB;
    BLOB_LENGTH NUMBER;
BEGIN
    SELECT LOB_COLUMN INTO OLD_BLOB FROM SCHEMA.TABLE WHERE OLD;
    SELECT LOB_COLUMN INTO NEW_BLOB FROM SCHEMA.TABLE WHERE NEW;
    SELECT dbms_lob.length(NEW_BLOB) INTO BLOB_LENGTH FROM DUAL;
    dbms_lob.TRIM(OLD_BLOB,1);
    dbms_lob.copy(OLD_BLOB,NEW_BLOB,BLOB_LENGTH);
    if (dbms_lob.compare(OLD_BLOB,NEW_BLOB) = 0) THEN
        commit;
    ELSE 
        rollback;
    END IF;
END;

and I've tried using cursors. Each method seems to work four times out of five, but every so often we'll encounter a field that just won't budge. the largest of these would be around fifty megs, but usually it's one of the smaller ones.

share|improve this question
2  
Are you getting an error message or anything? Try adding an EXCEPTION block to your PL/SQL to catch? –  Phil Oct 8 '12 at 20:47
    
@Phil: adding an exception block won't make the error magically appear, quite the contrary! Don't catch unexpected errors, that's my advice. –  Vincent Malgrat Oct 9 '12 at 8:10
    
@watt Is the error reproducible? You have to find a precise example, a specific LOB that you can't update for example. Then post the details here so that we can help you. –  Vincent Malgrat Oct 9 '12 at 8:14
    
@VincentMalgrat Well, yes. What I'm alluding to is that we can't help unless there's a specific error we can look at. Oracle isn't known to silently fail –  Phil Oct 9 '12 at 8:24
    
A pure SQL update should work. Please post the error number you get. –  Colin 't Hart Oct 9 '12 at 8:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.