How can I access a Oracle CLOB object from ASP?

[gerickpa]

Hi all,

I have posted this also in the ASP forum but somebody here might have an answer.

I am currently using Oracle 8i and been trying to use Oracle XSU in order to generate the resultset of a query as XML. I need to return the XML back to the ASP (Classic ASP, not .NET) page. Since the XML string can exceed 4000 characters, I am returning it from the stored procedure as CLOB. I am trying to access the results using ADO. So my Oracle stored procedure is declared as follows:

CREATE PROCEDURE SPP_GET_XML (
pv_sSQL IN VARCHAR2,
pv_sXML OUT CLOB)
...

and my ASP contains the following:

sSQL = "... SELECT statement ..."
set cn = Server.CreateObject("ADODB.Connection&quot
cn.Open ConnectionString
set oCmd = Server.CreateObject("ADODB.Command&quot
with oCmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "SPP_GET_XML"
Set oParam1 = .CreateParameter("pv_sSQL", adVarChar, adParamInput, 4000, sSQL)
.Parameters.Append oParam1
Set oParam2 = .CreateParameter("pv_sXML", adLongVarChar, adParamOutput, 32512)
.Parameters.Append oParam2
.Execute
end with
sXML = oParam2.value

but I keep getting the error:

PLS-00306: wrong number or types of arguments in call to 'SPP_GET_XML'

I had tried it also by changing the data type of pv_sXML in the stored procedure to VARCHAR2 and using DBMS_LOB.READ for setting the value for this variable but it only works when the length of the XML string is at most 4000 characters.

Is there a way to return a CLOB value back to ASP as this is really what I need to happen? Does anybody know of any workarounds?

I would appreciate any help.

Thanks,
Gene Cardenio
gcardenio@rosenbluth.com