The outputstring procedure is the oracle procedure interface to the External procedure.(src_file); EXCEPTION WHEN access_error THEN WHEN invalid_argval THEN WHEN invalid_directory THEN WHEN no_data_found THEN WHEN noexist_directory THEN WHEN nopriv_directory THEN WHEN open_toomany THEN WHEN operation_failed THEN WHEN unopened_file THEN WHEN others THEN END read_file; /(l_clob, rpad ('*',4000,'*')); END LOOP; END LOOP; END; / CREATE TEMPORARY TABLESPACE don_1024 TEMPFILE 'c: emp\don_1024.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024k; CREATE TEMPORARY TABLESPACE don_512 TEMPFILE 'c: emp\don_512.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512k; CREATE TEMPORARY TABLESPACE don_64 TEMPFILE 'c: emp\don_64.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64k; SELECT tablespace_name, initial_extent FROM dba_tablespaces WHERE tablespace_name LIKE ('DON%'); TABLESPACE_NAME INITIAL_EXTENT --------------- -------------- DON_1024 1048576 DON_512 524288 DON_64 65536 ALTER USER uwclass TEMPORARY TABLESPACE don_1024; SELECT tablespace, segtype, blocks*8*1024 USED_BYTES FROM gv$tempseg_usage WHERE username = user; TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_1024 LOB_DATA 1048576 DON_1024 LOB_INDEX 1048576 ALTER USER dellera TEMPORARY TABLESPACE don_512; (logout then in again) TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_512 LOB_DATA 524288 DON_512 LOB_INDEX 524288 ALTER USER dellera TEMPORARY TABLESPACE don_64; (logout then in again) TABLESPACE SEGTYPE USED_BYTES ---------- ----------- ---------- DON_64 LOB_DATA 327680 DON_64 LOB_INDEX 65536 So by reducing the extent size we greatly reduce the space allocated to the temp lob_index.I don't know why the lob_data that should contain 64000 bytes stays to 327,680 for an extent size of 64K.
The SQL code equivalent is (from here): and check if it’s for a duplicate entry (my identifier field has a primary key — unique — constraint).
I’ve got a table with a single CLOB column holding a single row.
In older Java projects I've been writing and reading CLOBS and I know you have to handle those fields a bit different than normal VARCHARs and such. NET Dynamic Help and whatever I could find on the Internet, and the funny thing is I found three different ways of doing it; 2 really simple and one not so simple.
The weird thing is that I don't think the first two options is supposed to be working - but when I test them they work just fine. To test this, I have created a simple table in Oracle, which consists of 2 fields; ID (INT) and TEXT (CLOB). I'm reading a simple text-file created with Notepad, which is 499 KB in size.
Oracle maintains an index keyed by (LOB_ID, chunk_number) *** pointing to all the chunks of a LOB in order, so when you update a single chunk Oracle simply creates an updated copy of the chunk and changes the appropriate index entry to point to the new chunk.
So here’s an image representing our one LOB value just after we’ve created it and before we’ve updated: And then we “modify” the first chunk – which means we have to add a chunk (which in this case is a single block) to the segment, create a new version of the first chunk, modify the index to point to the new block, and add an index entr – keyed by time-stamp – to the end of the index to point to the old chunk; something like this: Now, when we run a query to select the LOB, Oracle will follow the index entries in order and pick up the new chunk from the end of the LOBSEGMENT.
(This can be nicely solved with a smart data access component that caches the parameters which is what I'm doing in my own data access component.) It has been a long time (I'm glad for that) since I had to work against Oracle (actually I haven't done it since .
NET came around) so I can't say anything about which of them to use with any Oracle perspective.
In one of our current projects we need to write large chunks of text to an Oracle CLOB field from an ASP. NET even though the later versions of that pack may work very well.
Oracle Client libraries so far (to write smaller amounts of data) and it has worked out quite well for us.
It’s common knowledge (to users of LOBs) that the undo mechanism Oracle has for LOBs is simply to leave the old LOB in place and create a new one – so the intial response to the question might be to guess that the LOBSEGMENT will grow to roughly double the size.