3 Temporary LOBs
Temporary LOBs are transient, just like other local variables in an application. This chapter discusses operations that are specific to temporary LOBs.
- Before You Begin
Ensure that you go through the topics in this section before you start working with temporary LOBs. - Temporary LOB APIs in Different Programmatic Interfaces
This section lists the temporary LOB specific APIs in different Programmatic Interfaces. - Transforming LOBs
Value LOBs are a subset of read-only temporary LOBs, which are a subset of temporary LOBs.
3.1 Transforming LOBs
Value LOBs are a subset of read-only temporary LOBs, which are a subset of temporary LOBs.
The following table summarizes how you can transform different kinds of LOBs. The column on the left is the source LOB which you want to transform. The column headings are the target LOBs, the final state of the LOB after the transformation. For example, to transform read-only temporary LOBs to value LOBs, you can use LOB_VALUE(lob_producing_plsql_function(...))
.
Source LOB | Value LOBs | Read-only Temporary LOBs | Temporary LOBs | Persistent LOBs |
---|---|---|---|---|
Value LOBs | Not applicable |
|
Not directly possible | Not directly possible |
Read-only Temporary LOBs | LOB_VALUE(lob_producing_plsql_function(...)) |
Not applicable | Not directly possible | Not directly possible |
Temporary LOBs | LOB_VALUE(temporary_lob) |
Open in READ mode | Not applicable | Not directly possible |
Persistent LOBs |
|
Not directly possible | Use SQL operators, such as to_clob() or substr() |
Not applicable |
The following example shows how you can transform temporary LOBs and read-only temporary LOBs to value LOBs. Also, how you can transform a value LOB to a read-only temporary LOB.
DROP TABLE t;
CREATE TABLE t (c clob) lob(c) query as value;
INSERT INTO t VALUES ('I am a CLOB');
CREATE OR REPLACE FUNCTION Vbl2rdo (c clob) RETURN clob IS
BEGIN
RETURN c;
END;
/
-- Transform value LOB to read-only temporary LOB
var tc clob;
BEGIN
SELECT c INTO :tc FROM t;
END;
/
print :tc
SELECT Vbl2rdo(c) FROM t;
-- Transform read-only temporary LOB to value LOB
SELECT lob_value(:tc) FROM dual;
-- Transform temporary LOB to value LOB
SELECT lob_value(to_clob('I am a temporary LOB')) FROM dual;
The following example shows how you can transform a persistent LOB to a temporary LOB and a value LOB.
DROP TABLE t2;
CREATE TABLE t2 (c CLOB);
INSERT INTO t2 VALUES ('I am a CLOB');
-- Transform persistent LOB to value LOB
SELECT Lob_value(c) FROM t2;
-- Transform persistent LOB to temporary LOB
SELECT To_clob(c) FROM t2;
Parent topic: Temporary LOBs