Clever Geek Handbook

What is the best (fastest, easiest and most efficient) way to transfer structured data to and from Oracle

I want to abstract a large database storage (relational, OLTP) from the application layer if the application is treating the database as a service. The natural way to do this is to call sprocs, but traditionally they follow the CRUD paradigm and according to my abstractive thinking, I want to divert all knowledge of the data structures in the database and concentrate on the business process.

So, instead of having a Save Account business process, it would be ...

  • Start a transaction
  • Create invoice header
  • For invoice items
    • Create invoice items
  • Commit Transaction

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

I can transfer an XML document containing an invoice, but this I want to avoid on the database side:

  • XML parsing
  • XML validation
  • Dropping and Binding Parameters to Oracle PL / SQL Objects

Off course, in all cases, regardless of the decision, it must be done. However, I do not want to pay the XML document penalty (corner bracket tax).

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

I would love to hear from anyone who wants to argue about JSON, ATOM or other formats.

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

+2


source to share


6 answers


Since you are using Oracle, parsing the XML in the stored procedure doesn't really matter. IMO there are only three reasonable paths:

  • Use DAO for clients who know the structure of the database.
  • Use a stored procedure to have a bit of abstraction
  • Use XML


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

+1


source


You can create a collection, populate it on the client side, pass it to a procedure, Oracle

and perform customization operations on it:



INSERT
INTO    dest_table
SELECT  *
FROM    TABLE(:mycollection)
0


source


It depends. You can store data in any form as a large object. The main reason no one else does this is because you are storing data in a database so you can search for it. The internet was a good idea until Google got close and could find things.

So, you have to parse the data in some way. You can parse it on the client and send the SQL insert / updates. If you do this with your favorite OO language, you have an OR mapper (which can load and store "objects" in simple SQL tables). So the hard work (parsing) is done on many clients and the database just stores and searches for the data.

0


source


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

What is the DAO layer for. For application code, DAO is a persistent layer (service on your terms). The DAO knows how to store a structured document.

I am assuming you have to execute SQL queries against the stored documents.

While there is an Oracle-specific XML format, I would refrain from using it as it ties your code to Oracle. While standard SQL works, use this.

0


source


I don't understand why I would like to do something like "parsing" and "checking" in the database.

Perhaps we use very busy databases in my work, so my point of view is subjective, but basically everything that can be done outside the database is done outside, since the database is a bottleneck for most applications (and "outside" can be easily parallelized).

Here's what we use at work:

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

Basically, indexes allow you to search for search capabilities, and the "Big Block" is under application control. Usually this is compressed serialized (versioned) data (some commands store about 300KB in this block;)).

Of course, this requires the interface (or library) to actually do serialization + compression or decompression + deserialization in a uniform manner.

This works well ... but then since I said that databases are a bottleneck for us, so we try to keep the load as simple as possible.

0


source


It is possible to explore object-relational views.

See these links for starting points ...

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

Ask Tom - "Relational VS Object Oriented Database Design"

0


source






More articles:


All Articles