|Oracle® Objects for OLE Developer's Guide
11g Release 2 (11.2) for Microsoft Windows
Part Number E12245-01
Creates a temporary LOB in the database.
Set OraBLOB = OraDatabase.CreateTempBLOB(use_caching) Set OraCLOB = OraDatabase.CreateTempCLOB(use_caching)
The arguments for the method are:
||A boolean value that specifies whether Oracle Database uses caching when accessing this LOB. The default value is
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.)
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
OraCLOB exist on the client. Note that these references include any
OraParamArray that contain a temporary
Example: Passing a Temporary CLOB to a Stored Procedure
The following example illustrates the use of the
CreateTempClob method to create a
OraCLOB is then populated with data and passed to a stored procedure which has an argument of type
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