CreateTempCLOB Example

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