OCI, LOBs and stored procedures

Hi folks,

I've been banging my head against google/asktom/oracle docs for the last few days trying to figure this one out.

I have a select statement, part of which uses a stored procedure to convert a string into a temporary table. This is then used to join with another table (instead of using the IN clause)

This gives outstanding speedups, and works fine as long as this stored proc takes a varchar2.

However sometimes we need to pass a string longer than 32k to this varchar2 field, so wish to use CLOBs instead.

The obvious approach seems to be:

1> create a LobLocator using OCIDescriptorAlloc (type OCI_DTYPE_LOB)
2> create a temporary CLOB, using OCILobCreateTemporary (OCI_TEMP_CLOB)
3> use OCILobWrite to fill in the string
4> bind the CLOB to a named parameter, and execute the statement

Or not.

I get an error when calling OCIStmtExec:

ORA-06502:
 PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 ORA-06512:
 at ""

Anyone got any suggestions on if/how this is possible?

I'm trying with Oracle 8.1.7 and Oracle 9.2 Databases on UNIX, dev environment with Windows (Visual Studio 7) (Oracle 8 and 9i client where relevant)

Anyone able to tell me if I'm flogging a dead horse on this one?

Cheers,

Glyn

-- 



------------------------------------------------------------------------

Glyn Davies / gryn_at_plig.net / www.technobobbins.com / Insert quote here?


------------------------------------------------------------------------