Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2) Part Number B14249-01 |
|
|
View PDF |
This chapter discusses LOB operations that differ between persistent and temporary LOB instances. This chapter contains these topics:
Creating Persistent and Temporary LOBs in PL/SQL
See Also:
Chapter 14, " LOB APIs for Basic Operations" gives details and examples of API usage for LOB APIs that can be used with either temporary or persistent LOBs.
Chapter 15, "LOB APIs for BFILE Operations" gives details and examples for usage of LOB APIs that operate on BFILEs.
This section describes operations that apply only to persistent LOBs.
You can insert LOB instances into persistent LOB columns using any of the methods described in Chapter 8, "DDL and DML Statements with LOBs".
You can select a persistent LOB from a table just as you would any other datatype. In the following example, persistent LOB instances of different types are selected into PL/SQL variables.
declare blob1 BLOB; blob2 BLOB; clob1 CLOB; nclob1 NCLOB; BEGIN SELECT ad_photo INTO blob1 FROM print_media WHERE Product_id = 2268 FOR UPDATE; SELECT ad_photo INTO blob2 FROM print_media WHERE Product_id = 3106; SELECT ad_sourcetext INTO clob1 FROM Print_media WHERE product_id=3106 and ad_id=13001 FOR UPDATE; SELECT ad_fltextn INTO nclob1 FROM Print_media WHERE product_id=3060 and ad_id=11001 FOR UPDATE; END; / show errors;
This section describes operations that apply only to temporary LOB instances.
To create a temporary LOB instance, you must declare a variable of the given LOB datatype and pass the variable to the CREATETEMPORARY
API. The temporary LOB instance will exist in your application until it goes out of scope, your session terminates, or you explicitly free the instance. Freeing a temporary LOB instance is recommended to free system resources.
The following example demonstrates how to create and free a temporary LOB in the PL/SQL environment using the DBMS_LOB package.
declare blob1 BLOB; blob2 BLOB; clob1 CLOB; nclob1 NCLOB; BEGIN -- create temp LOBs DBMS_LOB.CREATETEMPORARY(blob1,TRUE, DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY(blob2,TRUE, DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY(clob1,TRUE, DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY(nclob1,TRUE, DBMS_LOB.SESSION); -- fill with data writeDataToLOB_proc(blob1); writeDataToLOB_proc(blob2); -- CHAR->LOB conversion clob1 := 'abcde'; nclob1 := TO_NCLOB(clob1); -- Other APIs call_lob_apis(blob1, blob2, clob1, nclob1); -- free temp LOBs DBMS_LOB.FREETEMPORARY(blob1); DBMS_LOB.FREETEMPORARY(blob2); DBMS_LOB.FREETEMPORARY(clob1); DBMS_LOB.FREETEMPORARY(nclob1); END; / show errors;
The code example that follows illustrates how to create persistent and temporary LOBs in PL/SQL. This code is in the demonstration file:
$ORACLE_HOME/rdbms/demo/lobs/plsql/lobdemo.sql */
This demonstration file also calls procedures in separate PL/SQL files that illustrate usage of other LOB APIs. For a list of these files and links to more information about related LOB APIs, see "PL/SQL LOB Demonstration Files".
----------------------------------------------------------------------------- ------------------------- Persistent LOB operations ------------------------ ----------------------------------------------------------------------------- declare blob1 BLOB; blob2 BLOB; clob1 CLOB; nclob1 NCLOB; BEGIN SELECT ad_photo INTO blob1 FROM print_media WHERE Product_id = 2268 FOR UPDATE; SELECT ad_photo INTO blob2 FROM print_media WHERE Product_id = 3106; SELECT ad_sourcetext INTO clob1 FROM Print_media WHERE product_id=3106 and ad_id=13001 FOR UPDATE; SELECT ad_fltextn INTO nclob1 FROM Print_media WHERE product_id=3060 and ad_id=11001 FOR UPDATE; call_lob_apis(blob1, blob2, clob1, nclob1); rollback; END; / show errors; ----------------------------------------------------------------------------- ------------------------- Temporary LOB operations ------------------------ ----------------------------------------------------------------------------- declare blob1 BLOB; blob2 BLOB; clob1 CLOB; nclob1 NCLOB; BEGIN -- create temp LOBs DBMS_LOB.CREATETEMPORARY(blob1,TRUE, DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY(blob2,TRUE, DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY(clob1,TRUE, DBMS_LOB.SESSION); DBMS_LOB.CREATETEMPORARY(nclob1,TRUE, DBMS_LOB.SESSION); -- fill with data writeDataToLOB_proc(blob1); writeDataToLOB_proc(blob2); -- CHAR->LOB conversion clob1 := 'abcde'; nclob1 := TO_NCLOB(clob1); -- Other APIs call_lob_apis(blob1, blob2, clob1, nclob1); -- free temp LOBs DBMS_LOB.FREETEMPORARY(blob1); DBMS_LOB.FREETEMPORARY(blob2); DBMS_LOB.FREETEMPORARY(clob1); DBMS_LOB.FREETEMPORARY(nclob1); END; / show errors;