Home » SQL & PL/SQL » SQL & PL/SQL » Using UTL_HTTP, are you able to send attachments in a web service? (Oracle DB 10G, Linux)
Using UTL_HTTP, are you able to send attachments in a web service? [message #442642] Tue, 09 February 2010 12:32 Go to next message
jbryniar1
Messages: 10
Registered: May 2006
Location: Atlanta, GA
Junior Member
Hello.

I will be sending some XML data with a SOAP request using UTL_HTTP.begin_request. I will consuming a web service. The requirement is to also send attachments. Am I able to do so? I am only seeing attachments with UTL_SMPT which would send an e-mail, which I do not want to do. I want to send the XML data and attachments with UTL_HTTP all together at once (and there are two attachments by the way).

I want to use UTL_HTTP, as I have some experience with this. I have very little experience with Web Services, XML, and Java though.

Thank you for any help.

John
Re: Using UTL_HTTP, are you able to send attachments in a web service? [message #442649 is a reply to message #442642] Tue, 09 February 2010 13:46 Go to previous messageGo to next message
jbryniar1
Messages: 10
Registered: May 2006
Location: Atlanta, GA
Junior Member
I have already completed a successful web service program call, and wanted to use almost the same code. However, I see nothing about being able to send attachments using UTL_HTTP, just UTL_SMPT. We save an e-delivery notification and a ship confirmation in Oracle Order Management attachments, and the goal is to retrieve those attachments and send them on to the web service I would consume.

Thank you again everyone.
John
Re: Using UTL_HTTP, are you able to send attachments in a web service? [message #442652 is a reply to message #442649] Tue, 09 February 2010 14:10 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are basically two options

1) embedding
2) referencing

XML, SOAP and Binary Data

Embedding might be possible with UTL_HTTP if you encode the data into base64 first and then add it to the SOAP request.

For referencing you would probably have to create the M/HTML MIME yourself, I don't know of any oracle package that does it.
Re: Using UTL_HTTP, are you able to send attachments in a web service? [message #442816 is a reply to message #442652] Wed, 10 February 2010 15:49 Go to previous messageGo to next message
jbryniar1
Messages: 10
Registered: May 2006
Location: Atlanta, GA
Junior Member
ThomasG wrote on Tue, 09 February 2010 15:10
There are basically two options

1) embedding
2) referencing

XML, SOAP and Binary Data

Embedding might be possible with UTL_HTTP if you encode the data into base64 first and then add it to the SOAP request.

For referencing you would probably have to create the M/HTML MIME yourself, I don't know of any oracle package that does it.


This doesn't sound like much fun for someone with really no experience using M/HTML MIME! Any idea how much time it would take to do something like this? Would it be better to just consider using something else other than Oracle PL/SQL from Oracle to call the Webservice, like something using Java to make a web call then that might be able to send attachments?
Re: Using UTL_HTTP, are you able to send attachments in a web service? [message #443429 is a reply to message #442816] Mon, 15 February 2010 13:23 Go to previous message
jbryniar1
Messages: 10
Registered: May 2006
Location: Atlanta, GA
Junior Member
I am very confused here.

OK. Once again, thank you for the information and examples. I guess this is what I really need to know. Some of this already discussed os over my head, but the main goal is off course to be able to send data from Oracle to another system using UTL_HTTP based on status changes in Order Management. Basiclaly the attachment is stored in the FND_DOCUMENTS_LONG_TEXT table as the LONG_TEXT column:

SELECT ROWID,MEDIA_ID,LONG_TEXT FROM FND_DOCUMENTS_LONG_TEXT WHERE (MEDIA_ID=432332)

From the example provided to be, I cannot tell if I need to somehow transpose this LONG_TEXT into a PDF and then send it, or if I can send just the TEXT as is?

Below is an example call to send an attachment, but I cannot tell what is really going on. I don't understand the relationship between an XSD file and the XML file either. The program is supposedly able to handle multiple attachments too. I do not know how this will work within making UTL_HTTP calls:

The following is the sample xsd which defines the document details and the document access.

<xsd:complexType name="ImportMultipleDocumentRequest">
    <xsd:sequence>
        <xsd:element name="header" type="commons:Header"/>
	<xsd:element name="transactionId" type="xsd:string"/>			
	<xsd:element maxOccurs="unbounded" minOccurs="0" name="documentDetail"           type="importschema:DocumentDetails"/>
     </xsd:sequence>
</xsd:complexType>

<xsd:complexType name="DocumentDetails">
         <xsd:sequence>
             <xsd:element name="documentName" type="xsd:string"/>
             <xsd:element name="documentDescription" nillable="true" type="xsd:string"/>
             <xsd:element name="documentClass" nillable="true" type="xsd:string"/>
             <xsd:element name="documentContent" type="xsd:base64Binary"/>
             <xsd:element maxOccurs="unbounded" minOccurs="0" name="documentAccess"
                       type="xsd:boolean"/>
         </xsd:sequence>
    </xsd:complexType>


This xsd is a complex type DocumentDetails which holds documentName, documentDescription, documentClass, documentContent which accepts strings as its values. The last element in the complex type is a Boolean array which accepts the values of true or false based on the document access specified.

Here is a sample input request xml,
<soapenv:Envelope xmlns:soapenv="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]" 
       xmlns:imp="[url]http://multidoc.imported.bean.soa.contracts.operations.support/ImportMultipleDocumentBase64ServiceSchema[/url]"
       xmlns:com="[url]http://common.bean.soa.contracts.operations.support/CommonServiceSchema[/url]">
   <soapenv:Header/>
   <soapenv:Body>
      <imp:ImportMultipleDocumentRequest>
         <imp:header>
            <com:userID>[email]thaocsohwT@us.xxx.com[/email]</com:userID>
            <com:orgID>Americas CSO HWT</com:orgID>
            <com:spID>1212121212</com:spID>
            <com:appID>[email]binotesT@us.xxx.com[/email]</com:appID>
            <com:sourceID>COL</com:sourceID>
            <com:module>P</com:module>
         </imp:header>
         <imp:trasnactionId>IMP_PROD_TEST_001-v1</imp:trasnactionId>
         
	<imp:documentDetails>
            <imp:documentName>FirstDoc.pdf</imp:documentName>
            <imp:documentDescription>FirstDoc</imp:documentDescription>
            <imp:documentClass>1</imp:documentClass>
            <imp:documentContent>cid:269912521934</imp:documentContent>
            <imp:documentAccess>false</imp:documentAccess>
            <imp:documentAccess>true</imp:documentAccess>
 	    <imp:documentAccess>false</imp:documentAccess>
            <imp:documentAccess>true</imp:documentAccess>
         </imp:documentDetails>


	<imp:documentDetails>
            <imp:documentName>SecondDoc.txt</imp:documentName>
            <imp:documentDescription>SecondDoc</imp:documentDescription>
            <imp:documentClass>2</imp:documentClass>
            <imp:documentContent>cid:269912521934</imp:documentContent>
            <imp:documentAccess>true</imp:documentAccess>
            <imp:documentAccess>false</imp:documentAccess>
 	    <imp:documentAccess>true</imp:documentAccess>
            <imp:documentAccess>true</imp:documentAccess>
         </imp:documentDetails>

	<imp:documentDetails>
            <imp:documentName>ThirdDoc.jpg</imp:documentName>
            <imp:documentDescription>ThirdDoc</imp:documentDescription>
                     <imp:documentClass>2</imp:documentClass>
            <imp:documentContent>cid:269912521934</imp:documentContent>
            <imp:documentAccess>true</imp:documentAccess>
            <imp:documentAccess>true</imp:documentAccess>
 	    <imp:documentAccess>true</imp:documentAccess>
            <imp:documentAccess>true</imp:documentAccess>
         </imp:documentDetails>
      </imp:ImportMultipleDocumentRequest>
   </soapenv:Body>
</soapenv:Envelope>


Thank you again.

John


[Mod-Edit: Frank added code tags and split some long lines]

[Updated on: Mon, 15 February 2010 23:44] by Moderator

Report message to a moderator

Previous Topic: how to convert number into binary format.
Next Topic: Dynamic Merge
Goto Forum:
  


Current Time: Thu Sep 29 09:01:34 CDT 2016

Total time taken to generate the page: 0.07515 seconds