How can I access a Oracle CLOB object from ASP?

[gerickpa]

Hi all,

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

 

[ehsant]

Hi ...
I had been chalenging with this a little while ...
the only way is using OO4O. (OLE Object For Oracle)
and it has an object named XOraSession which with that you can make something like Connection and it has also something named DynaSet which is same as RecordSet.
If you goto the OracleHome in your program files you can find OO4O help in it.

----
TNX.
E.T.

 

[gerickpa]

Hi ehsant,

Sorry for not responding back sooner. I got busy with other stuff. Anyway, I was already going in this direction (OO4O) but I got stumped with the error I'm getting. In my ASP code, I defined a parameter (for the XML data) to be an output parameter with the Parameters.Add method and set its ServerType to be ORATYPE_CLOB (112). Then I tried using a stored procedure with the CLOB as an output parameter and I also tried a stored function with CLOB as the return value. Either way, when I call the ExecuteSQL method with the stored procedure/function, the error is

Error #440
OIP-04796: Error in creating object instance

I'm not sure what is causing this. I can't locate the error in the Oracle technical documentation. If you have any idea, I will appreciate the help.

Thanks again,
Gene Cardenio
gcardenio@rosenbluth.com

 

[ehsant]

Hi ...
I'm not sure about the error, but when and where does the error occurs ?
can you post your code ?
----
TNX.
E.T.

 

[gerickpa]

Hi ehsant,

I have a stored procedure called SPP_GET_XML defined in a package called PACKAGE_NAME. Its declaration is as follows:

PROCEDURE SPP_GET_XML (
pv_sSQL IN VARCHAR2,
pv_sXML OUT CLOB);

and it supposedly places the XML output into pv_sXML based on the SQL statement passed through pv_sSQL. It makes use of Oracle XSU to do this (through the DBMS_XMLQuery object).

This is the code that I have in ASP:

Dim OraSession, OraDatabase
Dim osRecordSet

Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession&quot
Set OraDatabase = OraSession.OpenDatabase(host_name, credentials ,cint(0))

' Set up parameters
OraDatabase.Parameters.Add "pv_sSQL", "", ORAPARM_INPUT
OraDatabase.Parameters ("pv_sSQL&quot.ServerType = ORATYPE_VARCHAR2

OraDatabase.Parameters.Add "pv_sXML", Null, ORAPARM_OUTPUT
OraDatabase.Parameters ("pv_sXML&quot.ServerType = ORATYPE_CLOB

' bind the variables
OraDatabase.Parameters("pv_sSQL&quot.Value = SQL_Select_Statement

' Execute the stored procedure PACKAGE_NAME.SPP_GET_XML to retrieve pv_sXML.
OraDatabase.ExecuteSQL "begin PACKAGE_NAME.SPP_GET_XML pv_sSQL, v_sXML); end;"

if Err.number <> 0 then
Response.Write &quot;Error #&quot; & Err.number & &quot;<BR>&quot; & Err.Description & &quot;<BR>&quot;
end if

' CLOB value will be stored in OraDatabase.Parameters(&quot;pv_sXML&quot.Value

As you will note, the Response.Write for the error is right after the ExecuteSQL method and what I am getting on the page is

Error #440
OIP-04796: Error in creating object instance

This is what I'm not sure what is causing the error. Again, if you have some ideas, I will greatly appreciate it.

Thanks,
Gene Cardenio
gcardenio@rosenbluth.com

 

[ehsant]

Hi ...
I'm not sure ...
But you had nt said in which line you get the error.
the point is that When I used this line :
Set OraSession = Server.CreateObject(&quot;OracleInProcServer.XOraSession&quot
I remember the I used to get an error, but when I used this line :
Set OraSession = CreateObject(&quot;OracleInProcServer.XOraSession&quot
there where no error.
can you try this ?
----
TNX.
E.T.

 

[801119]

okey... screw my last message, the problem is the DATE!! My codes look like something a kid wrote
I have absolutely no idea what I am talking about
Somehow I still manage to make it work

 

[gerickpa]

Hi ehsant,

The problematic line is this:

OraDatabase.ExecuteSQL &quot;begin PACKAGE_NAME.SPP_GET_XML pv_sSQL, v_sXML); end;&quot;

which executes the packaged stored procedure. The CreateObject, OpenDatabase and the addition and binding of parameters all go through fine. Of course, in my actual code, I have the host_name, credentials, SQL_Select_Statement and PACKAGE_NAME spelled out.

Thanks,
Gene Cardenio
gcardenio@rosenbluth.com

 

[801119]

hrm... one question, how did my post end up here?! =S sooo wrong place, sorry all =D My codes look like something a kid wrote
I have absolutely no idea what I am talking about
Somehow I still manage to make it work