The following configuration works for me on Oracle 11.2.0.2:
C prog:
[oracle@node1 ~]$ cat exec_hmmftg.c
#include <stdio.h>
int exec_hmmftg ( char *p1 , char *p2, char *p3 )
{
return 5;
}
[oracle@node1 ~]$
Compile it, and make sure it's placed in $ORACLE_HOME/bin
or $ORACLE_HOME/lib
(configuration can be changed to allow other directories) - compiler flags will differ for AIX too.
gcc -shared -o $ORACLE_HOME/lib/exec_hmmftg.so exec_hmmftg.c
Configure EXTPROC
in the listener.ora
file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.6)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PHIL112)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
(SID_NAME = PHIL112)
)
)
Restart the listener with lsnrctl stop
followed by lsnrctl start
.
Create the reference to the external library & create the function to interface between PL/SQL and the C prog:
[oracle@node1 ~]$ sqlplus phil/phil
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 14 17:51:21 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE LIBRARY CPROGLIB UNTRUSTED AS '/u01/app/oracle/product/11.2.0/dbhome_1/lib/exec_hmmftg.so ';
2 /
Library created.
SQL> CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1 IN VARCHAR2, P2 IN VARCHAR2, P3 IN VARCHAR2)
2 RETURN BINARY_INTEGER
3 IS EXTERNAL NAME "exec_hmmftg"
4 LIBRARY CPROGLIB
5 LANGUAGE C
6 PARAMETERS(P1 STRING, P2 STRING, P3 STRING, RETURN INT);
7 /
Function created.
SQL>
Test the function:
SQL> select call_c_program('a','b','c') from dual;
CALL_C_PROGRAM('A','B','C')
---------------------------
5
SQL>
Not quite sure what you're doing wrong without seeing the full C you've written. Try and setup this simple case I've made for you, then add functionality bit by bit from there.
In the case of purely mathematical circumstances, it is my understanding that the premise of a function f
calling a volatile
function g
would indicate that f
is inherently volatile
as well. That's because functions in mathematics are equivalencies designed to reduce the work of explaining systems and the one (function or expression) can be substituted for the other, often as syntactic sugar moreso than anything else.
However, my answer isn't specifically from the context of postgres, as I don't normally work in PG. It may be reasonable in PG that the f
function would be stable
even while the g
function were volatile
.
I would encourage every developer to examine their code as being basically just mathematics exposed, and so therefore would encourage you to consider the code here as volatile
.
Best Answer
The documentation describes
IMMUTABLE
asThis means you can do the following:
random()
is a volatile function, andimmutablerandom()
fulfills the criteria of the definition ofIMMUTABLE
. If this actually makes sense is a different thing - you have to discard the results of the volatile function altogether, and in practice I cannot see a case where I could use it.