Modeling and Design, 7 of 21

Binds Greater Than 4,000 Bytes in INSERTs and UPDATEs

Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs

This release supports binds of more than 4,000 bytes of data for LOB INSERTs and UPDATEs. In previous releases this feature was allowed for LONG columns only. You can now bind the following for INSERT or UPDATE into a LOB column:

Up to 4GB data using OCIBindByPos(), OCIBindByName()
Up to 32,767 bytes data using PL/SQL binds

Since you can have multiple LOBs in a row, you can bind up to 4GB data for each one of those LOBs in the same INSERT or UPDATE statement. In other words, multiple binds of more than 4,000 bytes in size are allowed in a single statement.

Note:

The length of the default values you specify for LOBs still has the 4,000 byte restriction.

Ensure Your Temporary Tablespace is Large Enough!

The bind of more than 4,000 bytes of data to a LOB column uses space from temporary tablespace. Hence ensure that your temporary tablespace is large enough to hold at least the sum of all the bind lengths for LOBs.

If your temporary tablespace is extendable, it will be extended automatically after the existing space is fully consumed. Use the following statement:

CREATE TABLESPACE .. AUTOEXTEND ON ... TEMPORARY ..;

to create an extendable temporary tablespace.

Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion

Table Multimedia_tab is described in Appendix B, "The Multimedia Schema Used for Examples in This Manual". The following examples use an additional column called Comments. You will need to add the Comments column to table Multimedia_tab's CREATE TABLE syntax with the following line:

Comments LONG -- stores the comments of viewers on this clip

Oracle does not do any implicit conversion, such as HEX to RAW or RAW to HEX e.t.c., for data of more than 4000 bytes.

declare 
  charbuf varchar2(32767); 
  rawbuf  raw(32767); 
begin 
  charbuf := lpad ('a', 12000, 'a'); 
  rawbuf  := utl_raw.cast_to_raw(charbuf);

Table 7-4, "Binds of More Than 4,000 Bytes: Allowed INSERT and UPDATE Operations", outlines which INSERT operations are allowed in the above example and which are not. The same cases also apply to UPDATE operations.

Table 7-4 Binds of More Than 4,000 Bytes: Allowed INSERT and UPDATE Operations

Allowed INSERTs/UPDATEs ...	Non-Allowed INSERTs/UPDATEs ...
INSERT INTO Multimedia_tab (story, sound) VALUES (charbuf, rawbuf);	INSERT INTO Multimedia_tab(sound) VALUES(charbuf); This does not work because Oracle will not do implicit hex to raw conversion.
	INSERT INTO Multimedia_tab(story) VALUES (rawbuf); This does not work because Oracle will not do implicit hex to raw conversion.
	INSERT INTO Multimedia_tab(sound) VALUES( utl_raw.cast_to_raw(charbuf)); This does not work because Oracle cannot combine utl_raw.cast_to_raw() operator with binds of more than 4,000 bytes.

Allowed INSERTs/UPDATEs ...

Non-Allowed INSERTs/UPDATEs ...

INSERT INTO 
  Multimedia_tab (story, sound)
  VALUES (charbuf, rawbuf);

INSERT INTO 
  Multimedia_tab(sound)   
  VALUES(charbuf);

This does not work because Oracle will not do implicit hex to raw conversion.

INSERT INTO 
  Multimedia_tab(story)
  VALUES (rawbuf);

This does not work because Oracle will not do implicit hex to raw conversion.

INSERT INTO
  Multimedia_tab(sound)
  VALUES(
  utl_raw.cast_to_raw(charbuf));

This does not work because Oracle cannot combine utl_raw.cast_to_raw() operator with binds of more than 4,000 bytes.

4,000 Byte Limit On Results of SQL Operator

If you bind more than 4,000 bytes of data to a BLOB or a CLOB, and the data consists of an SQL operator, then Oracle limits the size of the result to at most 4,000 bytes.

The following statement inserts only 4,000 bytes because the result of LPAD is limited to 4,000 bytes:

INSERT INTO Multimedia_tab (story) VALUES (lpad('a', 5000, 'a'));

The following statement inserts only 2,000 bytes because the result of LPAD is limited to 4,000 bytes, and the implicit hex to raw conversion converts it to 2,000 bytes of RAW data:

INSERT INTO Multimedia_tab (sound) VALUES (lpad('a', 5000, 'a'));

Binds of More Than 4,000 Bytes: Restrictions

The following lists the restrictions for binds of more than 4,000 bytes:

If a table has both LONG and LOB columns then you can bind more than 4,000 bytes of data to either the LONG or LOB columns, but not both in the same statement.
You cannot bind data of any size to LOB attributes in ADTs. This restriction in prior releases still exists. For LOB attributes, first insert an empty LOB locator and then modify the contents of the LOB using OCILob* functions.
In an INSERT AS SELECT operation, binding of any length data to LOB columns is not allowed. This restriction in prior releases still exists.

Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE

CREATE TABLE  foo (a INTEGER ); 
DECLARE 
     bigtext     VARCHAR2(32767);  
     smalltext   VARCHAR2(2000); 
     bigraw      RAW (32767);
BEGIN  
     bigtext     := LPAD('a', 32767, 'a'); 
     smalltext   := LPAD('a', 2000, 'a'); 
     bigraw      := utlraw.cast_to_raw (bigtext);
 
/* The following is allowed: */ 
     INSERT INTO Multimedia_tab(clip_id, story, frame, comments)
         VALUES (1,bigtext, bigraw,smalltext);  
/* The following is allowed: */ 
     INSERT INTO Multimedia_tab (clip_id, story, comments) 
         VALUES (2,smalltext, bigtext);  
 
     bigtext    := LPAD('b', 32767, 'b'); 
     smalltext  := LPAD('b', 20, 'a'); 
     bigraw     := utlraw.cast_to_raw (bigtext);
 
/* The following is allowed: */ 
     UPDATE Multimedia_tab SET story = bigtext, frame = bigraw, 
         comments = smalltext; 

/* The following is allowed */ 
     UPDATE Multimedia_tab set story = smalltext, comments = bigtext; 
 
/* The following is NOT allowed because we are trying to insert more than 
        4000 bytes of data in a LONG and a LOB column: */ 
      INSERT INTO Multimedia_tab (clip_id, story, comments)
          VALUES (5, bigtext, bigtext);  

/* The following is NOT allowed because we are trying to insert
       data into LOB attribute */ 
     INSERT into Multimedia_tab (clip_id,map_obj) 
        VALUES (10,map_typ(NULL, NULL, NULL, NULL, NULL,bigtext, NULL));  

/* The following is not allowed because we try to perform INSERT AS
     SELECT data INTO LOB */ 
    INSERT INTO Multimedia_tab (story) AS SELECT bigtext FROM foo; 
 END;

Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported Because Hex to Raw/Raw to Hex Conversion is Not Supported

/*  Oracle does not do any implicit conversion (e.g., HEX to RAW or RAW to HEX 
    etc.) for data of more than 4000 bytes. Hence, the following cases will not 
    work :  */

  declare 
    charbuf   varchar2(32767); 
    rawbuf    raw(32767); 
  begin 
    charbuf := lpad ('a', 12000, 'a'); 
    rawbuf  := utl_raw.cast_to_raw(charbuf); 

/* The following is allowed ... */
   INSERT INTO Multimedia_tab (story, sound) VALUES (charbuf, rawbuf); 
   
/* The following is not allowed because Oracle won't do implicit
   hex to raw conversion. */
   INSERT INTO Multimedia_tab (sound) VALUES (charbuf);   
 
/* The following is not allowed because Oracle won't do implicit
   raw to hex conversion. */
   INSERT INTO Multimedia_tab (story) VALUES (rawbuf);   
 
/* The following is not allowed because we can't combine the
   utl_raw.cast_to_raw() operator with the bind of more than 4,000 bytes. */
   INSERT INTO Multimedia_tab (sound) VALUES (utl_raw.cast_to_raw(charbuf)); 
 
end; 
/

Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data Includes SQL Operator

If you bind more than 4,000 bytes of data to a BLOB or a CLOB, and the data actually consists of a SQL operator, then Oracle limits the size of the result to 4,000 bytes.

For example,  
/* The following command inserts only 4,000 bytes because the result of 
   LPAD is limited to 4,000 bytes */
   INSERT INTO Multimedia_tab (story) VALUES (lpad('a', 5000, 'a'));  

/* The following command inserts only 2,000 bytes because the result of 
   LPAD is limited to 4,000 bytes, and the implicit hex to raw conversion 
   converts it to 2,000 bytes of RAW data. */
   INSERT INTO Multimedia_tab (sound) VALUES (lpad('a', 5000, 'a'));

Example: C (OCI) - Binds of More than 4,000 Bytes For INSERT and UPDATE

CREATE TABLE foo( a INTEGER ); 
void insert()         /* A function in an OCI program */ 
{ 
/* The following is allowed */ 
   ub1 buffer[8000]; 
   text *insert_sql = "INSERT INTO Multimedia_tab(story, frame, comments)  
              VALUES (:1, :2, :3)"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
              SQLT_LBI, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* The following is allowed */ 
   ub1 buffer[8000]; 
   text *insert_sql = "INSERT INTO Multimedia_tab (story,comments)  
              VALUES (:1, :2)"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* The following is allowed, no matter how many rows it updates */ 
   ub1 buffer[8000]; 
   text *insert_sql = (text *)"UPDATE Multimedia_tab SET  
              story = :1, sound=:2, comments=:3"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
              (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
              SQLT_LBI, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000,  
              SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);  
} 
 
void insert() 
{ 
/* The following is allowed, no matter how many rows it updates */ 
   ub1 buffer[8000]; 
   text *insert_sql = (text *)"UPDATE Multimedia_tab SET  
              story = :1, sound=:2, comments=:3"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
             (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 2000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 8000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* Piecewise, callback and array insert/update operations similar to  
   the allowed regular insert/update operations are also allowed */ 
} 
 
void insert() 
{ 
/* The following is NOT allowed because we try to insert >4000 bytes 
   to both LOB and LONG columns */ 
ub1 buffer[8000]; 
text *insert_sql = (text *)"INSERT INTO Multimedia_tab (story, comments)  
             VALUES (:1, :2)"; 
OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
            (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* The following is NOT allowed because we try to insert data into 
   LOB attributes */ 
   ub1 buffer[8000]; 
   text *insert_sql = (text *)"INSERT INTO Multimedia_tab (map_obj)  
             VALUES (map_typ(NULL, NULL, NULL, NULL, NULL,:1, NULL))"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),  
            (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000,  
             SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* The following is NOT allowed because we try to do insert as  
   select character data into LOB column */ 
   ub1 buffer[8000]; 
   text *insert_sql = (text *)"INSERT INTO Multimedia_tab (story) 
            SELECT :1 from FOO"; 
   OCIStmtPrepare(stmthp, errhp, insert_sql,strlen((char*)insert_sql),  
            (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); 
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000,  
            SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); 
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); 
} 
 
void insert() 
{ 
/* Other update operations similar to the disallowed insert operations are also  
   not allowed. Piecewise and callback insert/update operations similar to the 
   disallowed regular insert/update operations are also not allowed  */ 
}