-
Notifications
You must be signed in to change notification settings - Fork 0
/
README-external-procedures.txt
516 lines (388 loc) · 21.5 KB
/
README-external-procedures.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
================================================================================
Oracle External Procedures
================================================================================
--------------------------------------------------------------------------------
Overview
--------------------------------------------------------------------------------
Oracle External Procedures (EXTPROC) is a feature that extends the functionality
of Oracle PL/SQL by allowing developers and database administrators to integrate
external programs and languages. EXTPROC enables users to incorporate code
written in languages such as C, C++, Java, or others directly into an Oracle
Database environment, enhancing its capabilities and enriching its flexibility.
The "Oracle DBA Toolkit" provides a sample program written in C and a BASH shell
script for compiling and building it as a shared library to be used as an
external procedure. The provided C program serves as a hands-on illustration and
demonstrates the practical implementation of Oracle External Procedures.
The sample C program can be found in 'oracle-dba-toolkit/extproc/example':
* factorial.c
* factorial.h
* main.c
Use the 'oracle-dba-toolkit/extproc/example/build.sh' shell script to compile
and build the shared library (factorial.so) for the included PL/SQL external
procedure application.
--------------------------------------------------------------------------------
Prerequisites
--------------------------------------------------------------------------------
1. The following software installed on the local machine:
* Oracle Database 8i (8.1.7) or higher
* GNU Compiler Collection (GCC)
--------------------------------------------------------------------------------
About Oracle External Procedures
--------------------------------------------------------------------------------
Oracle PL/SQL is a powerful programming language that offers a number of
benefits for developers and database administrators such as tight integration
with the Oracle Database, robust support for transaction management, and a
powerful exception-handling mechanism. PL/SQL serves various purposes, with a
specialization in SQL transaction processing. However, some tasks are more
quickly or easily done in a lower-level language such as C, which is more
efficient at machine-precision calculations. For example, a Fast Fourier
Transform (FFT) routine written in C runs faster than one written in PL/SQL.
To support such special-purpose processing, PL/SQL provides an interface
for calling routines written in other languages known as "External Procedures"
(EXTPROC).
EXTPROC is a feature in Oracle Database that allows developers and database
administrators to call external procedures or functions written in a language
other than PL/SQL. The primary use case for EXTPROC is to integrate non-PL/SQL
code, typically written in a lower-level language such as C, C++, or Java, with
the Oracle Database.
Developers can extend Oracle PL/SQL by creating shared objects (dynamic linked
libraries or DDL's) that contains the code in a function for what they want to
achieve. This is accomplished by registering the shared object with the Oracle
server using the CREATE LIBRARY statement. Once registered, the functions or
procedures can be called from within PL/SQL or SQL.
--------------------------------------------------------------------------------
Setup Oracle extproc Environment (Release 12.1 and higher)
--------------------------------------------------------------------------------
Configure the $ORACLE_HOME/hs/admin/extproc.ora file on the Oracle Database
server to point to the location of the shared library file(s).
For example:
SET EXTPROC_DLLS=ONLY:/opt/oracle-dba-toolkit/lib/extproc/factorial.so
SET LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib
. . .
TRACE_LEVEL=ON
After modifying extproc.ora, it may be necessary to restart the Oracle Listener:
$ lsnrctl stop
$ lsnrctl start
extproc.ora sets environment variables for the extproc daemon, which is used to
execute code for external stored procedures, and for procedures themselves.
The one environment variable recognized by extproc itself is EXTPROC_DLLS (shown
above), which determines the locations where the daemon looks for shared
libraries. If set to 'ONLY:<path>', extproc will not look in $ORACLE_HOME/bin or
$ORACLE_HOME/lib.
Any other variables set in extproc.ora are simply passed on to the environment
where the external code runs, so they would be specific to each library.
--------------------------------------------------------------------------------
Setup Oracle extproc Environment (Release 8.1.7 to 11.2)
--------------------------------------------------------------------------------
1. Oracle Database uses an external procedure agent named 'extproc' to support
external procedures.
2. Configure the Oracle Listener file on the local database machine.
For example:
#
# $ORACLE_HOME/network/admin/listener.ora
#
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:/opt/oracle-dba-toolkit/lib/extproc/factorial.so",
"LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib"
)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oranode11.acmeindustries.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
3. Restart the Oracle Listener:
$ lsnrctl stop
$ lsnrctl start
4. A valid TNS entry is required in the $ORACLE_HOME/network/admin/tnsnames.ora
file on the local database machine.
For example:
#
# $ORACLE_HOME/network/admin/tnsnames.ora
#
EXTPROC_CONNECTION_DATA.ACMEINDUSTRIES.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLSExtProc)
)
)
--------------------------------------------------------------------------------
Setup
--------------------------------------------------------------------------------
The 'oracle-dba-toolkit/extproc/example/build.sh' shell script performs the
following steps:
1. Compile and build the shared library 'factorial.so' for the PL/SQL
external procedures demo (Linux).
# Linux
$ gcc -Wall -Wextra -Wpedantic -std=c99 -m64 -fPIC -g -c factorial.c
$ gcc -shared -m64 -o factorial.so factorial.o
$ chmod 775 factorial.so
# SunOS (Oracle Solaris)
$ gcc -Wall -Wextra -std=c99 -m64 -fPIC -g -c factorial.c
$ gcc -shared -m64 -o factorial.so factorial.o
$ chmod 775 factorial.so
+-------------------+ +--------------------+
| factorial.c | ------> | factorial.so |
+-------------------+ +--------------------+
source code shared library
2. Copy shared library to destination operating system path.
$ mkdir -p /opt/oracle-dba-toolkit/lib/extproc
$ cp factorial.so /opt/oracle-dba-toolkit/lib/extproc/factorial.so
+--------------------+ +--------------------------------------------------------+
| factorial.so | ------> | /opt/oracle-dba-toolkit/lib/extproc/factorial.so |
+--------------------+ +--------------------------------------------------------+
shared library destination os path
3. After successfully building the shared library and copying it to the
destination OS path, create an Oracle library definition named 'EXAMPLE_LIB'
which is an Oracle schema object associated with the operating system shared
library.
SQL> CONNECT scott@"//datanode:1521/datadb.acmeindustries.com"
SQL> CREATE OR REPLACE LIBRARY example_lib
AS '/opt/oracle-dba-toolkit/lib/extproc/factorial.so';
/
+--------------------------------------------------------+ +------------------------+
| /opt/oracle-dba-toolkit/lib/extproc/factorial.so | ------> | SCOTT.EXAMPLE_LIB |
+--------------------------------------------------------+ +------------------------+
destination os path oracle library definition
Note: The default paths searched by the extproc daemon for loading the
required shared library are $ORACLE_HOME/bin and $ORACLE_HOME/lib.
4. Create a PL/SQL package specification and body including the procedures and
functions (wrapper procedures and wrapper functions) referencing the Oracle
library definition named 'example_lib'.
SQL> CONNECT scott@"//datanode:1521/datadb.acmeindustries.com"
SQL> CREATE OR REPLACE PACKAGE example_pkg AS
FUNCTION fact(
n IN PLS_INTEGER
) RETURN DOUBLE PRECISION;
END example_pkg;
/
SQL> CREATE OR REPLACE PACKAGE BODY example_pkg AS
FUNCTION fact(
n IN PLS_INTEGER
) RETURN DOUBLE PRECISION AS
EXTERNAL
LIBRARY example_lib -- Oracle library definition
NAME "fact" -- function name in library, quotes preserve lower case
LANGUAGE C -- language of routine
PARAMETERS (n ub4, RETURN double); -- map n to unsigned 4-byte (unsigned int)
-- map return value to a double
END example_pkg;
/
SQL> DESC SCOTT.EXAMPLE_PKG
FUNCTION FACT RETURNS NUMBER(126)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
N BINARY_INTEGER IN
+-------------------------+ +------------------------------+
| SCOTT.EXAMPLE_LIB | ------> | SCOTT.EXAMPLE_PKG.FACT |
+-------------------------+ +------------------------------+
oracle library definition oracle pl/sql wrapper procedures/functions
5. Test external procedures by calling the PL/SQL specification procedures and
functions (wrapper procedures and wrapper functions).
SQL> CONNECT scott@"//datanode:1521/datadb.acmeindustries.com"
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
DBMS_OUTPUT.put(chr(10));
DBMS_OUTPUT.PUT_LINE('Current session: ' ||
UPPER(SYS_CONTEXT('USERENV', 'CURRENT_USER')) || '@' ||
UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')));
DBMS_OUTPUT.put(chr(10));
DBMS_OUTPUT.put_line('Calculate factorials using an Oracle External Procedure (EXTPROC)');
DBMS_OUTPUT.put_line('written in C.');
DBMS_OUTPUT.put(chr(10));
FOR n IN 0..20
LOOP
DBMS_OUTPUT.put_line(n || '! = ' || EXAMPLE_PKG.fact(n));
END LOOP;
END;
/
+--------------+
| OUTPUT |
+--------------+
Current session: SCOTT@DATADB
Calculate factorials using an Oracle External Procedure (EXTPROC)
written in C.
0! = 1
1! = 1
2! = 2
3! = 6
4! = 24
5! = 120
6! = 720
7! = 5040
8! = 40320
9! = 362880
10! = 3628800
11! = 39916800
12! = 479001600
13! = 6227020800
14! = 87178291200
15! = 1307674368000
16! = 20922789888000
17! = 355687428096000
18! = 6402373705728000
19! = 121645100408832000
20! = 2432902008176640000
PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------
Notes
--------------------------------------------------------------------------------
1. Console Output
If the shared library produces any output to the console, it will not be
displayed. All you should see is "PL/SQL procedure successfully completed."
which is often times good enough for testing and proves extproc is working
properly. To see the output, redirect the output to a text file. For example:
SQL> EXEC shell('/bin/ls>output.txt');
If there is no output, supply a full path to an output file:
SQL> EXEC shell('/bin/ls>/u01/app/oracle/output.txt');
2. Trace extproc Process
Once the PL/SQL block execution starts, you can trace the extproc process by
listing the processes. The extproc process is active until the end of the
session.
[oracle@datanode ~]$ ps -ef | grep extproc | grep -v 'grep'
oracle 2662968 1 0 17:16 ? 00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/extproc (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))
Additionally, if TRACE_LEVEL is enabled in
$ORACLE_HOME/hs/admin/extproc.ora, the extproc process will write trace
files to $ORACLE_HOME/hs/log.
3. External Procedures Restrictions
* The feature is limited to the platforms that support dynamically linked
libraries
* The extproc process and PL/SQL procedure need to be on the same host
* The library clause cannot point to a remote location through a database
link
* The maximum number of parameters to an external procedure is 128
* Parameters of cursor variable types are not supported with external
procedures
4. The default configuration for external procedures does not require a network
listener to work with Oracle Database and the extproc agent. The extproc
agent is spawned directly by Oracle Database and eliminates the risks that
the extproc agent might be spawned by Oracle Listener unexpectedly. This
default configuration is recommended for maximum security.
You can change the default configuration for external procedures and have
the extproc agent spawned by Oracle Listener. To do this, you must perform
additional network configuration steps.
Having the extproc agent spawned by Oracle Listener is necessary if you use
the AGENT clause of the LIBRARY specification or the AGENT IN clause of the
PROCEDURE specification such that you can redirect external procedures to a
different extproc agent.
5. Securing External Procedures with Oracle Database 12c
The Oracle Database creates the extproc process and runs under the operating
system user that starts the listener or runs an Oracle server process. Quite
often, you will see the extproc process running as the oracle user. The
extproc process is not physically associated with the Oracle Database.
Oracle Database 12c enables enhanced security for extproc by authenticating
it against a user-supplied credential. This new feature allows the creation
of a user credential and associates it with the PL/SQL library object.
Whenever the application calls an external procedure, the extproc process
authenticates the connection before loading the shared library.
The DBMS_CREDENTIAL package allows the configuration of the credential
through member subprograms. The CREATE LIBRARY statement has been enhanced
for credential specification. A new environment variable,
ENFORCE_CREDENTIAL, can be specified in extproc.ora to control the
authentication by the extproc process. The default value of the parameter is
FALSE. Another new environment variable, GLOBAL_EXTPROC_CREDENTIAL, serves
as the default credential and is only used when the credential is not
specified for a library. If ENFORCE_CREDENTIAL is FALSE and no credential
has been defined in the PL/SQL library, there will be no user
authentication; this means the extproc process will authenticate by using
the privileges of the user running the Oracle server.
The following PL/SQL block creates a credential by using
DBMS_CREDENTIAL.CREATE_CREDENTIAL. This credential is built using the SCOTT
user:
BEGIN
DBMS_CREDENTIAL.CREATE_CREDENTIAL (
credential_name => 'datanode_auth',
user_name => 'scott',
password => 'tiger');
END;
/
The Oracle library definition will include an additional CREDENTIAL clause:
CREATE OR REPLACE LIBRARY example_lib
AS '/opt/oracle-dba-toolkit/lib/extproc/factorial.so'
CREDENTIAL datanode_auth;
/
When the extproc process reads the call specification and finds the shared
library with a secured credential, it authenticates the library on behalf of
the credential and then loads it.
--------------------------------------------------------------------------------
Known Issues
--------------------------------------------------------------------------------
1. ORA-28595: Extproc agent : Invalid DLL Path
Possible solutions are:
* The most common reason for this error is that the Oracle External
Procedure Agent (extproc) is unable to access the shared library
files. Use the 'EXTPROC_DLLS' parameter in the
$ORACLE_HOME/hs/admin/extproc.ora configuration file to specify the
shared libraries (*.so) or dynamic link libraries (DLLs) that contain
the external procedures.
Review the following sections provided in this note:
> Setup Oracle extproc Environment (Release 8.1.7 to 11.2)
> Setup Oracle extproc Environment (Release 12.1 and higher)
* Use default configuration for better security and let the database
spawn the extproc agent and use the $ORACLE_HOME/hs/admin/extproc.ora
to specify all the settings (i.e., LD_LIBRARY_PATH, EXTPROC_DLLS,
etc).
* For some reason, if the extproc needs to be spawned by the listener,
then use a different service name other than "EXTPROC_CONNECTION_DATA"
and use the AGENT/AGENT IN clause with the library or PL/SQL
specification.
2. stats.c:32:5: error: ‘for’ loop initial declarations are only allowed in C99 mode
Error Description
The error message indicates that the use of variable declarations within
the initialization part of a for loop is not allowed in the current
compilation mode (e.g., C89 mode).
For example:
for (int i = 0; i < 10; ++i) {
// Your loop code here
}
Solution Description
To address this issue, you can do one of the following:
1. Compile in C99 mode:
The -std=c99 option is used to specify the language standard to
be used for compiling C code. Specifically, it instructs the
compiler to adhere to the C99 standard, which is the 1999
revision of the C programming language standard.
Use the -std=c99 option when compiling your code to enable
support for C99 features, including variable declarations within
the initialization part of a for loop. For example:
gcc -Wall -std=c99 -fPIC -c stats.c
2. Move variable declarations outside the loop:
If changing the compilation mode is not an option, you can move
the variable declarations outside the for loop. For example:
int i; // Move the declaration outside the loop
for (i = 0; i < 10; ++i) {
// Your loop code here
}
This way, you comply with the C89 standard, which does not allow
variable declarations within the initialization part of a for
loop.
--------------------------------------------------------------------------------
References
--------------------------------------------------------------------------------
* Steps to Create and Run a Sample External Procedure Program on Unix (Doc ID 312564.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=312564.1
* Configuring extproc 11g and higher in a RAC environment with SCAN (Doc ID 1608372.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1608372.1
* Getting 'ORA-28595: Extproc Agent : Invalid DLL Path' With External Procedure Call After Upgrade to 12.2 (Doc ID 2424136.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2424136.1
* Securing External Procedures with Oracle Database 12c
https://learning.oreilly.com/library/view/advanced-oracle-pl-sql/9781785284809/ch05s04.html
* Oracle - spawn extproc by listener or database
https://westzq1.github.io/oracle/2019/02/24/extproc-use-different-user.html
* Access to external executables must be disabled or restricted
https://www.stigviewer.com/stig/oracle_database_12c/2019-09-26/finding/V-61685
* Extending the SQL and PL/SQL with custom external functions and procedures
https://medium.com/codex/extending-the-sql-and-pl-sql-with-custom-external-functions-and-procedures-214067761061