Pl sql clob updating

I've found it a bit of a pain today to find a (relatively) simple way to work with XML in a BLOB/CLOB field in an Oracle 11g database via Oracle SQL Developer, so I want to note it for future reference.To UPDATE a BLOB/CLOB with a string, the string must be cast to RAW.That inhibits scalability massively -- in a multi-user test, the one without open/close would not scale very well at all. 100 11 LOOP 12 INSERT INTO lob_effect 13 VALUES 14 (idx, 15 empty_clob()) 16 RETURNING loc INTO lob_loc; 17 l_cnt := 0; 18 WHILE (l_cnt DECLARE 2 l_cnt PLS_INTEGER; 3 l_start_time PLS_INTEGER; 4 lob_loc CLOB; 5 data VARCHAR2(32767) := RPad('*', 6 32767, 7 '*'); 8 BEGIN 9 l_start_time := dbms_utility.get_time; 10 FOR idx IN 1 ..Moving upto 2,000 writeappends ops$tkyte@ORA920 exec runstats_pkg.rs_stop(10000); Run1 ran in 1258 hsecs Run2 ran in 706 hsecs run 1 ran in 178.19% of the time Name Run1 Run2 Diff STAT..block gets 19,044 29,575 10,531 global data 18,837 4,022 -14,815 LATCH.simulator hash latch 36,148 2,215 -33,933 STAT..size 15,471,664 15,910,000 438,336 STAT...consistent changes 1,741,844 11,075 -1,730,769 STAT..blocks consistent 1,741,335 10,557 -1,730,778 STAT...session logical reads 1,783,277 46,937 -1,736,340 STAT...consistent gets - exami 1,753,600 13,920 -1,739,680 STAT...consistent gets 1,765,441 18,570 -1,746,871 LATCH.cache buffers chains 5,349,496 147,523 -5,201,973 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 5,541,018 277,802 -5,263,216 1,994.59% PL/SQL procedure successfully completed. 100 11 LOOP 12 INSERT INTO lob_effect 13 VALUES 14 (idx, 15 empty_clob()) 16 RETURNING loc INTO lob_loc; 17 l_cnt := 0; 18 19 dbms_lob.Declaring this as a longliteral and using it as a parameter in a procedure gets around a 2000 character limit with casting: To SELECT from a BLOB/CLOB and have the output displayed as text, you have to cast the BLOB/CLOB field to VARCHAR2.The Ask TOM team is taking a break over the holiday season. pretend it is a file and you might be better off understanding it.THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS.IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

pl sql clob updating-33pl sql clob updating-63pl sql clob updating-32

Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.ops$tkyte@ORA920 exec runstats_pkg.rs_stop(10000); Run1 ran in 8925 hsecs Run2 ran in 1956 hsecs run 1 ran in 456.29% of the time Name Run1 Run2 Diff STAT..work - consistent re 2,478 18,398 15,920 STAT..block gets 64,682 96,602 31,920 STAT..block changes 136,403 84,374 -52,029 STAT..entries 114,133 44,234 -69,899 allocation 114,690 44,735 -69,955 LATCH.cache buffers lru chain 86,428 5,496 -80,932 STAT..buffer requested 85,405 4,074 -81,331 STAT...calls to kcmgas 86,058 4,712 -81,346 STAT...cleanout - number of kt 84,750 378 -84,372 STAT...active txn count during 84,743 351 -84,392 STAT...immediate (CR) block cl 84,729 312 -84,417 STAT...cleanouts and rollbacks 84,722 288 -84,434 STAT... Does this mean that what documentation says is not totally correct? OPEN(lob_loc, 20 DBMS_LOB.lob_readwrite); 21 22 WHILE (l_cnt DECLARE 2 l_cnt PLS_INTEGER; 3 l_start_time PLS_INTEGER; 4 lob_loc CLOB; 5 data VARCHAR2(32767) := RPad('*', 6 32767, 7 '*'); 8 BEGIN 9 l_start_time := dbms_utility.get_time; 10 FOR idx IN 1 ..CR blocks created 84,750 288 -84,462 global data 173,491 13,620 -159,871 LATCH.simulator hash latch 697,188 4,117 -693,071 STAT..size 48,304,392 46,655,964 -1,648,428 STAT...consistent changes 17,086,252 37,143 -17,049,109 STAT..blocks consistent 17,085,741 36,626 -17,049,115 STAT...consistent gets - exami 17,179,776 46,216 -17,133,560 STAT...session logical reads 17,337,689 169,999 -17,167,690 STAT...consistent gets 17,277,215 77,605 -17,199,610 LATCH.cache buffers chains 52,010,113 508,652 -51,501,461 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 53,376,618 865,837 -52,510,781 6,164.74% PL/SQL procedure successfully completed. 100 11 LOOP 12 INSERT INTO lob_effect 13 VALUES 14 (idx, 15 empty_clob()) 16 RETURNING loc INTO lob_loc; 17 l_cnt := 0; 18 WHILE (l_cnt DECLARE 2 l_cnt PLS_INTEGER; 3 l_start_time PLS_INTEGER; 4 lob_loc CLOB; 5 data VARCHAR2(32767) := RPad('*', 6 32767, 7 '*'); 8 BEGIN 9 l_start_time := dbms_utility.get_time; 10 FOR idx IN 1 .. 10 loop dbms_lob.write Append( l_clob, 32000, rpad( '*', 32000, '*' ) ); end loop; commit; end; / is an example? A clob is a lot like a file -- you can "seek" in it (dbms_lob.substr, dbms_lob.read), you can write to it (dbms_lob.write, writeappend) and so on. June June 27, 2002 - pm UTC declare l_clob clob; begin insert into t values ( empty_clob() ) returning clob_col into l_clob; for i in 1 ..

Leave a Reply