stored procedure with CLOB out parameter won't work with oracle provider
- From: charles b black <cboozb@xxxxxxxxxxx>
- Date: Wed, 20 Sep 2006 22:34:21 GMT
We have the following stored procedure in Oracle 10g database:
CREATE OR REPLACE PACKAGE BODY CLOBBER AS
PROCEDURE GET_CLOB ( id IN VARCHAR2, data OUT CLOB)
IS
BEGIN
SELECT clob_data.getClobVal() FROM our_clob_table WHERE clob_id = id;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END GET_CLOB;
The our_clob_table table has 2 columns:
name type
clob_id 130 (DBTYPE_WSTR)
clob_data 13 (DBTYPE_IUNKNOWN)
I was using Visual C++ 6 and the OLEDB interfaces to call this stored
procedure;
IDBInitialize-->IDBCreateSession; then,
call IDBCreateSession::CreateSession to get IOpenRowset; then
IOpenRowset-->IDBCreateCommand; then
call IDBCreateCommand to get ICommandProperties.
Use ICommandProperties::SetProperties to set ORAPROP_SPPrmsLOB
then use ICommandProperties-->ICommandText
call ICommandText::SetCommandText(DBGUID_DEFAULT, L"BEGIN
GET_CLOB(?,?); END;")
then set up DBBINDING array for the one IN parameter and the one OUT
parameter. (see stored procedure above)
Previously I was able to set it up :
struct tagSP_PARAMS {
TCHAR id[255];
TCHAR stuff[8194];
} SP_PARAMS param;
DBBINDING rgBinding[2];
....
rgBinding[0].iOrdinal = 1;
rgBinding[0].obValue = offsetof(SP_PARAMS, id);
....
rgBinding[1].iOrdinal = 2;
rgBinding[1].obValue = offsetof(SP_PARAMS, stuff);
then instantiate IAccessor (from ICommandText)
call pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,2,rgBinding,
sizeof(SP_PARAMS), &handle,rgStatus);
then
DBPARAMS p;
p.pData = ¶m;
p.cParamSets = 1;
p.hAccessor = handle;
and finally call
pICommandText->Execute(NULL, IID_NULL, &p, &cNumRows,
(IUnknown**)&pIRowset).
//now the CLOB will be found in the stuff member of SP_PARAMS struct
BUT, for RELEASE UNICODE BUILDS this now always fails with
HRESULT of -2147217900 and
ORA-06502: PL/SQL:numeric or value error: character string buffer too
small
ORA-06512: at line 1
Previously for previous versions of Oracle Provider for OLEDBOracle
this was working. And it doesn't matter how large I adjust the sizes
of the TCHAR arrays in the struct SP_PARAMS. It fails.
If I do a DEBUG BUILD, it works.
I tried using VB and ADO to access the same stored procedure and was
getting -2147217900 - ROW-0004:Invalid column datatype.
So that explains the -2147217900.
Does anyone know how to use Oracle Provider for OLEDB to call a stored
procedure that has a CLOB as an OUT parameter???????
Charles B. Black
CPP Inc.
Mountain View, CA 94043
cboozb@xxxxxxxxxxx
-
.
- Prev by Date: Re: How to write or replace a custom OLEDB provider
- Next by Date: Need help processing multiple result set via stored procedure
- Previous by thread: Re: Could not fetch a row from OLE DB provider 'VFPOLEDB'
- Next by thread: Need help processing multiple result set via stored procedure
- Index(es):
Relevant Pages
|
