Start Updating oracle clob

Updating oracle clob

Then update the record in the variable, delete the record in the long data type column, insert the variable data into the long column data type. I need to perform this update on a base table in application that should not be altered.

declare var1 clob := 'some string'; var2 blob := hextoraw('43'||'41'||'52'); begin result := dbms_lob.get_storage_limit(var1); dbms_output.put_line('CLOB maximum limit: ' || result); dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var1)); result := dbms_lob.get_storage_limit(var2); dbms_output.put_line('BLOB maximum: ' || result); dbms_output.put_line('CLOB length: ' || dbms_lob.getlength(var2)); end; / CLOB and NCLOB You can define CLOB or NCLOB in a table or a nested table, it allows you to store very large text files.

CLOB columns are usually stored separately from the rest of the row in the table, only the descriptor or locator (pointer) is physically stored in the column.

will contain the locator (pointer) of the clob Usually you would read/write in chunks that are hundreds of megabytes, gigabytes or terabytes in size but not for files that are less than 100MB.

Some developers resort to using C, C or Java to accomplish reading and writing small clob files.

Can you guys please help me in this through PL/SQL procedure as I am not much experienced on that.

Your help will be highly appreciated..." id="ctl00_m_m_i_ctl00_gr_ctl09_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="5020821" / Hi All, Thanks for your valuable suggestions. 1 declare 2 v1 varchar2(500); 3 begin 4 select field1 into v1 from long_test; 5 dbms_output.put_line(v1); 6 update long_test set field2 = v1; 7* end; SQL With all of your valuable suggestions, i had came to an approach but got hanged in the middle. Created a temporary table for the table that i want to update. converted the column in temporary table having long data type to CLOB. Applied the REPLACE operation which i want to perform on that temporary table. Updated contents in main table with contents from temporary table. ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4790, maximum: 4000) The other hard part is that main table can't be done any alteration.

(Moving a lob column might also rebuild the lob index - I'm not sure).

Insert empty_clob() into the Clob column of Oracle Step2. Depending on other circumstances, that might not hurt, but since the intent is to insert a populated clob, it should all happen in one transaction.

If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB.

There are articles on Meta Link explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit.

Commit Example: Please note that this was posted many years ago.