What is the best (fastest, easiest, most efficient) wayt to pass structured data into and out of Oracle

Question

This is targeted at Technical Leads and Architects, but Anyone can answer this - all thoughts are welcome - have fun with it.

I want to abstract a large database store (relational, OLTP) from the application layer by having the application consider the database as a service. The natural way of doing this is by calling sprocs, but traditionally these follow CRUD paradigms and in keeping with my abstraction thinking I want to abstract away all knowledge of data structures within the database and concentrate on business process.

So instead of having 'Save Invoice' business process be this....

1. Start Transaction
2. Create Invoice Header
3. For Invoice Line Items
   1. Create Invoice Line Items
4. Commit Transaction

... instead I want to pass into the database structured data representing the invoice.

I can pass down an XML document containing the invoice, but this is what I want to avoid on the Database side:

1. Parsing XML
2. XML Validation
3. Parameter extraction and binding into Oracle PL/SQL objects

Off-course, in all cases, no matter the solution, this must be done. However, I don't want to pay the XML document penalty (the angle bracket tax).

Hence the question - what is the most efficient way to send and receive and structure data into Oracle stored procedures?

I would like to hear from those who want to argue for JSON, ATOM or other formats.

Also consider native or binary mechanisms to achieve this. What about constructing and sending in Oracle tables (memory datasets)? Has anyone done this before? What where your experiences?

Answer 1

You can create a collection, fill it on the client side, pass it into the Oracle procedure and do set-based operations with it:

INSERT
INTO    dest_table
SELECT  *
FROM    TABLE(:mycollection)

Answer 2

That depends. You can store the data in any form as a LOB. The main reason why no one is doing this is that you store data in a database so you can search through it. The Internet was a nice idea until Google came around and made it possible to find things.

So you must parse the data in some way. You can parse it on the client and send SQL insert/updates. If you do this with your favorite OO language, you'd have an OR mapper (which can load and save "objects" in plain SQL tables). This way, the heavy lifting (the parsing) is done on many clients while the database just stores and searches the data.

Answer 3

I want to abstract a large database store (relational, OLTP) from the application layer by having the application consider the database as a service.

That's what DAO layer is for. For application code DAO is a persistence layer (service in your terms). DAO knows how to store a structured document.

I presume you have to do SQL queries against the stored documents.

Though there is an Oracle-specific XML format, I'd refrain from using it as it ties your code to Oracle. As long as standard SQL works, use that.

Answer 4

I don't understand why one would like to do anything like 'parsing' and 'validation' on a database.

It may be that we use very heavily loaded databases at my work so my point of view is kinda subjective, but basically anything that can be done outside of the database is done outside since the database is a bottleneck for most applications (and the 'outside' can be parallelized easily).

Here is what we use at work:

| ID | Index 1 | Index 2 | Index 3 | ... | Big Blob of Data |

Basically, the indexes allow for searchability and the 'Big Blob of Data' is under the control of the applications. It is usually compressed serialized (versioned) data (some teams store about 300KB in this blob ;) ).

Of course, this requires a front-end (or library) to actually perform the serialization+compression or decompression+deserialization in a uniform manner.

It works well, ... but then as I said databases are a bottleneck for us so we try to externalize the load as much as possible.

Answer 5

Maybe investigate Object-Relational views.

See these links for starting points...

Oracle 9i Docs - "Applying an Object Model to Relational Data"

Ask Tom - "Relational VS Object Oriented Database Design"

Answer 6

Since you use Oracle, parsing XML in a stored procedure is not a big deal. IMO there are only three sensible ways to go:

• Use DAO on the clients that know the database structure
• Use stored procedure so you have a little bit of abstraction
• Use XML

Any other structured text format (JSON etc.) is inferior, since XML is the format you already have a parser for in Oracle.