CreateTempBLOB/CLOB Method

Applies To

OraDatabase Object

Description

Creates a temporary LOB in the database.

Usage

Set OraBLOB = OraDatabase.CreateTempBLOB(use_caching) Set OraCLOB = OraDatabase.CreateTempCLOB(use_caching)

Arguments

The arguments for the method are:

Remarks

Temporary LOBs are LOBs that do not exist permanently in the database. OO4O programmers commonly use temporary LOBs to pass into stored procedures and functions that have LOB arguments.

Temporary LOBs do not require or take part in transactions. (It is not necessary to acquire a lock before write operations, and rollbacks have no effect on temporary LOBs.)

The use_caching argument directs Oracle to use caching when accessing the temporary LOB. This is suggested when multiple accesses are expected on a single LOB. Caching is not required for the typical case, where a LOB is created, filled with data, passed to a stored procedure, and then discarded.

Temporary LOBs exist on the database until no more references to the corresponding OraBLOB or OraCLOB exist on the client. Note that these references include any OraParameter or OraParamArray that contain a temporary OraBLOB or OraCLOB object.

Examples

Example: Passing a Temporary CLOB to a Stored Procedure

The following example illustrates the use of the CreateTempClob method to create a OraCLOB. The OraCLOB is then populated with data and passed to a stored procedure which has an argument of type CLOB.

Dim OraSession as OraSession 
Dim OraDatabase as OraDatabase
Dim OraClob as OraClob 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&) 
 
'Create the stored procedure used in this example 
OraDatabase.ExecuteSQL ("create or replace procedure GetClobSize" & _
           "(in_clob IN CLOB, clobsize OUT NUMBER) as Begin clobsize" & _ 
           " := DBMS_LOB.GETLENGTH(in_clob); End;") 
 
'create an OraParameter object to represent Clob bind Variable 
OraDatabase.Parameters.Add "CLOB", Null, ORAPARM_INPUT,  ORATYPE_CLOB 
 
'the size will go into this bind variable 
OraDatabase.Parameters.Add "CLOBSIZE", Null, ORAPARM_OUTPUT,  ORATYPE_NUMBER 
 
' create a temporary CLOB 
set OraClob = OraDatabase.CreateTempClob 
 
'Populate the OraClob with some data. Note that no row locks are needed. 
OraClob.Write "This is some test data" 
 
'set the Parameter Value to the temporary Lob 
OraDatabase.Parameters("CLOB").Value = OraClob 
 
'execute the sql statement which updates Address in the person_tab 
OraDatabase.ExecuteSQL ("Begin GetClobSize(:CLOB, :CLOBSIZE); end;") 
 
'Display the size 
MsgBox OraDatabase.Parameters("CLOBSize").Value 
 
'these two lines force the temporary clob to be freed immediately 
OraDatabase.Parameters.Remove "CLOB" 
Set OraClob = nothing