Subject: PL/SQL 8.0: Building External C Procedures using the Callback Method
Overview
--------The ability to call external C routines from within procedures and functions
stored in the database is a new feature introduced in Oracle 8.0. This
article contains an example which was created and tested on a Sun Solaris
machine.
Preparation
-----------It is important to note that by using OCI calls it is possible to execute
SQL statements that share the same Oracle transaction as the calling PL/SQL
block. To set this up, however, a number of steps need to be followed. Before
beginning, create your C program with the relevant OCI calls.
Example C Code - emp_count.c
----------------------------/*
* This is a simple OCI callback procedure to return the number of employees
* in the EMP table with the given department number.
*/#include <stdio.h>
#include <string.h>
#include "oci.h"
void rpt_err( OCIExtProcContext *, sword, char * );
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIError *errhp;
int emp_count( OCIExtProcContext *ctx, int dept_num )
{
sword status;
char errmsg[50];
OCIStmt *stmthp = 0;
OCIDefine *def1p = 0;
OCIBind *bnd1p = 0;
text *stmt = (text *)"select count(*) from emp where deptno = :b1";
int num_emps = 0;
/*
* Grab the connection.
*/
status = OCIExtProcGetEnv( (OCIExtProcContext *)ctx, (OCIEnv **)&envhp,
(OCISvcCtx **)&svchp, (OCIError **)&errhp );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Failed to get the connection" );
rpt_err( ctx, status, errmsg );
return -1;
} /*
* Allocate the statement handle, prepare, bind in dept_num, define
* the output variable, execute and fetch.
*/
status = OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&stmthp,
(ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0 );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Failed to allocate statement handle" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIStmtPrepare( (OCIStmt *)stmthp, (OCIError *)errhp,
(CONST text*)stmt, (ub4)strlen( (char *)stmt ),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Prepare failed" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIBindByName( (OCIStmt *)stmthp, (OCIBind **)&bnd1p,
(OCIError *)errhp, (CONST text*)":b1",
(sb4)-1, (dvoid *)&dept_num, (sb4)sizeof(int),
(ub2)SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0,
(ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Bind failed" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIDefineByPos( (OCIStmt *)stmthp, (OCIDefine **)&def1p,
(OCIError *)errhp, (ub4)1, (dvoid *)&num_emps,
(sb4)sizeof(int), (ub2)SQLT_INT,
(dvoid *)0, (ub2 *)0, (ub2 *)0,
(ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Define failed" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIStmtExecute( (OCISvcCtx *)svchp, (OCIStmt *)stmthp,
(OCIError *)errhp, (ub4)1,
(ub4)0, (CONST OCISnapshot *)NULL,
(OCISnapshot *)NULL, (ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Execute and fetch failed" );
rpt_err( ctx, status, errmsg );
return -1;
} /*
* Free up the statement handle only.
*/
OCIHandleFree((dvoid *)stmthp,(ub4)OCI_HTYPE_STMT); /*
* Return the count.
*/
return num_emps;
}
void rpt_err( OCIExtProcContext *ctx, sword status, char *errmsg )
{
text errbuf[450];
text outmsg[512];
ub4 errcode;
strcpy( (char *)errbuf, "" ); if ( status == OCI_ERROR )
{
OCIErrorGet ((dvoid *)errhp,(ub4)1,(text *)NULL,(sb4 *)&errcode,
(text *)errbuf,(ub4)sizeof(errbuf),(ub4)OCI_HTYPE_ERROR);
}
sprintf( (char *)outmsg, "ERROR: %s (%s)", errmsg, (char *)errbuf ); OCIExtProcRaiseExcpWithMsg( (OCIExtProcContext *)ctx, (int)20000,
(text *)outmsg,
(size_t)strlen( (char *)outmsg ) );
}
Procedure
---------1. To call external procedures, you must make certain changes to the
SQL*Net configuration. The first SQL*Net change is to the listener.ora file. Add the
following entry in the SID_LIST_LISTENER section: SID_LIST_LISTENER=
( SID_LIST=
( SID_DESC = (SID_NAME=callout)
(ORACLE_HOME= xxxxxx)
(PROGRAM= yyyyyy)
)
) where 'xxxxxx' is the full pathname of your Oracle Home directory and
'yyyyyy' is the full pathname of your Oracle Home Bin directory plus the
program name which in this case is called 'extproc'. For example: /app/oracle/8.0.5/bin/extproc2. The second change is to the tnsnames.ora file on the server. You must
add an alias for extproc_connection_data (note that the name of this
entry is always extproc_connection_data, regardless of the name of the
external procedure). Add the following entry: extproc_connection_data=
( DESCRIPTION=
( ADDRESS = (PROTOCOL=tcp)(host= xxxx)(port = nnnn))
( CONNECT_DATA=(SID=callout))
) where 'xxxx' is the name of your UNIX host and 'nnnn' is the number of
the port that Net8 is listening on. Note the value of SID must match that
of SID_NAME in the listener.ora entry above. Typically "callout" or
"extproc" is used here.
3. Compile your C code. Either use the demo makefile: make -f demo_rdbms.mk emp_count.o or do: cc -I$ORACLE_HOME/rdbms/demo -I$ORACLE_HOME/rdbms/public \
-I$ORACLE_HOME/plsql/public -I$ORACLE_HOME/network/public \
-c emp_count.c4. Generate a shared library based upon your C object file, e.g.: make -f demo_rdbms.mk extproc_callback \
SHARED_LIBNAME=emps.so OBJS=emp_count.o5. Create an alias library in your database. For example: create or replace library jonslib is 'xxx/xxx/xxx/emps.so' where 'xxx/xxx/xxx/' is the full path of the shared library which
you created above.6. Create an external function/procedure in your database to publish the
C routine. For example: CREATE OR REPLACE FUNCTION count_emps (n BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY jonslib
NAME "emp_count"
LANGUAGE C
WITH CONTEXT
PARAMETERS (CONTEXT,
n int);7. Test the external function/procedure. E.g. run the following PL/SQL block: DECLARE
c NUMBER;
BEGIN
c := count_emps(20);
DBMS_OUTPUT.PUT_LINE('Number of employees in Dept 20 is '||c);
END;
Overview
--------The ability to call external C routines from within procedures and functions
stored in the database is a new feature introduced in Oracle 8.0. This
article contains an example which was created and tested on a Sun Solaris
machine.
Preparation
-----------It is important to note that by using OCI calls it is possible to execute
SQL statements that share the same Oracle transaction as the calling PL/SQL
block. To set this up, however, a number of steps need to be followed. Before
beginning, create your C program with the relevant OCI calls.
Example C Code - emp_count.c
----------------------------/*
* This is a simple OCI callback procedure to return the number of employees
* in the EMP table with the given department number.
*/#include <stdio.h>
#include <string.h>
#include "oci.h"
void rpt_err( OCIExtProcContext *, sword, char * );
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIError *errhp;
int emp_count( OCIExtProcContext *ctx, int dept_num )
{
sword status;
char errmsg[50];
OCIStmt *stmthp = 0;
OCIDefine *def1p = 0;
OCIBind *bnd1p = 0;
text *stmt = (text *)"select count(*) from emp where deptno = :b1";
int num_emps = 0;
/*
* Grab the connection.
*/
status = OCIExtProcGetEnv( (OCIExtProcContext *)ctx, (OCIEnv **)&envhp,
(OCISvcCtx **)&svchp, (OCIError **)&errhp );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Failed to get the connection" );
rpt_err( ctx, status, errmsg );
return -1;
} /*
* Allocate the statement handle, prepare, bind in dept_num, define
* the output variable, execute and fetch.
*/
status = OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&stmthp,
(ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0 );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Failed to allocate statement handle" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIStmtPrepare( (OCIStmt *)stmthp, (OCIError *)errhp,
(CONST text*)stmt, (ub4)strlen( (char *)stmt ),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Prepare failed" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIBindByName( (OCIStmt *)stmthp, (OCIBind **)&bnd1p,
(OCIError *)errhp, (CONST text*)":b1",
(sb4)-1, (dvoid *)&dept_num, (sb4)sizeof(int),
(ub2)SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0,
(ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Bind failed" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIDefineByPos( (OCIStmt *)stmthp, (OCIDefine **)&def1p,
(OCIError *)errhp, (ub4)1, (dvoid *)&num_emps,
(sb4)sizeof(int), (ub2)SQLT_INT,
(dvoid *)0, (ub2 *)0, (ub2 *)0,
(ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Define failed" );
rpt_err( ctx, status, errmsg );
return -1;
} status = OCIStmtExecute( (OCISvcCtx *)svchp, (OCIStmt *)stmthp,
(OCIError *)errhp, (ub4)1,
(ub4)0, (CONST OCISnapshot *)NULL,
(OCISnapshot *)NULL, (ub4)OCI_DEFAULT );
if ( status != OCI_SUCCESS )
{
strcpy( errmsg, "Execute and fetch failed" );
rpt_err( ctx, status, errmsg );
return -1;
} /*
* Free up the statement handle only.
*/
OCIHandleFree((dvoid *)stmthp,(ub4)OCI_HTYPE_STMT); /*
* Return the count.
*/
return num_emps;
}
void rpt_err( OCIExtProcContext *ctx, sword status, char *errmsg )
{
text errbuf[450];
text outmsg[512];
ub4 errcode;
strcpy( (char *)errbuf, "" ); if ( status == OCI_ERROR )
{
OCIErrorGet ((dvoid *)errhp,(ub4)1,(text *)NULL,(sb4 *)&errcode,
(text *)errbuf,(ub4)sizeof(errbuf),(ub4)OCI_HTYPE_ERROR);
}
sprintf( (char *)outmsg, "ERROR: %s (%s)", errmsg, (char *)errbuf ); OCIExtProcRaiseExcpWithMsg( (OCIExtProcContext *)ctx, (int)20000,
(text *)outmsg,
(size_t)strlen( (char *)outmsg ) );
}
Procedure
---------1. To call external procedures, you must make certain changes to the
SQL*Net configuration. The first SQL*Net change is to the listener.ora file. Add the
following entry in the SID_LIST_LISTENER section: SID_LIST_LISTENER=
( SID_LIST=
( SID_DESC = (SID_NAME=callout)
(ORACLE_HOME= xxxxxx)
(PROGRAM= yyyyyy)
)
) where 'xxxxxx' is the full pathname of your Oracle Home directory and
'yyyyyy' is the full pathname of your Oracle Home Bin directory plus the
program name which in this case is called 'extproc'. For example: /app/oracle/8.0.5/bin/extproc2. The second change is to the tnsnames.ora file on the server. You must
add an alias for extproc_connection_data (note that the name of this
entry is always extproc_connection_data, regardless of the name of the
external procedure). Add the following entry: extproc_connection_data=
( DESCRIPTION=
( ADDRESS = (PROTOCOL=tcp)(host= xxxx)(port = nnnn))
( CONNECT_DATA=(SID=callout))
) where 'xxxx' is the name of your UNIX host and 'nnnn' is the number of
the port that Net8 is listening on. Note the value of SID must match that
of SID_NAME in the listener.ora entry above. Typically "callout" or
"extproc" is used here.
3. Compile your C code. Either use the demo makefile: make -f demo_rdbms.mk emp_count.o or do: cc -I$ORACLE_HOME/rdbms/demo -I$ORACLE_HOME/rdbms/public \
-I$ORACLE_HOME/plsql/public -I$ORACLE_HOME/network/public \
-c emp_count.c4. Generate a shared library based upon your C object file, e.g.: make -f demo_rdbms.mk extproc_callback \
SHARED_LIBNAME=emps.so OBJS=emp_count.o5. Create an alias library in your database. For example: create or replace library jonslib is 'xxx/xxx/xxx/emps.so' where 'xxx/xxx/xxx/' is the full path of the shared library which
you created above.6. Create an external function/procedure in your database to publish the
C routine. For example: CREATE OR REPLACE FUNCTION count_emps (n BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY jonslib
NAME "emp_count"
LANGUAGE C
WITH CONTEXT
PARAMETERS (CONTEXT,
n int);7. Test the external function/procedure. E.g. run the following PL/SQL block: DECLARE
c NUMBER;
BEGIN
c := count_emps(20);
DBMS_OUTPUT.PUT_LINE('Number of employees in Dept 20 is '||c);
END;
解决方案 »
- jobs 手动运行可以 自动运行就会报错、断开。怎么解决啊！
- 安装oracle失败提示如下
- （100分）oracle9i企业管理器没有了
- 一条修改语句，要修改的字段：[金币] yxb，[经验] Exp [最大回合数] maximumNumberOfRound 如果最大回合数比数据库大就替代
- VC嵌入proc编程访问Oracle数据库问题
- 请帮忙看看这个函数有什么问题？谢谢
- 关于SQL合并字段的问题。。。急！！！
- 求助：我安装oracle 9i时点击setup.exe没有反应是怎么回事啊？？？
- 类型定义问题，请各位帮忙！
- 带条件的联合查询，数据库是先联合两个表，还是先用条件过滤再联合两个表？
- 一个典型的关于ORACLE数据库中大小写的问题。
- 有谁有国产数据库如IBASE、OPENBASE的开发、使用经验。建议 CSDN。NET增加国产数据库讨论区
谢谢
2、ORACLE安装路径为：C:\ORANT
3、服务器A、服务器B，均装有NT 4.0中文版实现方法：
1. 假设A地址192.1.1.1，B地址192.1.1.22. A、B上配置好TCP/IP，互相Ping通。3. 配置init.ora文件，若global_name = true的话，database link 的名字必须同远程机的实例名相同， 为简便起见，请将global_name 设为 false。4. 在服务器上配置tnsnames.ora，将Remote机器的地址(IP)信息加入本地的tnsnames.ora A服务器：
TNSA_B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.1.1.2)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
) B服务器：
TNSB_A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.1.1.1)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)5. 在 SQL*Plus 或其它工具中创建数据库链接 A服务器：create public database link A_TO_B connect to tmp identified by tmp using 'TNSA_B'; B服务器：create public database link B_TO_A connect to tmp identified by tmp using 'TNSB_A'; 说明：
tmp是一个临时用户，A服务器、B服务器上均有，它的作用是提供链接的目的地，
假如：
B服务器上有user1、user2、tmp三个用户，user1和user2把他们想要对外公开的表的权限授给tmp用户，
那么，所有能通过database link连接到tmp用户上的人就可以直接访问user1、user2上的已授权表了。6. 建立database link以后，请用这种格式select * from table_name@database_link_name 的方式访问 如：在A服务器上想访问B服务器上user1用户table1表的内容（A到B的连接为A_TO_B），则 SQL> select * from table1@A_TO_B;7. 如果Oracle版本为7.3，则数据库联接写法如下： A服务器：create public database link A_TO_B connect to tmp identified by tmp using 't:192.1.1.2:orcl'; B服务器：create public database link B_TO_A connect to tmp identified by tmp using 't:192.1.1.1:orcl';