oracle external procedure calls Теодор Иванов Софтуерен инженер
Post on 27-Dec-2015
246 views
TRANSCRIPT
Oracle External Procedure Calls
Теодор Иванов Софтуерен инженер
2
Oracle Documentationhttp://www.oracle.com/technology/documentation/database10gr2.html
Oracle Course
Oracle Database 10g: Advanced PL/SQLhttp://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D17220GC10&p_org_id=1001&lang=US&source_call=
Помощ от приятел …
Как да се научим да използваме EPC
3
Моят подход ...
Oracle® Database SQL Reference 10g Release 1 (10.1),Part No. B10759-01
PL/SQL User's Guide and Reference 10g Release 1 (10.1),Part No. B10807-01
Oracle® Data Cartridge Developer's Guide 10g Release 1 (10.1), Part No. B10800-01
5. Implementing Data Cartridges in C, C++ and Java
4
Моят подход ...
Oracle® Database Application Developer's Guide –
Fundamentals 10g Release 1 (10.1), Part Number B10795-01
1 Programmatic Environments
Overview of Pro*C/C++
Overview of OCI and OCCI
Choosing a Programming Environment
8 Calling External Procedures
5
Моят подход ...
Oracle® Call Interface - Programmer's Guide
10g Release 1 (10.1), Part No. B10779-01
1. Introduction and Upgrading
2. OCI Programming Basics
3. Datatypes
18. OCI Datatype Mapping and Manipulation Functions
19. OCI Cartridge Functions
6
Какво е Oracle EPC ?
Начин да се използват функции от външни за
Oracle RDBMS библиотеки ( .lib, .so, .dll )
- SQL- PL/SQL - Java
Oracle RDBMS
C interface
Java interface
C proceduresor functions
Java proceduresor functions
7
Кога се използва Oracle EPC ?
Липса на функционалност в Oracle RDBMS илинедостатачна ефективност на PL/SQL:
Многократни сметки с плаваща запетая и работа с едномерни и многомерни масиви и указатели
Описание или пресмятания, свързани със сложни информационни обекти – графи, матрици, и т.н.
Използване на вече готови библиотеки
Пълноценнo използване възможностите на операционната система
8
Как работи Oracle EPC ?
Oracle RDBMS
begin res := a_plus_b( a, b );end;/
PL/SQL
SQL> select a_plus_b( a, b )from dual;
SQL
PL/SQL function a_plus_b(…), sum_lib
OCI network libraries, using tnsnames.ora
Oracle Listener process LISTENER using listener.ora
extproc agent$ORACLE_HOME/bin/extproc
Library sum_lib, sum.so EXTPROC_CONNECTION_DATA
using: EPC_IPC_KEYlooking for: EPC_SID
using: EPC_SIDexecuting: extproc agent
using: OS .so loadcalls: sum.so/a_plus_b_c()
OScreate or replace function a_plus_b(
a in binary_integer,
b in binary_integer
) return binary_integer
is language C
library sum_lib
name "a_plus_b_c"
parameters(
a int,
b int,
return int
);
/
tnsnames.ora
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY))
)
(CONNECT_DATA =
(SID = EPC_SID)
)
)
create or replace library sum_lib
as '${ORACLE_HOME}/lib/sum.so';
/
listener.ora
SID_LIST_LISTENER =
(SID_LIST = ...
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = EPC_SID)
(ORACLE_HOME = /oracle/10.1)
(ENVS = "EXTPROC_DLLS=ANY")
)
)
LISTENER =
(DESCRIPTION_LIST = ...
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY))
)
)
$ORACLE_HOME/lib/sum.so a_plus_b_c() { … }
9
Как работи Oracle EPC ? - 2
Oracle RDBMS
begin res := a_plus_b( a, b );end;/
PL/SQL
SQL> select a_plus_b( a, b )from dual;
SQL
PL/SQL function a_plus_b(…), sum_lib
OCI network libraries, using tnsnames.ora
Oracle Listener process LISTENER using listener.ora
extproc agent$ORACLE_HOME/bin/extproc
Library sum_lib, sum.so EXTPROC_CONNECTION_DATA
using: EPC_IPC_KEYlooking for: EPC_SID
using: EPC_SIDexecuting: extproc agent
using: OS .so loadcalls: sum.so/a_plus_b_c()
OS$ORACLE_HOME/lib/sum.so a_plus_b_c() { … }
10
Пример за EPC
С функция epc_demo_func_c() и Linux библиотека epc_demo.so C Тест на функцията epc_demo_func_cСъдържание на tnsnames.oraСъдържание на sqlnet.oraСъдържание на listener.oraOracle библиотека epc_demo_libPL/SQL функция epc_demo_func()Тест на PL/SQL функцията epc_demo_func()makefile
11
Какво прави epc_demo_func()
Приема два параметара:
a - целочислено число
s - символен низ ( стринг )
Ако а е NULL, тогава epc_demo_func() връща 0
Ако а не е NULL, тогава разпечатва стойностите на всички входящи за функцията параметри в стринг buff и връща стойността на buff в s, а epc_demo_func() връща стойността на a
12
PL/SQL библиотека и функция - epc_demo_func.sql
create or replace library epc_demo_lib as '${ORACLE_HOME}/lib/epc_demo.so';/
create or replace function epc_demo_func( a in pls_integer, s in out varchar2 ) return pls_integer is language C library epc_demo_lib name "epc_demo_func_c" parameters( a int, a indicator, s string, s length by reference int, s maxlen by reference int, return int );/
exit
при wrap на библиотеката, не можеда се използва обращение към
shell environment variables
13
Съответствие на PL/SQL и C параметрите
PL/SQL PARAMETERS C
epc_demo_func( parameters( int epc_demo_func_c(
a in pls_integer,a int,
a indicator,
int a,
short a_ind,
s in out varchar2
s string,
s length by reference int,
s maxlen by reference int,
char *s,
int *s_length,
int *s_maxlen
) return pls_integer return int
)
)
14
С функция - epc_demo_func_c.c#include <stdio.h>#include <string.h>#include "oci.h"
int epc_demo_func_c( int a, short a_ind, char *s, int *s_length, int *s_maxlen ){ // if a is NULL then return 0 if( a_ind == OCI_IND_NULL ) return( 0 );
продължава на следващият слайд ...
15
продължение от предишният слайд ...
// prepare the new return value for s char buff[200]; sprintf( buff, "%s\na=%d, a_ind=%d, s='%s', s_length=%d, s_maxlen=%d", "C func epc_demo_func_c()\nreceived the following input paramters:", a, a_ind, s, *s_length, *s_maxlen );
// copy the prepared value into y strncpy( s, buff, *s_maxlen );
// set new s length *s_length = strlen( s );
// return the value of a return( a );}
16
Тест на С функцията - epc_demo_func_c.c#ifdef TEST
int main( int argc, char *argv[] ){ char ss[200] = { "123" }; int aa = -7, res = 0, ss_len = strlen( ss ), ss_maxl = sizeof( ss ) - 1;
printf( "\nC test: begins\n\n" ); printf( "C test main(): will call C epc_demo_func_c() with
parameters:\n" ); printf( "C test main(): not NULL a = %d\n", aa ); printf( "C test main(): and s = >>>%s<<<\n\n", ss );
res = epc_demo_func_c( aa, OCI_IND_NOTNULL, ss, &ss_len, &ss_maxl );
продължава на следващият слайд ...
17
продължение от предишният слайд ...
printf( "C test main(): epc_demo_func_c() returns %d\n", res ); printf( "C test main(): and resulted s is >>>%s<<<\n", ss ); printf( "C test main(): and resulted s length is %d\n", ss_len ); printf( "\nC test: ends\n\n" );
return( 0 );}
#endif
18
Тест на PL/SQL функцията - epc_demo_func_test.sql
set serveroutput ondeclare aa pls_integer := -7; ss varchar2( 200 ) := '123'; res pls_integer := 0;begin dbms_output.enable; dbms_output.put_line( chr( 10 ) || 'PL/SQL test: begins' || chr( 10 ) ); dbms_output.put_line( 'PL/SQL test: will call PL/SQL epc_demo_func() ' || || 'with parameters' ); dbms_output.put_line( 'PL/SQL test: not NULL a = ' || aa ); dbms_output.put_line( 'PL/SQL test: s = >>>' || ss || '<<<' || chr( 10 ) ); res := epc_demo_func( aa, ss ); dbms_output.put_line( 'PL/SQL test: epc_demo_func() returns ' || res ); dbms_output.put_line( 'PL/SQL test: and resulted s is >>>' || ss || '<<<' ); dbms_output.put_line( chr( 10 ) || 'PL/SQL test: ends' || chr( 10 ) );end;/
19
Редактиране на tnsnames.ora
Файлът се намира в $TNS_ADMINили в $ОRACLE_HOME/network/admin
EXTPROC_CONNECTION_DATA[.default_domain] = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY)) ) (CONNECT_DATA = (SID = EPC_SID) ) )
20
[.default_domain]
Трябва да съответствува на стойността на:
NAMES.DEFAULT_DOMAIN = semantec.bg
от файла sqlnet.ora
21
Редактиране на listener.oraСпиране на лисънъра, редактиране на listener.ora истартиране на лисънъра
SID_LIST_LISTENER = (SID_LIST = ... (SID_DESC = (PROGRAM = extproc) (SID_NAME = EPC_SID) (ORACLE_HOME = /oracle/10.1) (ENVS = "EXTPROC_DLLS=ANY") ) )
LISTENER = (DESCRIPTION_LIST = ... (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY)) ) )
тези стойности трябва да съответстват на маркираните в зелено от tnsnames.ora
22
Тест на редактираните *.ora файловете[oracle@didcos42 ~]$ tnsping EXTPROC_CONNECTION_DATA.SEMANTEC.BG
TNS Ping Utility for Linux: Version 10.1.0.4.0 - Production on 24-OCT-2006 20:59:47
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/oracle/10.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY))) (CONNECT_DATA = (SID = EPC_SID)))
OK (10 msec)
[oracle@didcos42 ~]$ tnsping EXTPROC_CONNECTION_DATA
TNS Ping Utility for Linux: Version 10.1.0.4.0 - Production on 24-OCT-2006 20:59:51
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/oracle/10.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EPC_IPC_KEY))) (CONNECT_DATA = (SID = EPC_SID)))
OK (10 msec)
23
makefileall: so test sql
so ${ORACLE_HOME}/lib/epc_demo.so: epc_demo_func_c.c gcc -shared -o epc_demo.so epc_demo_func_c.c cp epc_demo.so ${ORACLE_HOME}/lib chmod 0755 ${ORACLE_HOME}/lib/epc_demo.so ls -l ${ORACLE_HOME}/lib/epc_demo.so
test: epc_demo_func_c.c gcc -o epc_demo_func_c epc_demo_func_c.c -D TEST ls -l epc_demo_func_c ./epc_demo_func_c
sql: epc_demo_func.sql ${ORACLE_HOME}/lib/epc_demo.so sqlplus -S di1/x @epc_demo_func.sql sqlplus -S di1/x @epc_demo_func_test.sql
24
[oracle@didcos42 epc_demo]$ ls -l
total 16
-rw-r--r-- 1 oracle users 449 Oct 23 19:58 epc_demo_func.sql
-rw-r--r-- 1 oracle users 793 Oct 23 20:22 epc_demo_func_c.c
-rw-r--r-- 1 oracle users 326 Oct 23 20:07 epc_demo_func_test.sql
-rw-r--r-- 1 oracle users 456 Oct 23 20:57 makefile
[oracle@didcos42 epc_demo]$
Файловете от примера за EPC
25
make test[oracle@didcos42 epc_demo]$
[oracle@didcos42 epc_demo]$
./epc_demo_func_c
C test: begins
C test main(): will call C epc_demo_func_c() with parameters:C test main(): not NULL a = -7C test main(): and s = >>>123<<<
C test main(): epc_demo_func_c() returns -7C test main(): and resulted s is >>>C func epc_demo_func_c()received the following input paramters:a=-7, a_ind=0, s='123', s_length=3, s_maxlen=199<<<C test main(): and resulted s length is 113
C test: ends
gcc -o epc_demo_func_c -I /oracle/10.1/rdbms/public -D TEST epc_demo_func_c.c ls -l epc_demo_func_c-rwxr-xr-x 1 oracle users 6175 Oct 25 18:59 epc_demo_func_c
26
make sql[oracle@didcos42 epc_demo]$
[oracle@didcos42 epc_demo]$
sqlplus -S di1/x @epc_demo_func_test.sqlPL/SQL test: begins
PL/SQL test: will call PL/SQL epc_demo_func() with parametersPL/SQL test: not NULL a = -7PL/SQL test: s = >>>123<<<
PL/SQL test: epc_demo_func() returns -7PL/SQL test: and resulted s is >>>C func epc_demo_func_c()received thefollowing input paramters:a=-7, a_ind=0, s='123', s_length=3, s_maxlen=200<<<
PL/SQL test: endsPL/SQL procedure successfully completed.
sqlplus -S di1/x @epc_demo_func.sqlLibrary created.Function created.
27
Дебъгване на EPC
Oracle RDBMS
SQL> select a_plus_b( 2, 3 ) from dual; <Enter>…
SQL>
SQL*Plus extproc agent$ORACLE_HOME/bin/extproc
using: OS .so loadcalls: sum.so/a_plus_b_c()
OS$ORACLE_HOME/lib/sum.so a_plus_b_c() { … }
(gdb) attach 9380 <Enter>Attaching to process 9380…(gdb) break pextproc <Enter>Breakpoint 1 at 0xb7d77782(gdb)
select function_for_debugging(…)from dual; <Enter>
[Switching to Thread -1225242944 (LWP 9380)]Breakpoint 1, 0xb7d77782 in pextproc () from /…/libagtsh.so(gdb) (gdb) break function_for_debugging <Enter>Breakpoint 2 at 0xb6e9e564(gdb)
Debugger - gdb, ddd !ps -ef | grep extproc <Enter>…oracle 9380 ... extprocEPC_SID (LOCAL=NO)…
SQL>(gdb) cont <Enter>Continuing.
(gdb) cont <Enter>Continuing.Breakpoint 2, 0xb6e9e564 in function_for_debugging () from /…/sum.so(gdb) _
[oracle@didcos42 ~]$ ps -ef | grep extprocUID PID PPID C STIME TTY TIME CMDoracle 9380 1 0 15:32 ? 00:00:00 extprocEPC_SID (LOCAL=NO)oracle 15776 15670 0 15:33 pts/1 00:00:00 grep extproc[oracle@didcos42 ~]$
28
Допълнителна информация на EPC
Синтаксис на 'parameters'
Предизвикване на exception от EPC
Памет, указатели, стойности, променливи
Предаване на масиви като параметри
Изпълнение на DDL и DML от EPC
29
Синтаксис на 'parameters'PARAMETERS (external_parameter[, external_parameter]...)
external_parameter stands for:{ CONTEXT |
SELF [{TDO | property}] | {parameter_name | RETURN} [property] [BY REFERENCE] [external_datatype] }
property stands for:{ INDICATOR [{STRUCT | TDO}] | LENGTH | DURATION | MAXLEN | CHARSETID | -- OCI_ATTR_CHARSET_ID CHARSETFORM -- OCI_ATTR_CHARSET_FORM}
30
Съответствие на типовете PL/SQL – C
PL/SQL data type PARAMETERS ext.data type C data type
BOOLEAN,
BINARY_INTEGER,
PLS_INTEGER
[UNSIGNED] CHAR
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
[unsigned] char
[unsigned] short
[unsigned] int
[unsigned] long
CHAR, LONG,
VARCHAR2
STRING
OCISTRING
char *
OCIString *
BLOB, CLOB OCILOBLOCATOR OCILobLocator *
NUMBER OCINUMBER OCINumber *
DATE OCIDATE OCIDate *
VARRAY, TABLE OCICOLL OCITable *
CONTEXT OCIExtProcContext *
31
Съответствие на типоветеPARAMETERS Property – C
Property Allowed ext.data type C data type
INDICATOR SHORT short
LENGTH,
MAXLEN
[UNSIGNED] SHORT
[UNSIGNED] INT
[UNSIGNED] LONG
[unsigned] short
int
[unsigned] long
32
Предизвикване на exception от EPC
CREATE OR REPLACE PROCEDURE plsTo_divide_proc (
dividend IN BINARY_INTEGER,divisor IN BINARY_INTEGER,result OUT FLOAT)
AS LANGUAGE CNAME "C_divide"LIBRARY MathLibWITH CONTEXTPARAMETERS (
CONTEXT,dividend INT,divisor INT,result FLOAT);
/
33
void C_divide (
OCIExtProcContext *ctx;
int dividend;
int divisor;
float *result
)
{
/* Check for zero divisor. */
if( divisor == (int)0 ) {
/* Raise exception ZERO_DIVIDE, which is Oracle error 1476. */
if( OCIExtProcRaiseExcp( ctx, (int)1476 ) == OCIEXTPROC_SUCCESS )
return;
else
assert( 0 ); /* Incorrect parameters were passed. */
}
*result = (float)dividend / (float)divisor;
}
34
void C_divide (
OCIExtProcContext *ctx;
int dividend;
int divisor;
float *result
)
{
/* Check for zero divisor. */
if( divisor == (int)0 ) {
/* Raise exception 20100, which is user defined */
if( OCIExtProcRaiseExcpWithMsg( ctx, (int)20100, "divisor is zero", 0 )
== OCIEXTPROC_SUCCESS )
return;
else
assert( 0 ); /* Incorrect parameters were passed. */
}
*result = (float)dividend / (float)divisor;
}
35
Памет, указатели, стойности, променливи
Области на действие на променливите и използване
на стойностите им
Необходимост от OCIExtProcAllocCallMemory
Начин за заобикаляне необходимостта от
OCIExtProcAllocCallMemory
36
С функция - epc_demo_func_c()int global_definition = 0;
int epc_demo_func_c( ...
){
char buf[200];static char large_buf[10485760]; // 10 MBytesint initialize_every_time = 8;static int static_initialized_every_time = 10;static int not_initialized;...
}
37
Необходимост от OCIExtProcAllocCallMemory
например във функция за обединяване на два стринга:
функцията приема като параметри два стринга,
който трябва да обединят и да върне резултът от
обединието
38
Начин за заобикаляне необходимостта от OCIExtProcAllocCallMemory
вместо 'out' параметри да се използват 'in out'параметри, чиито стойности да бъдат инициализиранипреди извикването на външна C функция:declare aa pls_integer := -7; ss varchar2( 200 ) := '123'; res pls_integer := 0;begin
... res := epc_demo_func( aa, ss );
...end;/
39
Предаване на масиви като параметри PL/SQL
create or replace TYPE T_String_Array AS TABLE OF VARCHAR2(4000);/
create or replace function func_with_varray( pls_sql_table_of_strings in out T_String_Array, ) return pls_integer as
language Cname "c_func_with_varray"library SOME_LIBcalling standard Cwith contextparameters(
context,pls_sql_table_of_strings OCICOLL,return int);
/
40
Предаване на масиви като параметри C
int c_func_with_varray( OCIExtProcContext *ctx,OCITable **pls_sql_table_of_strings,
){
// get the current number of elements into pls_sql_table_arraystatus = OCICollSize( ... );
// remove all current elements from pls_sql_table_arraystatus = OCICollTrim( ... );
// makes an OCI string from 'char *'status = OCIStringAssignText( ... );
// append one more element to the pls_sql_table_arraystatus = OCICollAppend( ... );
}
41
Изпълнение на DDL и DML във EPCint c_func_with_varray(
OCIExtProcContext *ctx,...
){
OCIEnv *envhp; OCISvcCtx *svchp; OCIError *errhp; sword err; ... // get the environment handles for reuse the existing connection err = OCIExtProcGetEnv( ctx, &envhp, &svchp, &errhp ); ... // executing all other kind of OCI calls...
}
42
Q & A
43
Благодаря за Вашето внимание!