Oracle CLOB Operations

--Oracle CLOB Operations--Created on 2015/4/8 by TianpingDeclare   --Local variables hereV_clob1 Clob;  V_CLOB2 Clob; V_amountInt;--lengthV_offsetInt;--Offset Amount    Str varchar2( -);Begin          
 --initial or empty CLOB variabledbms_lob.createtemporary (v_clob1,true); Dbms_lob.createtemporary (v_clob2,true);

--writing characters to a CLOB variableDbms_lob.write (V_CLOB1, -,1,'123456789012345678901234567890'); Dbms_output.put_line (V_CLOB1);

--initial or empty Clob field      UpdateTest_lobSetClob_test=Empty_clob ()Wherelob_id=1;
      --writes the Clob field. To obtain the CLOB locator and lock the record, the maximum buffer length is 32767, exceeding the number of times to write       SelectClob_test intoV_clob2 fromTest_lobWherelob_id=1;
     --Update test_lob Set clob_test=v_clob1 Where lob_id=1;Dbms_lob.write (V_CLOB2, -,1,'abcdefghijklmnopkrstuvwxyz');
      --fetch CLOB Field locator      SelectClob_test intoV_clob1 fromTest_lobWherelob_id=1 ; Dbms_output.put_line (V_CLOB1);
      --writing a string to a CLOB variableV_amount:= -;--Write LengthV_offset:= +;--Start Write LocationDbms_lob.write (V_clob1,v_amount,v_offset,'abcdefghijklmnopkrstuvwxyz'); Dbms_output.put_line (V_CLOB1);
      --Erase CLOB variable partial string, erase part with space insteadV_amount:=5;--Erase lengthV_offset:= to;--Start Erase locationdbms_lob.erase (V_clob1,v_amount,v_offset); Dbms_output.put_line (V_CLOB1);
      --intercept CLOB Front partial stringV_amount:= -;--Intercept LengthDbms_lob.trim (V_clob1,v_amount); Dbms_output.put_line (V_CLOB1);
            --intercept CLOB Partial string, note that substr is a function rather than a stored procedure, and does not modify the value of the CLOB variableV_amount:= One;--Intercept LengthV_offset:=3;--Start intercept positionDbms_output.put_line (Dbms_lob.substr (V_clob1,v_amount,v_offset)); Dbms_output.put_line (V_CLOB1);
            --finds the position of the string ' 89 ' in Clob starting at the 1th occurrence of the 11th characterDbms_output.put_line (Dbms_lob.instr (V_CLOB1,' the', One,1));
             --finds the position of the string ' 89 ' in the CLOB starting at the 2nd occurrence of the 5th character, as if no return 0 appearsDbms_output.put_line (Dbms_lob.instr (V_CLOB1,' the', One,2));
     
--take Clob object lengthDbms_output.put_line (Dbms_lob.getlength (V_CLOB1));
  
--adds a buffer string to the end of the Clob object, with a maximum buffer length of 32767, more than multiple writes      Str:='abcdefghijklmnopqrstuvwxyz'; Dbms_lob.append (V_CLOB1,Str); Dbms_output.put_line (V_CLOB1);
  --the first 5 characters of the intercept buffer are added to the tail of the Clob object      Str:='1234567890'; Dbms_lob.writeappend (V_CLOB1,5,Str); Dbms_output.put_line (V_CLOB1);
--read partial string to bufferV_amount:= -;--Read LengthV_offset:= +;--Start Read locationDbms_lob.Read(V_clob1,v_amount,v_offset,Str); Dbms_output.put_line (Str); End;

Oracle CLOB Operations