Oracle 11g – Using C/C++ for Functions or Procedures

functionsoracleoracle-11g-r2postgresqlstored-procedures

I want to create a function called by a trigger on oracle but in c or c++. Something like in PostgreSQL: Audit trigger in C.

Or, in any case, is there support for this but in other languages like Python?

Thanks!

Best Answer

Oracle database supports Java and external C calls. They can be used as wrappers to call programs written in other languages such as Python.

Publishing External Procedures

Oracle Database can only use external procedures that are published through a call specification, which maps names, parameter types, and return types for your Java class method or C external procedure to their SQL counterparts.

Create a sample program:

$ cat /home/oracle/1.c
char* myfunc()
{
   return "Hello World!";
}

$ gcc -shared -o /home/oracle/1.so -fPIC /home/oracle/1.c

In the database, create a library and a function using it:

create or replace library lib_myfunc is '/home/oracle/1.so';
/

create or replace function myfunc
return varchar2 as external
name "myfunc"
library lib_myfunc
language c;
/

Enable the use of this library in $ORACLE_HOME/hs/admin/extproc.ora:

SET EXTPROC_DLLS=/home/oracle/1.so

Then finally use it as:

SQL> select myfunc from dual;

MYFUNC
------------
Hello World!

More about this:

Developing Applications with Multiple Programming Languages

Related Question