Release 2 (9.2)
Part Number A96591-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 2 (9.2) Part Number A96591-01 |
|
This chapter includes the following Frequently Asked Questions (FAQs):
Can I insert or update any length of data for a LOB column? Am I still restricted to 4K. How about LOB attributes
When inserting or updating a LOB column you are now not restricted to 4K.
For LOB attributes, you must use the following two steps:
Example: Copy Long to LOB Using SQL :
INSERT INTO Multimedia_tab (clip_id,sound) SELECT id, TO_LOB(SoundEffects)
Does this work if the data in LONG or LONGRAW is > 64K?
Yes. All data in the LONG is copied to the LOB regardless of size.
The project that I'm working on requires a trigger on a LOB column. The requirement is that when this column is updated, we want to check some conditions. How do I check whether there is any value in the NEW for this LOB column? Null does not work, since you can't compare BLOB with NULL.
You can use the UPDATING clause inside of the trigger to find out if the LOB column is being updated or not.
CREATE OR REPLACE TRIGGER...... ... IF UPDATING('lobcol') THEN .....
...
Note: The preceding works only for top-level LOB columns.
I read in one of the prior release Application Developer's Guides the following:
"When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4Gb regardless of the starting offset and the amount of data in the LOB. You do need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read. "
And again, under the DBMS_LOB.LOADFROMFILE() procedure...
"It is not an error to specify an amount that exceeds the length of the data in the source BFILE. Thus, you can specify a large amount to copy from the BFILE which will copy data from the src_offset to the end of the BFILE. "
However, the following code...
declare cursor c is select id, text from bfiles; v_clob clob; begin for j in c loop Dbms_Lob.FileOpen ( j.text, Dbms_Lob.File_Readonly ); insert into clobs ( id, text ) values ( j.id, empty_clob() ) returning text into v_clob; Dbms_Lob.LoadFromFile ( dest_lob => v_clob, src_lob => j.text, amount => 4294967296, /* = 4Gb */ dest_offset => 1, src_offset => 1 ); Dbms_Lob.FileClose ( j.text ); end loop; commit; end; /
causes the following error message:
ORA-21560: argument 3 is null, invalid, or out of range
Reducing the amount by 1 to 4294967295 causes the following error message:
ORA-22993: specified input amount is greater than actual source amount
Please help me understand why I am getting errors.
amount more than the size of the BFILE. So the code example you gave returns an error.amount can be larger than the size of the data. But then, since PL/SQL limits the size of the buffer to 32K, and given the fact that the amount should be no larger than the size of the buffer, the amount is restricted to 32K.Please note that in PL/SQL, if the amount is larger than the buffer size, it returns an error. In any case, the amount cannot exceed 4Gig-1 because that is the limit of a ub4 variable.
amount larger than the length of the BFILE in OCILobLoadFromFile. However, in OCILobRead, you can specify amount=4Gig-1, and it will read to the end of the LOB.We have a table with BLOB columns. We use NOLOGGING to be fast. It means that the BLOB chunks are not saved in redologs. What happens if the server crashes? At recovery, is the table data lost or is the table corrupted?
Any LOB data not written to the datafiles cannot be recovered from redo logs; it must be reloaded. Because the LOB index updates are written to redo, the index will indicate that the LOB exists, but the LOB will not exist (it was not recovered from redo), so my guess is that you will get a data corruption error.
Is inline storage allowed for LOBs in index-organized tables?
For LOBs in index organized tables, inline LOB storage is allowed only if the table is created with an overflow segment.
When must I use EMPTY_BLOB() and EMBPTY_CLOB()? I always thought it was mandatory for each insert of a CLOB or BLOB to initialize the LOB locator first with either EMPTY_CLOB() or EMPTY_BLOB().
In Oracle8i release 8.1.5, you can initialize a LOB with data using the INSERT statement as long as the data is <4K. This is why your insert statement worked. Note that you can also update a LOB with data that is <4K using the UPDATE statement. If the LOB is larger than 4K perform the following steps:
Note the following:
From java we want to insert a complete object with a BLOB attribute into an Oracle8.1.5 object table. The problem is - in order to do that - we have somehow to initialize the blob attribute with EMPTY_BLOB(). Is there any way to initialize the BLOB attribute with EMPTY_BLOB() in java? What I am doing at the moment is:
First I insert the object with null in the BLOB attribute. Afterwards I update the object with an EMPTY_BLOB(), then select it again, get the BLOB locator and finally write my BLOB.
Is this the only way it works ? Is there a way to initialize the BLOB directly in my toDatum method of the Custom Datum interface implementation?
Here is the SQLJ equivalent...
BLOB myblob = null; #sql { select empty_blob() into :myblob from dual } ;
and use myblob in your code wherever the BLOB needed to be initialized to null.
See also the question and answer under the section, "JDBC, JPublisher and LOBs", "How Do I setData to EMPTY_BLOB() Using JPublisher?"
Is it possible to insert a row with an empty LOB locator into a table using JDBC?
You can use the EMPTY_BLOB() in JDBC also.
Statement stmt = conn.createStatement() ; try { stmt.execute ("insert into lobtable values (empty_blob())"); } catch{ ...}
Another example is:
stmt.execute ("drop table lobtran_table"); stmt.execute ("create table lobtran_table (b1 blob, b2 blob, c1 clob, c2 clob, f1 bfile, f2 bfile)"); stmt.execute ("insert into lobtran_table values ('010101010101010101010101010101', empty_blob(), 'onetwothreefour', empty_clob(), bfilename('TEST_DIR','tkpjobLOB11.dat'), bfilename ('TEST_DIR','tkpjobLOB12.dat'))");
How do I setData to EMPTY_BLOB() Using JPublisher? Is there something like EMPTY_BLOB() and EMPTY_CLOB() in a Java statement, not a SQL statement processed by JDBC? How do we setData to an EMPTY_BLOB() using JPublisher?
One way to build an empty LOB in JPublisher would be as follows:
BLOB b1 = new BLOB(conn, null) ;
You can use b1 in set method for data column.
Do OracleBlob and OracleClob work in 8.1.x?
OracleBlob and OracleClob were Oracle specific functions used in JDBC 8.0.x drivers to access LOB data. In Oracle8i version 8.1.x (and subsequent releases such as Oracle 9i and higher), OracleBlob and OracleClob are deprecated.
If you use OracleBlob or OracleClob to access LOB data, you will receive the following typical error message, for example, when attempting to manipulate LOBs with JDBC Thin Driver :
"Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or representation etc."
See Oracle9i JDBC Developer's Guide and Reference for a description of these non-supported functions and alternative and improved JDBC methods.
Has anyone come across the following error when attempting to manipulate LOBs with the 8.1.5 JDBC Thin Driver:
Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or representation at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:181) at oracle.jdbc.ttc7.Odscrarr.receive(Compiled Code) at oracle.jdbc.ttc7.TTC7Protocol.describe(Compiled Code) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java: 516) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1002) at oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1163) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme nt.java:1211) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java: 201) at LobExample.main(Compiled Code)
---------------------------------
The code I'm using is the LobExample.java shipped with 8.0.5. This sample was initially and OCI8 sample. (I am using the Thin Driver and instance from a higher release.)
You are using a wrong sample. OracleBlob and OracleClob have been deprecated and they no longer work.
I am running a Java stored procedure that writes a CLOB and am getting an exception as follows:
ORA-22920: row containing the LOB value is not locked
ORA-06512: at "SYS.DBMS_LOB", line 708
ORA-06512: at line 1
Once I added a 'FOR UPDATE' clause to my SELECT statement, this exception did not occur.
I feel that the JDBC Developer's Guide and Reference(8.1.5) should be updated to reflect the need for the 'FOR UPDATE' clause on the SELECT.
This is not a JDBC issue in specific. This is how LOBs work! This got manifested in the JSP because by default autoCommit is false. You would also see the same exception when autoCommit is set to false on the client side. You didn't see the exception when used with 'For Update' because locks are acquired explicitly.
What is DBMS_LOB.ERASE doing?
It's just "clearing" a segment of the CLOB. It does *not* shorten the CLOB. So the length of the CLOB is the same before and after the ERASE. You can use DBMS_LOB.TRIM to make a CLOB shorter.
Can I use oracle.sql.CLOB.putChars()?
Yes, you can, but you have to make sure that the position and length arguments are correct. You can also use the recommended OutputStream interface which in turn will call putChars for you.
OCI provides function to manipulate a CLOB CharSetId. What is the JDBC equivalent?
In JDBC CLOBs are *always* in USC2, which is the Oracle character set corresponding to the Java "char" type. So there is no equivalent for the OCI CLOB CharSetId.
Why is writing into BLOBs slower than inserting into LONG RAWs?
It is true that inserting data in BLOBs with JDBC Thin is slower as it still uses the DBMS_LOB package. With JDBC OCI the inserts are faster as native LOB APIs are used.
Why am I getting an ORA-03127 error when getting the LobLength on a LONG column?
This is the correct behavior. LONG columns are not 'fetched' in-place (aka in-row). They are fetched out of place and exists in the pipe until you read them explicitly. In this case, we got the LobLocator (getBlob()) and then we are trying to get the length of this LOB before we read the LONG column. Since the pipe is not clear we are getting the preceding exception. The solution would be to complete reading the LONG column before you do any operation on the BLOB.
Here is what I'm trying to do with CLOBs through JDBC:
I was looking at the method SQLUtil.makeOracleDatum(), but that doesn't work. I get an invalid type error message. The only Oracle examples I've seen have the CLOB object created by reading it in from Oracle through a SQL object. I need to create the CLOB in the Java program.
This cannot be done as you describe here. The oracle.sql.CLOB class encapsulates a CLOB locator, not the actual data for the CLOB, and the CLOB locator must come from the database. There is no way currently to construct a CLOB locator in the client. You need to insert an empty_clob() into the table, retrieve the locator, and then write the data to the CLOB.
PLSQL procedures can be poor vehicles for this particular functionality.
If you make the PLSQL parameter of the CLOB type, it represents the CLOB locator and you still have to use some other interface to write the data to the CLOB. And, passing all the data to PLSQL as a VARCHAR2 or LONG parameter is also a problem because PLSQL parameters are limited to 32K, which is rarely enough to be practically useful in this context.
I would recommend just using the standard JDBC API's for dealing with the CLOB.
You need to encapsulate the entire functionality required to insert a CLOB, in a single stored procedure invoked from a client applicatiLoading LOBs and Data Into LOBs.
How do I insert a file of 1MB which is stored on disk, into a CLOB column of my table. I thought DBMS_LOB.LOADFROMFILE should do the trick, but, the document says it is valid for BFILE only. How do I do this?
You can use SQL*Loader. See Oracle9i Database Utilities or in this manual, Chapter 4, "Managing LOBs", Using SQL*Loader to Load LOBs on .
You can use loadfromfile() to load data into a CLOB, but the data is transferred from the BFILE as raw data--no character set conversions are performed. It is up to you to do the character set conversions yourself before calling loadfromfile().
Use OCILobWrite() with a callback. The callback can read from the operating system (OS) file and convert the data to the database character set (if it's different than the OS file's character set) and then write the data to the CLOB.
We are facing a performance problem concerning BLOBs and CLOBs. Much time is consumed when loading data into the BLOB or CLOB using JDBC Driver.
It's true that inserting data into LOBs using JDBC Thin driver is slower as it still uses the DBMS_LOB package and this adds the overhead of a full JDBC CallableStatement execution for each LOB operation.
With the JDBC OCI and JDBC server-side internal drivers, the inserts are faster because native LOB APIs are used. There is no extra overhead from JDBC driver implementation.
It's recommended that you use InputStream and OutputStream for accessing and manipulating LOB data. By using streaming access of LOBs, JDBC driver will handle the buffering of the LOB data properly to reduce the number of network round-trips and ensure that each database operation uses a data size as a multiple of the LOB's natural chunk size.
Here is an example that uses OutputStream to write data to a BLOB:
/*
* This sample writes the GIF file john.gif to a BLOB.
*/
import java.sql.*; import java.io.*; import java.util.*; // Importing the Oracle Jdbc driver package makes the code more readable import oracle.jdbc.driver.*; //needed for new CLOB and BLOB classes import oracle.sql.*; public class LobExample { public static void main (String args []) throws Exception { // Register the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when auto commit is off conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); try { stmt.execute ("drop table persons"); } catch (SQLException e) { // An exception could be raised here if the table did not exist already. } // Create a table containing a BLOB and a CLOB stmt.execute ("create table persons (name varchar2 (30), picture blob)"); // Populate the table stmt.execute ("insert into persons values ('John', EMPTY_BLOB())"); // Select the BLOB ResultSet rset = stmt.executeQuery ("select picture from persons where name = 'John'"); if (rset.next ()) { // Get the BLOB locator from the table BLOB blob = ((OracleResultSet)rset).getBLOB (1); // Declare a file handler for the john.gif file File binaryFile = new File ("john.gif"); // Create a FileInputStream object to read the contents of the GIF file FileInputStream istream = new FileInputStream (binaryFile); // Create an OutputStram object to write the BLOB as a stream OutputStream ostream = blob.getBinaryOutputStream (); // Create a tempory buffer byte[] buffer = new byte[1024]; int length = 0; // Use the read() method to read the GIF file to the byte // array buffer, then use the write() method to write it to // the BLOB. while ((length = istream.read(buffer)) != -1) ostream.write(buffer, 0, length); // Close the inputstream and outputstream istream.close(); ostream.close(); // Check the BLOB size System.out.println ("Number of bytes written = "+blob.length()); } // Close all resources rset.close(); stmt.close(); conn.close(); } }
Note that you'll get even better performance if you use DBMS_LOB.LOADFROMFILE() instead of using DBMS_LOB.WRITE().
In order to be able to use DBMS_LOB.LOADFROMFILE(), the data to be written into the LOB must be in a server-side file.
Is the LOB index created for the LOB in the same tablespace as the LOB data?
The LOB index is created on the LOB column and it indexes the LOB data. The LOB index resides in the same tablespace as the locator.
The promotion column could be defined and indexed as a BFILE, but if for example, a row is DELETEd, the Word document is removed with it when the promotion column is defined as BLOB, but it is not removed when the column is defined as a BFILE. Why?
We don't create an index for BFILE data. Also note that internal persistent LOBs are automatically backed up with the database whereas external BFILEs are not and modifications to the internal persistent LOB can be placed in the redo log for future recovery.
Which views can I query to find out about a LOB index?
For temporary LOBs, the LOB index information can be retrieved from the view, V$SORT_USAGE.
For example:
SELECT USER#, USERNAME, SEGTYPE, EXTENTS, BLOCKS FROM v$sort_usage, v$session WHERE SERIAL#=SESSION_NUM;
What happens if I specify a LOB TABLESPACE, but also say ENABLE STORAGE IN ROW?
If the length of the LOB value is less than approximately 4K, then the data is stored inline in the table. When it grows to beyond approximately 4K, then the LOB value is moved to the specified tablespace.
I am looking for information on the pros and cons of storing images in a BFILE versus a BLOB.
Here's some basic information.
Should DISABLE STORAGE IN ROW always be specified if many UPDATEs, or SELECTs including full table scans are anticipated?
Use DISABLE STORAGE IN ROW if the other table data will be updated or selected frequently, not if the LOB data is updated or selected frequently.
If I specify a segment and tablespace for the BLOB, and specify ENABLE STORAGE IN ROW then look in USER_LOBS, I see that the BLOB is defined as IN_ROW and it shows that it has a segment specified. What does this mean? That all BLOBs 4K and under will go into the same segment as the table data, but the ones larger than that go into the segment I specified?
Yes.
Oracle9i SQL Reference, states the following:
"ENABLE STORAGE IN ROW--specifies that the LOB value is stored in the row (inline) if its length is less than approximately 4K bytes minus system control information. This is the default. "
If an inline LOB is > 4K, which of the following possibilities is true?
It sounds to me like #2, but I need to check.
You are correct -- it's number 2. Some meta information is stored inline in the row so that accessing the LOB value is faster. However, the entire LOB value is stored elsewhere once it grows beyond approximately 4K bytes.
INSERT INTO blob_table (key, blob_column) VALUES (1, null);
In this case I expect that you do not use any space, like any other NULL value, as we do not have any pointer to a BLOB value at all.
INSERT INTO blob_table (key, blob_column) VALUES (1, empty_blob());
In this case you would be right, that we need at least a chunk size of space.
We distinguish between when we use BLOBs between NULL values and empty strings.
If a LOB column is EMPTY_CLOB() or EMPTY_BLOB() instead of NULL, how is the LOB locator stored in the row and are extra data blocks used for this?
See also Chapter 7, "Modeling and Design", in this manual, under "LOB Storage".
You can run a simple test that creates a table with a LOB column with attribute DISABLE STORAGE IN ROW. Insert thousands of rows with NULL LOBs.
Note that Oracle does not consume thousands of chunks to store NULLs!
I have questions about storing CLOBs inline outside the row. We know when you create a table with LOB column, you can specify DISABLE STORAGE IN ROW or ENABLE STORAGE IN ROW. My questions are:
What are the effects of providing or not providing a LOB storage clause when creating a table containing a Varray column? The documentation suggest that Varrays will be stored inline or in a LOB depending on their size, so I assume this would be the case even if no LOB storage clause were provided? Does providing one imply that a LOB will always be used?
I assume LOB are named for a reason. It is not clear to me what use the names might be. I understand that it is convenient to name the nested table storage table because you may want to index it, alter it, and so on. But what can I do with the LOB? The only example I found was one that modifies the LOB to cache it?
The documentation says: "Varrays are stored in columns either as raw values or BLOBs. Oracle decides how to store the varray when the varray is defined, based on the maximum possible size of the varray computed using the LIMIT of the declared varray. If the size exceeds approximately 4000 bytes, then the varray is stored in BLOBs. Otherwise, the varray is stored in the column itself as a raw value. In addition, Oracle supports inline LOBs; therefore, elements that fit in the first 4000 bytes of a large varray (with some bytes reserved for the LOB locator) are stored in the column of the row itself."
So, your data will be inline as raw data if you have less than about 4000 bytes and you do NOT specify a LOB storage clause for your varray.
The documentation (Oracle9i SQL Reference ) also says:
"varray_storage_clause: lets you specify separate storage characteristics for the LOB in which a varray will be stored. In addition, if you specify this clause, Oracle will always store the varray in a LOB, even if it is small enough to be stored inline."
So, if you do specify this varray_storage_clause, then you will always be storing your varrays in LOBs. However, according to the first paragraph, varrays also support inline LOBs, so by default your first 4000 bytes or so will still be stored inline in the table's row with the other data as an inline LOB. It will also have some extra LOB overhead.
To clarify, if you specify varray store as LOB, and the column you've defined has a max size less than 4000 bytes, then it will be stored as an inline LOB. Here's the whole synopsis:
Calculate MAX possible size of your column, remember that there is some overhead involved so if you have 10 elements of size 1000 the MAX size is still a little bit greater than 10*1000.
Our current table consists of records with 3 fields - a sequence, a redundancy check number, and a long raw field. The size of the long raw field is around 100KB but it can be as big as 300KB. The entire file is 160GB and the server's maximum size is 200GB. We converted this database from 7.3.4 to Oracle9i and now our application programs do not work well with the LONG raw fields. We want to convert them to BLOBs. We cannot have the application down while we migrate to BLOBs. What suggestions do you have?
Oracle9i allows you to use ALTER TABLE in order to copy the data from a LONG to a LOB. See Chapter 8, "Migrating From LONGs to LOBs". But the ALTER TABLE command would make the table unusable for the duration of the ALTER.
Another way to do this is to use the TO_LOB operator introduced in Oracle 8i to copy data from the LONG to the LOB. You can take a look at Oracle9i Database Migration, Chapter 8 -- Copy LONGs to LOBs. In this case, the table will be unusable for a much shorter duration of time.
There are no implicit LOB conversions between different LOB types? For example, in PL/SQL, I cannot use:
INSERT INTO t VALUES ('abc'); WHERE t CONTAINS a CLOB column.....
Do you know if this restriction still exists in Oracle8i? I know that this restriction existed in PL/SQL for Oracle8 but users could issue the INSERT statement in SQL as long as data to insert was <4K. My understanding is that this <4K restriction has now been removed in SQL.
The PL/SQL restriction is removed. In Oracle8i and higher, you can insert more than 4K worth of data.
We were experiencing a load time of 70+ seconds when attempting to populate a BLOB column in the database with 250MB of video content. Compared to the 15 seconds transfer time using the UNIX copy, this seemed unacceptable. What can we do to improve this situation?
The BLOB was being stored in partitioned tablespace and NOLOGGING, NOCACHE options were specified to maximize performance.
The INITIAL and NEXT extents for the partition tablespace and partition storage were defined as 300M, with MINEXTENTS set to 1 in order to incur minimal overhead when loading the data.
CHUNK size was set to 32768 bytes - maximum for Oracle.
INIT.ORA parameters for db_block_buffers were increased as well as decreased.
All the preceding did very little to affect the load time - this stayed consistently around the 70-75 seconds range suggesting that there was minimal effect with these settings.
First examine the I/O storage devices and paths.
4 SUN AS5200 disk arrays were being used for data storage--the devices where the BLOB was to be written to. Disks on this array were RAID (0+1) with 4 stripes of (9+9). Veritas VxFS 3.2.1 was the file system on all disks.
In order to measure the effect of using a different device, the tablespace for the BLOB was defined on /tmp. /tmp is the swap space.
Needless to say, loading the BLOB now only took 14 seconds, implying a data transfer rate of 1.07GIG per minute - a performance rating as close, if not higher than the UNIX copy!
This prompted a closer examination of what was happening when the BLOB was being loaded to a tablespace on the disk arrays. SAR output indicated significant waits for I/O, gobbling up of memory, high CPU cycles and yes, the ever-consistent load time of 70 seconds. Any suggestions on how to resolve this?
Obviously, there seems to be an issue with Veritas, UNIX, and Oracle operating together. I have come up with supporting documentation on this. For acceptable performance with Veritas file-system on your disk arrays with Oracle, we recommend that you install the Veritas QuickIO option.
Typically when customers complain that writing LOBs is slow, the problem is usually not how Oracle writes LOBs. In the preceding case, you were using Veritas File System, which uses UNIX file caching, so performance was very poor.
After disabling UNIX caching, performance should improve over that with the native file copy.
Is there a difference in performance when using DBMS_LOB.SUBSTR compared with DBMS_LOB.READ?
DBMS_LOB.SUBSTR is there because it's a function and you can use it in a SQL statement. There is no performance difference.
I was wondering if anyone had any white papers or guidelines on tuning LOB performance.
Chapter 9, "LOBS: Best Practices" in this manual, discusses LOB performance issues. Also see "Selecting a Table Architecture" in Chapter 7, "Modeling and Design".
When should I use chunks over reading the whole thing?
If you intend to read more than one chunk of the LOB, then use OCILobRead with the streaming mechanism using either polling or a callback. If you only need to read a small part of the LOB that will fit in one chunk, then only read that chunk. Reading more will incur extra network overhead.
Is inlining the LOB a good idea. If so, then when?
Inlining the LOB is the default and is recommended most of the time. Oracle9i and higher stores the LOB inline if the value is less than approximately 4K thus providing better performance than storing the value out of line. Once the LOB grows larger than 4K, the LOB value is moved into a different storage segment but meta information that allows quick lookup of the LOB value is still stored inline. So, inlining provides the best performance most of the time.
However, you probably don't want to inline the LOB if you'll be doing a lot of base table processing such as full table scans, multi-row accesses (range scans) or many updates/selects of columns other than the LOB columns.
How can I store LOBs that are >4Gb in the database?
Your alternatives for storing >4Gb LOBs are:
We have a nasty performance problem that I have isolated to the creation of temporary LOBs in a called routine. The following procedures demonstrate the problem.
SQL> set serveroutput on size 20000; SQL> execute runlob(FALSE); Start time (seconds): 52089 End time (seconds): 52089 PL/SQL procedure successfully completed. SQL> execute runlob(TRUE); Start time (seconds): 52102 End time (seconds): 52131 PL/SQL procedure successfully completed.
This is really killing performance of DDL creation in our API. Any ideas what's happening here?
CREATE OR REPLACE PROCEDURE lob(createlob BOOLEAN) IS doc CLOB; BEGIN IF createlob THEN DBMS_LOB.CREATETEMPORARY(doc, TRUE); DBMS_LOB.FREETEMPORARY(doc); END IF; END; / CREATE OR REPLACE PROCEDURE RUNLOB(createlob BOOLEAN DEFAULT FALSE) AS doc CLOB; BEGIN dbms_output.put_line('Start time (seconds): '||to_char(sysdate,'SSSSS')); FOR i IN 1..400 LOOP DBMS_LOB.CREATETEMPORARY(doc, TRUE); lob(createlob); DBMS_LOB.FREETEMPORARY(doc); END LOOP; dbms_output.put_line('End time (seconds): '||to_char(sysdate,'SSSSS')); END; /
In your test case, the difference between creating temporary LOBs in RUNLOB() and in LOB() is that:
kdlt_add_dso_link() is an expensive operation compared to the rest of the temporary LOB creation cycles in kdlt? The overhead is from (de)allocating a DSO for LOB(). kdlt_add_dso_link() needs to allocate a new DSO, for its associated memory allocation and control structures initialization. The extra code path accounts for the cost.
To avoid new DSO creation, can you use the workaround of a package variable tmplob locator in LOB() instead of a local one? Please try the following modified script. The performance hit is no longer there with this script.
create or replace package pk is tmplob clob; end pk; / CREATE OR REPLACE PROCEDURE lob(createlob BOOLEAN) IS doc CLOB; BEGIN IF createlob THEN DBMS_LOB.CREATETEMPORARY(pk.tmplob, TRUE); DBMS_LOB.FREETEMPORARY(pk.tmplob); null; END IF; END; / CREATE OR REPLACE PROCEDURE RUNLOB(createlob BOOLEAN DEFAULT FALSE) AS doc CLOB; BEGIN dbms_output.put_line('Start time (seconds): '||to_char(sysdate,'SSSSS')); FOR i IN 1..400 LOOP DBMS_LOB.CREATETEMPORARY(doc, TRUE); lob(createlob); DBMS_LOB.FREETEMPORARY(doc); END LOOP; dbms_output.put_line('End time (seconds): '||to_char(sysdate,'SSSSS')); END; /
Thank you. We should be able to use package-scoped temporary LOBs almost everywhere we currently have function-local LOBs.
What is "UPLOAD_AS_BLOB"?
UPLOAD_AS_BLOB is an attribute of Database Access Descriptor (DAD) which is used for uploading documents into BLOB type table column using PL/SQL web gateway interface.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|