This section describes the implicit conversion process in PL/SQL from one LOB type to another LOB type or from a LOB type to a non-LOB type.
Most of the in the following sections use
print_media table. Following
is the structure of
Figure 6-1 print_media table
6.1.1 Implicit Conversion Between CLOB and NCLOB Data Types in SQL
This section describes support for implicit conversions between CLOB and NCLOB data types.
The database enables you to perform operations such as cross-type assignment and cross-type parameter passing between
NCLOB data types. The database performs implicit conversions between these types when necessary to preserve properties such as character set formatting.
Note that, when implicit conversions occur, each character in the source LOB is changed to the character set of the destination LOB, if needed. In this situation, some degradation of performance may occur if the data size is large. When the character set of the destination and the source are the same, there is no degradation of performance.
After an implicit conversion between
NCLOB types, the destination LOB is implicitly created as a temporary LOB. This new temporary LOB is independent from the source LOB. If the implicit conversion occurs as part of a define operation in a
SELECT statement, then any modifications to the destination LOB do not affect the persistent LOB in the table that the LOB was selected from as shown in the following example:
SQL> -- check lob length before update SQL> SELECT DBMS_LOB.GETLENGTH(ad_sourcetext) FROM Print_media 2 WHERE product_id=3106 AND ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 205 SQL> SQL> DECLARE 2 clob1 CLOB; 3 amt NUMBER:=10; 4 BEGIN 5 -- select a clob column into a clob, no implicit convesion 6 SELECT ad_sourcetext INTO clob1 FROM Print_media 7 WHERE product_id=3106 and ad_id=13001 FOR UPDATE; 8 -- Trim the selected lob to 10 bytes 9 DBMS_LOB.TRIM(clob1, amt); 10 END; 11 / PL/SQL procedure successfully completed. SQL> -- Modification is performed on clob1 which points to the SQL> -- clob column in the table SQL> SELECT DBMS_LOB.GETLENGTH(ad_sourcetext) FROM Print_media 2 WHERE product_id=3106 AND ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 10 SQL> SQL> ROLLBACK; Rollback complete. SQL> -- check lob length before update SQL> SELECT DBMS_LOB.GETLENGTH(ad_sourcetext) FROM Print_media 2 WHERE product_id=3106 AND ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 205 SQL> SQL> DECLARE 2 nclob1 NCLOB; 3 amt NUMBER:=10; 4 BEGIN 5 6 -- select a clob column into a nclob, implicit conversion occurs 7 SELECT ad_sourcetext INTO nclob1 FROM Print_media 8 WHERE product_id=3106 AND ad_id=13001 FOR UPDATE; 9 10 DBMS_LOB.TRIM(nclob1, amt); -- Trim the selected lob to 10 bytes 11 END; 12 / PL/SQL procedure successfully completed. SQL> -- Modification to nclob1 does not affect the clob in the table, SQL> -- because nclob1 is a independent temporary LOB SQL> SELECT DBMS_LOB.GETLENGTH(ad_sourcetext) FROM Print_media 2 WHERE product_id=3106 AND ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 205
See Also:Oracle Database SQL Language Reference for details on implicit conversions supported for all data types.
6.1.2 Implicit Conversions Between CLOB and VARCHAR2
This section describes support for implicit conversions between
VARCHAR2 data types.
CLOBdata types are supported in PL/SQL.
See Also:SQL Semantics for LOBs for details on LOB support in SQL statements.
Note:While this section uses
VARCHAR2data type as an example for simplicity, other character types like
LONGcan also participate in implicit conversions with CLOBs.
Assigning a CLOB to a VARCHAR2 in PL/SQL
VARCHAR2, the data stored in the
CLOBcolumn is retrieved and stored into the
VARCHAR2buffer. If the buffer is not large enough to contain all the
CLOBdata, then a truncation error is thrown and no data is written to the buffer. This is consistent with
VARCHAR2semantics. After successful completion of this assignment operation, the
VARCHAR2variable holds the data as a regular character buffer. This operation can be performed in the following ways:
SELECTpersistent or temporary
CLOBdata into a character buffer variable such as
VARCHAR2. In a single
SELECTstatement, you can have more than one of such defines.
- Assign a
CLOBdata types to built-in SQL and PL/SQL functions and operators that accept
VARCHAR2arguments, such as the
INSTRfunction and the
CLOBdata types to user-defined PL/SQL functions that accept
The following example illustrates the way
CLOB data is
accessed when the
CLOBs are treated as
DECLARE myStoryBuf VARCHAR2(32000); myLob CLOB; BEGIN -- Select a LOB into a VARCHAR2 variable SELECT ad_sourcetext INTO myStoryBuf FROM print_media WHERE ad_id = 12001; DBMS_OUTPUT.PUT_LINE(myStoryBuf); -- Assign a LOB to a VARCHAR2 variable SELECT ad_sourcetext INTO myLob FROM print_media WHERE ad_id = 12001; myStoryBuf := myLob; DBMS_OUTPUT.PUT_LINE(myStoryBuf); END; /
Assigning a VARCHAR2 to a CLOB in PL/SQL
VARCHAR2can be assigned to a
CLOBin the following scenarios:
UPDATEcharacter data stored in
LONGvariables into a
CLOBcolumn. Multiple such binds are allowed in a single
- Assign a
LONGvariable to a
LONGdata types to user-defined PL/SQL functions that accept LOB data types.
DECLARE myLOB CLOB; BEGIN -- Select a VARCHAR2 into a LOB variable SELECT 'ABCDE' INTO myLOB FROM print_media WHERE ad_id = 11001; -- myLOB is a temporary LOB. -- Use myLOB as a lob locator DBMS_OUTPUT.PUT_LINE('Is temp? '||DBMS_LOB.ISTEMPORARY(myLOB)); -- Insert a VARCHAR2 into a lob column INSERT INTO print_media(product_id, ad_id, AD_SOURCETEXT) VALUES (1000, 1, 'ABCDE'); -- Assign a VARCHAR2 to a LOB variable myLob := 'XYZ'; END; /
6.1.3 Implicit Conversions Between BLOB and RAW
This section describes support for implicit conversions between BLOB and RAW data types.
VARCHAR2data types also apply to the implicit conversion process between
RAWdata types, unless mentioned otherwise. However, to provide concise description, most examples in this chapter do not explicitly mention
RAWdata types. The following operations involving BLOB data types support implicit conversions:
UPDATEbinary data stored in
RAWvariables into a
BLOBcolumn. Multiple such binds are allowed in a single
SELECTpersistent or temporary
BLOBdata into a binary buffer variable such as
RAW. Multiple such defines are allowed in a single
- Assign a
LONG RAWvariable, or assign a
LONG RAWto a
BLOBdata types to built-in or user-defined PL/SQL functions defined to accept
LONG RAWdata types or pass
LONG RAWdata types to built-in or user-defined PL/SQL functions defined to accept
6.1.4 Guidelines and Restrictions for Implicit Conversions with LOBs
This section describes the techniques that you use to access LOB columns or attributes using the Data Interface for LOBs.
BLOB columns or
attributes can be referenced by regular SQL statements, such as
There is no piecewise
UPDATE, or fetch routine in PL/SQL. Therefore, the amount of data that can be accessed from a LOB column or attribute is limited by the maximum character buffer size in PL/SQL, which is 32767 bytes. For this reason, only LOBs less than 32 kilo bytes in size can be accessed by PL/SQL applications using the data interface for persistent LOBs.
If you must access a LOB with a size more than
32 kilobytes -1 bytes, using the data interface, then you must make JDBC or OCI calls from the PL/SQL code to use the APIs for piecewise insert and fetch.
Use the following guidelines for using the Data Interface to access LOB columns or attributes:
LOB columns or attributes can be selected into character or binary buffers in PL/SQL. If the LOB column or attribute is longer than the buffer size, then an exception is raised without filling the buffer with any data. LOB columns or attributes can also be selected into LOB locators.
INSERTinto tables containing LOB columns or attributes using regular
INSERTstatements in the
VALUESclause. The field of the LOB column can be a literal, a character data type, a binary data type, or a LOB locator.
LOB columns or attributes can be updated as a whole by
SETstatements. In the
SETclause, the new value can be a literal, a character data type, a binary data type, or a LOB locator.
There are restrictions for binds of more than 4000 bytes:
If a table has both
LONGand LOB columns, then you can bind more than 4000 bytes of data to either the
LONGor LOB columns, but not both in the same statement.
SELECToperation, binding of any length data to LOB columns is not allowed.
- If you bind more than 4000 bytes of data to a
CLOB, and the data consists of a SQL operator, then Oracle Database limits the size of the result to at most 4000 bytes. For example, the following statement inserts only 4000 bytes because the result of
LPADis limited to 4000 bytes:
INSERT INTO print_media (ad_sourcetext) VALUES (lpad('a', 5000, 'a'));
The database does not do implicit hexadecimal to
RAWto hexadecimal conversions on data that is more than 4000 bytes in size. You cannot bind a buffer of character data to a binary data type column, and you cannot bind a buffer of binary data to a character data type column if the buffer is over 4000 bytes in size. Attempting to do so results in your column data being truncated at 4000 bytes.
For example, you cannot bind a
VARCHAR2buffer to a
BLOBcolumn if the buffer is more than 4000 bytes in size. Similarly, you cannot bind a
RAWbuffer to a
CLOBcolumn if the buffer is more than 4000 bytes in size.
6.1.5 Detailed Examples for Implicit Conversions with LOBs
The example in this section demonstrates using multiple
RAW binds in
Example 6-1 Using Character and RAW Binds in INSERT and UPDATE Operations
The following example demonstrates using Character and
RAW binds for LOB columns in
DECLARE bigtext VARCHAR2(32767); smalltext VARCHAR2(2000); bigraw RAW (32767); BEGIN bigtext := LPAD('a', 32767, 'a'); smalltext := LPAD('a', 2000, 'a'); bigraw := utl_raw.cast_to_raw (bigtext); /* Multiple long binds for LOB columns are allowed for INSERT: */ INSERT INTO print_media(product_id, ad_id, ad_sourcetext, ad_composite) VALUES (2004, 1, bigtext, bigraw); /* Single long bind for LOB columns is allowed for INSERT: */ INSERT INTO print_media (product_id, ad_id, ad_sourcetext) VALUES (2005, 2, smalltext); bigtext := LPAD('b', 32767, 'b'); smalltext := LPAD('b', 20, 'a'); bigraw := utl_raw.cast_to_raw (bigtext); /* Multiple long binds for LOB columns are allowed for UPDATE: */ UPDATE print_media SET ad_sourcetext = bigtext, ad_composite = bigraw, ad_finaltext = smalltext; /* Single long bind for LOB columns is allowed for UPDATE: */ UPDATE print_media SET ad_sourcetext = smalltext, ad_finaltext = 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 print_media(product_id, ad_id, ad_sourcetext, press_release) VALUES (2030, 3, bigtext, bigtext); /* Insert of data into LOB attribute is allowed */ INSERT INTO print_media(product_id, ad_id, ad_header) VALUES (2049, 4, adheader_typ(null, null, null, bigraw)); /* The following is not allowed because we try to perform INSERT AS SELECT data INTO LOB */ INSERT INTO print_media(product_id, ad_id, ad_sourcetext) SELECT 2056, 5, bigtext FROM dual; END; /
Example 6-2 Multiple Defines for LOBs in SELECT
The following example demonstrates performing a
operation to retrieve multiple persistent or temporary CLOBs from a SQL query into a
VARCHAR2 variable, or a
BLOB to a
DECLARE ad_src_buffer VARCHAR2(32000); ad_comp_buffer RAW(32000); BEGIN /* This retrieves the LOB columns if they are up to 32000 bytes, * otherwise it raises an exception */ SELECT ad_sourcetext, ad_composite INTO ad_src_buffer, ad_comp_buffer FROM print_media WHERE product_id=2004 AND ad_id=5; /* This retrieves the temporary LOB produced by SUBSTR if it is up to 32000 bytes, * otherwise it raises an exception */ SELECT substr(ad_sourcetext, 2) INTO ad_src_buffer FROM print_media WHERE product_id=2004 AND ad_id=5;END; /
Example 6-3 Implicit Conversions between
Implicit assignment works for variables declared explicitly and for
variables declared by referencing an existing column type using the
attribute as show in the following example. The example assumes that column
long_col in table
t has been migrated from a
LONG to a
CREATE TABLE t (long_col LONG); -- Alter this table to change LONG column to LOB DECLARE a VARCHAR2(100); b t.long_col%type; -- This variable changes from LONG to CLOB BEGIN SELECT * INTO b FROM t; a := b; -- This changes from "VARCHAR2 := LONG to VARCHAR2 := CLOB b := a; -- This changes from "LONG := VARCHAR2 to CLOB := VARCHAR2 END;
Example 6-4 Calling PL/SQL and C Procedures from PL/SQL
You can call a PL/SQL or C procedure from PL/SQL. You can pass a
CLOB as an actual parameter, where a
VARCHAR2 is the formal parameter, or you can pass a
VARCHAR2 as an actual parameter, where a
CLOB is the formal parameter. The same holds good for
RAWs. One example of when these cases can arise is when either the formal or the actual parameter is an anchored type, that is, the variable is declared using the
table_name.column_name%type syntax. PL/SQL procedures or functions can accept a
CLOB or a
VARCHAR2 as a formal parameter. This holds for both built-in and user-defined procedures and functions.
The following example demonstrates implicit conversion during procedure calls:
CREATE OR REPLACE PROCEDURE foo(vvv IN VARCHAR2, ccc INOUT CLOB) AS ... BEGIN ... END; / DECLARE vvv VARCHAR2 := rpad('varchar', 32000, 'varchar') ccc CLOB := rpad('clob', 32000, 'clob') BEGIN foo(vvv, ccc); -- No implicit conversion needed here foo(ccc, vvv); -- Implicit conversion for both parameters done here END; /
Example 6-5 Implicit Conversion with PL/SQL built-in functions
The following example illustrates the use of
CLOBs in PL/SQL
DECLARE my_ad CLOB; revised_ad CLOB; myGist VARCHAR2(100):= 'This is my gist.'; revisedGist VARCHAR2(100); BEGIN INSERT INTO print_media (product_id, ad_id, ad_sourcetext) VALUES (2004, 5, 'Source for advertisement 1'); -- select a CLOB column into a CLOB variable SELECT ad_sourcetext INTO my_ad FROM print_media WHERE product_id=2004 AND ad_id=5; -- perform VARCHAR2 operations on a CLOB variable revised_ad := UPPER(SUBSTR(my_ad, 1, 20)); -- revised_ad is a temporary LOB -- Concat a VARCHAR2 at the end of a CLOB revised_ad := revised_ad || myGist; -- The following statement raises an error if my_ad is -- longer than 100 bytes myGist := my_ad; END; /