Sending CLOBs to Oracle using VB/ADO
From: Tim Dale (dev_at_cavendish.co.uk)
Date: 04/27/04
- Next message: alphaCodeWarrior: "Re: SQL Server Scema"
- Previous message: Live_Love_Laugh: "Re: Not able to open MSDataShape connection"
- Next in thread: Niall Litchfield: "Re: Sending CLOBs to Oracle using VB/ADO"
- Reply: Niall Litchfield: "Re: Sending CLOBs to Oracle using VB/ADO"
- Reply: Ross McKay: "Re: Sending CLOBs to Oracle using VB/ADO"
- Reply: Val Mazur: "Re: Sending CLOBs to Oracle using VB/ADO"
- Messages sorted by: [ date ] [ thread ]
Date: 27 Apr 2004 07:46:46 -0700
Can anyone help ?
I have to pass some XML to an Oracle 8i stored procedure, which is
expecting a CLOB, using VB and ADO.
Only, it isn't working. Oracle keeps returning the error :
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments
(If I remove the parameter from the stored procedure and the call to
it, it
all works fine. i.e. the other parameters are all correct.)
I've been hunting through Google and most people appear to have done
this
without too many problems simply by setting the type of the ADO
parameter to
adLongVarChar. I have done this -- it refuses to work.
Before I get any of the obvious replies, I have tried the following :
- I am using the Oracle OLEDB provider rather than the Microsoft
one.
- I am using MDAC 2.6 (and have tried 2.8 as well).
- I have included the line : m_cmd.Properties("SPPrmsLOB") = TRUE
- I have upgraded to the latest Oracle provider 8.1.7.3.0. The
readme
file says this functionality was added at v8.1.7.0.0.
The SP definition is as follows :
PROCEDURE main (p_afp_clob IN CLOB
,pn_contact_inc_id IN NUMBER
,pn_gpms_id IN NUMBER
,pn_org_id IN NUMBER
,pc_error_ind OUT VARCHAR2
,pc_error OUT VARCHAR2
,pc_ref OUT VARCHAR2
,pc_prop_val IN VARCHAR2 DEFAULT NULL
,pn_expiration_time IN INTEGER DEFAULT NULL);
and my VB code, which uses a wrapper functions, looks like :
If (oDbase.bSetProcedure("main") = True) Then
oDbase.m_cmd.Properties("SPPrmsLOB") = True
Call oDbase.bAddProcTag("p_afp_clob", sXML, adLongVarChar)
Call oDbase.bAddProcTag("pn_contact_inc_id", iContactIncId,
adNumeric)
Call oDbase.bAddProcTag("pn_gpms_id", iGpmsId, adNumeric)
Call oDbase.bAddProcTag("pn_org_id", iOrgId, adNumeric)
Call oDbase.bAddProcTag("pc_error_ind", sErrorNo, adVarChar,
adParamOutput)
Call oDbase.bAddProcTag("pc_error", sErrorText, adVarChar,
adParamOutput)
Call oDbase.bAddProcTag("pc_ref", sRef, adVarChar,
adParamOutput)
If Not IsMissing(sPropVal) Then
Call oDbase.bAddProcTag("pc_prop_val", CStr(sPropVal),
adVarChar)
End If
If Not IsMissing(iTimeout) Then
Call oDbase.bAddProcTag("pn_expiration_time",
CInt(iTimeout), adInteger)
End If
If (oDbase.bExecute() = True) Then
...
The wrapper function bAddProcTag() sets the direction (defaulted to
adParamInput),
name, size and value of the parameter, and then appends the parameter
to the ADO
parameters collection. This works fine for quite a number of stored
procedures
(the remainer of which only use VARCHAR2 and NUMBER parameters)
- Next message: alphaCodeWarrior: "Re: SQL Server Scema"
- Previous message: Live_Love_Laugh: "Re: Not able to open MSDataShape connection"
- Next in thread: Niall Litchfield: "Re: Sending CLOBs to Oracle using VB/ADO"
- Reply: Niall Litchfield: "Re: Sending CLOBs to Oracle using VB/ADO"
- Reply: Ross McKay: "Re: Sending CLOBs to Oracle using VB/ADO"
- Reply: Val Mazur: "Re: Sending CLOBs to Oracle using VB/ADO"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
