c执行oracle函数吗,oracle二次开发调用外部C函数。

在编程过程中，我们可能会发现有些功能通过PL/SQL完成会很麻烦，而通过C/C++语言编程则会容易很多。因此，Oracle提供了在PL/SQL程序里直接调用外部函数。

oracle根据别名库，寻找C函数的文件(DLL文件)，而我们若想调用C的DLL，需要配置oracle服务器端的安装目录C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora下的listener.ora和tnsnames.ora两个文件。只需分别在两个文件中添加PLSExtProc进程即可。

配置后的文件listener.ora内容如下：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = “EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll”)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

)

ADR_BASE_LISTENER = C:\app\Administrator

配置后的tnsnames.ora文件内容如下：

LISTENER_ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

(CONNECT_DATA =

(SID = CLRExtProc)

(PRESENTATION = RO)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

(CONNECT_DATA =

(SID = PLSExtProc)

)

)

若是没有成功，注意两文件中内容格式，及等号两边的空格。(似乎oracle对这两个文件的格式要求特别严格)

重启监听， 可查看添加的PLSExtProc是否成功。

创建C的DLL，

define SS_ORCLE_EXTEND_API extern “C” __declspec(dllexport)

SS_ORCLE_EXTEND_API int GetModelCount(OCILobLocator *lob, short lbind, OCIExtProcContext *ctxt );

SS_ORCLE_EXTEND_API char* GetModelName(OCILobLocator *lob, short lbind, OCIExtProcContext *ctxt );

生成C的dll拷贝至安装目录的product\11.2.0\dbhome_1\BIN文件夹下。

由于number varchar2等字段类型比较简单，此处不做代码样例。而oracle的BLOB类型，需通过OCILobLocator lob, short lbind 内容和长度参数传递。通过oci，转为C的二进制。unsigned char 和长度。

sword errnum = 0;

OCIEnv *envhp = NULL;

OCISvcCtx *svchp = NULL;

OCIError *errhp = NULL;

/*

* Retrieve the environment, service context, and error handles

*/

if ((errnum = OCIExtProcGetEnv(ctxt, &envhp, &svchp, &errhp)) != OCIEXTPROC_SUCCESS)

{

return -1;

}

ub1 csfrm;

ub4 amount;

int r;

r = OCILobOpen( svchp, errhp, lob , (ub1)OCI_FILE_READONLY);

if (r != OCI_SUCCESS)

{

return r;

}

// Read this from the database, don’t assume we know what it is set to

r = OCILobCharSetForm(envhp, errhp, lob, &csfrm);

if (r != OCI_SUCCESS)

{

csfrm = 0;

}

// Get the length of the LOB (this is in characters)

r = OCILobGetLength(svchp,errhp, lob, &amount);

if (r == OCI_SUCCESS)

{

if (amount == 0)

{

// Short cut for null LOBs

return -1;

}

}

else

{

return r;

}

// Resize the buffer to hold the LOB contents

unsigned char* buf = new unsigned char[amount];

int sz = sizeof(char);

// Read the LOB into the buffer

r = OCILobRead(svchp,

errhp,

lob,

&amount,

1,

(void*)buf,

amount * sz, // this argument is in bytes, not characters

0,

0,

// Extract the data from a CLOB in UTF-16 (ie. what QString uses internally)

sz == 1 ? ub2(0) : ub2(2002),

csfrm);

if (r != OCI_SUCCESS)

{

}

r = OCILobClose(svchp, errhp, lob);

if (r != OCI_SUCCESS)

{

return -1;

}

即可对oracle的二进制进行处理。

DLL拷贝至BIN目录下后，创建oracle的library ，若当前用户无创建权限，需为此用户分配创建library权限。

create or replace library MESHTEST as ‘C:\app\Administrator\product\11.2.0\dbhome_1\BIN\OracleExtend.dll’;

创建oracle内部函数

create or replace function meshnum(a_lob BLOB ) return binary_integer as language C library MESHTEST name “GetModelCount” with context parameters(a_lob OCILOBLOCATOR,a_lob INDICATOR SHORT,CONTEXT,return int);

create or replace function meshname(a_lob BLOB ) return varchar2 as language C library MESHTEST name “GetModelName” with context parameters(a_lob OCILOBLOCATOR,a_lob INDICATOR SHORT,CONTEXT);

把二进制入到oracle库中的表blocks_3d_model_lt的filecontent字段内。

利用sql语句测试select meshname(filecontent) from blocks_3d_model_lt 结果如下：

下一篇，着重讲解更为复杂的C函数，以及参数的相互对照。