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