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	Pass from SQL to PLSQL. If passed from PLSQL out to JDBC, OCI etc, it stays a read-only temporary LOB. Send to older JDBC, OCI etc clients	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	SELECT from column declared as QUERY AS VALUE LOB_VALUE(persistent_lob)	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;