2

Our company is in the process of creating an ASP.NET service to accept XML data sent from ERP systems such as Oracle. We have no experience (at all) with Oracle, so please excuse the simplicity of this question.

I see online that Oracle has a tool called JDeveloper that can hook up to WCF Services that use a DataContract/WSDL to send/receive data with relative ease.

Can anyone advise about the situation regarding WebAPI's, where no WSDL or DataContracts exist? Is it simple to craft a POST in Oracle to send to a WebAPI, or is the former option better/easier to work with?

Thanks in advance.

EvilDr
  • 8,943
  • 14
  • 73
  • 133
  • You want to pull XML out of Oracle (from your service), correct? – tbone Sep 27 '13 at 14:48
  • XML out of Oracle, but rather as a scheduled job not a 'pull' operation. More 'push' I guess. – EvilDr Sep 27 '13 at 14:59
  • push it where? why not pull the XML out of Oracle from your service when needed? – tbone Sep 27 '13 at 15:17
  • Because it needs to work across many different schemas from different companies, which may all be different. Therefore by specifying an XML schema we can guarantee that certain rules are met and avoid complications related to internal IT barriers. – EvilDr Sep 27 '13 at 15:27
  • 1
    Defining a schema is fine and good, I'm just saying don't serve up the XML by trying to push it from Oracle. You should have a service layer that pulls the data (from many db instances perhaps), formats the proper XML, and makes it available to the app layer. – tbone Sep 27 '13 at 15:38
  • Okay this is helpful. It will be up to the company's Oracle dev's to get this up and running, so I am really just interested in how big of a job it is, and what is generally the easiest approach? – EvilDr Sep 27 '13 at 15:40

1 Answers1

2

It's simple enough to call web service directly from Oracle:

  1. There are a good support of XML/XSLT/XQuery to construct requests and parse responses (XML DB)

  2. Oracle have an API to work with HTTP/HTTPS requests (UTL_HTTP package).

So if you decide to call web service from Oracle - it's possible and relatively simple for SOAP and REST web-services.
You can find example code in this answer on StackOverflow.

Update - answer on a comment

To make it clear, example above isn't work at a "database query level" because it's implemented on PL/SQL. Oracle Database engine natively incorporates support for two different languages:

This two things are really different. Even there are a common questions about performance affected by switching context between SQL and PL/SQL engines and mostly caused by improper procedures design.

PL/SQL as a procedural language can access a rich set of APIs, provided by Oracle as a set of built-in packages. Among others there are a number of packages directly related to network communication protocols and standards: UTL_TCP, UTL_URL, UTL_SMTP, UTL_MAIL, UTL_INADDR, UTL_HTTP, HTP, HTF, DBMS_LDAP.

Needs to be said, that there are a set of APIs, provided to support publishing of PL/SQL code on the web. Set of OWA_xxxx packages supports access through mod_plsql. Another thing is a support for publishing SOAP web services in Oracle XML DB.

If you need to unload data from Oracle to web service on a schedule then look at DBMS_SCHEDULE and DBMS_JOB packages to start unload procedures periodically.

Most of this system packages implemented on Java and it's possible to write your own Java extensions callable from PL/SQL.

P.S. There are a UTL_DBWS package dedicated for implementation of calls to SOAP services from Oracle Database, but seems that it produces more problems than solves and I can't find reference to it in 11g documentation (10g only).

P.P.S. Some statements may be slightly inaccurate or contain exaggerations, but that should be enough to understand the overall picture.

Community
  • 1
  • 1
ThinkJet
  • 6,725
  • 24
  • 33
  • Super, thanks. Just a quick one; UTL_HTTP clearly takes place at database query level, but is it possible to create functionality higher up the application stack to achieve the same, or would this build on top of the functions you've already noted? – EvilDr Oct 02 '13 at 15:47
  • 1
    @EvilDr I tried to answer, but for me not clear what is a possibility you are looking for. So, please review an updated answer and refine a question if I missed the point. – ThinkJet Oct 02 '13 at 23:21
  • Amazing update, thank you. Our software (ASP.NET) has two ways of 'accepting' data sent via HTTP, those being WebAPI or WCF (WSDL). It will be the responsibility of our customers to alter their Oracle (etc) implementations to take advantage of our chosen approach. The underlying point I am trying to understand therefore is whether it is more simple in Oracle to send data via WebApi, or to WCF (WSDL), or does it not matter because a good Oracle developer would be comfortable with either? We are just trying to make life simple for our customers. Thanks again, I really appreciate your help. – EvilDr Oct 03 '13 at 07:11
  • 1
    @EvilDr It would be same, but avoid using [JSON serialization](http://www.asp.net/web-api/overview/formats-and-model-binding/json-and-xml-serialization) for WebAPI interface because there are no native support for this data format in Oracle. – ThinkJet Oct 03 '13 at 10:36
  • Excellent. Fantastic set of replies and really really helpful. Thank you :-) – EvilDr Oct 03 '13 at 12:20