Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: XML (and noobie question)
Sorry for the delayed response. Didn't see your question initially.
Anyway, all of our stored procs have the same inteface. ( see below )
We build up the response xml but appending to the responseXML in/out
parameter. These procedures are all called from a middle-tier,
COM+ application using ADO calls. ( over Oracle OLEDB Provider ). See
example ADO code below. Does this help?
PROCEDURE ExampleProc(requestXML IN CLOB, responseXML IN OUT CLOB);
-- Dim conData As ADODB.Connection Dim cmdData As ADODB.Command Dim prmInputData As ADODB.Parameter Dim prmOutputData As ADODB.Parameter Dim sResultXML As String Set conData = New ADODB.Connection Set cmdData = New ADODB.Command If conData.State = adStateClosed Then ' Open connection ConnectMe conData End If '------------------------- ' Fill and pass parameter '------------------------- Set prmInputData = cmdData.CreateParameter("sIn", adLongVarChar, adParamInput, Len(sParameters), sParameters) Set prmOutputData = cmdData.CreateParameter("sOut", adLongVarChar, adParamOutput, 100) '------------------------------- ' Assign stored proc parameters ' and then execute it '------------------------------- With cmdDataReceived on Fri Nov 12 2004 - 09:13:37 CST
.ActiveConnection = conData
.Properties("SPPrmsLOB") = True
.CommandText = sStoredProcName
.CommandType = adCmdStoredProc
.Parameters.Append prmInputData
.Parameters.Append prmOutputData
.Execute
End With sResultXML = CStr(prmOutputData.Value) ---------------------------------------------------- This mailbox protected from junk email by MailFrontier Desktop from MailFrontier, Inc. http://info.mailfrontier.com "Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:41813bb6_at_news.victoria.tc.ca... > KurtisK (KJKYLE_at_COOLBLUENOSPAM.COM) wrote: > : This block shows how we do this in our application. We basically pass in a > : CLOB parameter to stored procedure. In the procedure, we append to this > : CLOB using > > : Oracle's XML functions. Hope this helps. > > : Kurt > > > : xmlDoc XMLTYPE; > : responseXML CLOB; > > : DBMS_LOB.createtemporary(responseXML, TRUE); > > : responseXML := '<?xml version ...>'; > > : SELECT XMLAGG(XMLELEMENT("Equipment", XMLATTRIBUTES(EquipmentID AS > : "EquipmentID"), > : XMLELEMENT("DatasetID", > : DatasetID), > : XMLELEMENT("LastModified", > : LastModified))) > : INTO xmlDoc > : FROM tblEquipment; > > : IF xmlDoc IS NOT NULL > > : THEN > > : DBMS_LOB.append(responseXML, xmlDoc.Getclobval); > > : END IF; > > I've read examples like this, but they always leave me with a "lack of > understanding something fundamental" question. > > This code appears to be running on the server. That means that > responseXML is stored in the memory of the server computer - so how does > the data typically then get from there to what ever eventual facility is > going to use the data? > > The only time I used LOB (CLOB BLOB, I don't recall) I wanted to get the > data from the LOB column (on the server) into a file locally. and later > put that file back into the LOB column. I used an OLE control to do this, > but never figured out how to do it directly myself, so I have no idea how > the OLE control was doing this task. >
![]() |
![]() |