Re: OraClob

Something other than this?

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 Received on Thu May 06 2004 - 14:36:01 CDT