Set OraBLOB = OraDatabase.CreateTempBLOB(use_caching) Set OraCLOB = OraDatabase.CreateTempCLOB(use_caching)
The arguments for the method are:
| Arguments | Description |
|---|---|
use_caching |
A boolean value that specifies whether Oracle Database uses caching when accessing this LOB. The default value is False. |
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.
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