External Procedure in DBMS

If we use external procedures, can I execute some code in other language, I want to use some numerical methods libraries to do some matrix computing and store the result in SQL, Oracle or whatever DBMS, Is this possible?

Maybe have a table in SQL, and tell the external code, to process it, I would like this to be the fast as possible.

• How to acommplish this?
• Are ex开发者_开发百科ternal procedures the best choice?

Depending on what "matrix computing" you want to do, you may be able to do it inside Oracle itself (in 10gR2 and later) using the UTL_NA package.

As a general rule.

CASE 
  WHEN "can do it in SQL" THEN SQL
  WHEN "can do it in PL/SQL" THEN PL/SQL
  WHEN "can do it in Java" THEN Java
  ELSE "External Procedure"
END

Generally, "as fast as possible" is a poor target as it results in spending vast quantities of money on the very best hardware (SSDs, high capacity, low latency networking components...). "As fast as possible given a budget of $x" is only slightly more reasonable.

Pick a measurable target, such as 1 million data items processed in 10 minutes. Then take an approach and time it. If it is too slow, then look for the slowest parts (eg disk, network) and eliminate them or optimize them.

Your question does clearly explain what you want to achieve. From your profile I can see that you ask a lot of questions about Matlab so I presume you want to know about using Matlab functionality against a database.

In Oracle an external procedure is a server-side OS routine which we can call from a PL/SQL procedure inside the database. The normal use for this functionality is to incorporate esoteric C functionality into our code. External procedures have become less used in recent years due to the vast increase in the range of built-ins Oracle providss, plus the extensibility we gain from Java Stored Procedures.

The advantage of stored procedures is that they run inside the database, which makes them very efficient at processing large volumes of records. If you want to run some Matlab function againsts shedloads of data then wrapping it as an external procedure may be the right way to go. Basically you need to create a LIBRARY object for the DLL and then create PL/SQL procedures or functions for the required library calls. Find out more.

However, database servers tend to be optimized for certain types of operation, and intense mathematics is not one of them. So if performance is the key criterion then perhaps you would be better off paying the I/O toll and connect to the database through ODBC. Find out more.