Transcript External Routines Chapter 12 Oracle Database PL/SQL 10g Programming

External Routines
Oracle Database PL/SQL 10g Programming
Chapter 12
External Routines







2006
Architecture
Multithreaded Heterogeneous Agent
Oracle Listener Configuration
C Libraries
Java Libraries
PL/SQL Library Wrappers
Troubleshooting Shared Libraries
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 2
External Routines
Architecture

External Procedures:





2006
Are processes to communicate between external
programs and the Oracle database.
Are wrapped by PL/SQL stored program units.
Use external languages that are callable from the C
programming language.
Use Oracle Net Services to communicate with external
libraries.
Use Oracle Call Interface (OCI) libraries to map data
types.
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 3
External Routines
Architecture
Return
result
Application calls
PL/SQL wrapper
PL/SQL wrapper
forks RPC
Return
result
No
Resolves
network?
ORA-06521: PL/SQL:
Error mapping function
Yes
Listener spawns
extproc agent
Return
result
ORA-28576: Lost RPC
connection to external
procedure gate
Finds library?
ORA-28595: Extproc
agent: Invalid DLL path
No
Yes
Dynamic/shared
library
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 4
External Routines
Multithreaded Heterogeneous Agent



Monitoring thread manages dispatcher
threads.
Dispatcher threads manage task threads.
Task threads:


2006
Manages external programs.
Exchanges variable values with external programs.
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 5
External Routines
Oracle Listener Configuration


The listener.ora file contains the configuration
information for communicating with extproc programs.
The listener.ora defines:





2006
EXTPROC_DLLS to enable shared external libraries.
$LD_LIBRARY_PATH for the extproc agent.
$PATH for the extproc agent.
$APL_ENV_FILE for required environment variables that support
the extproc agent.
The IPC and TCP protocols should be run on separate
listeners.
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 6
External Routines
Oracle Listener Configuration
CALLOUT_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)
(KEY = extproc)
)
)
)
)
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 7
External Routines
Oracle Listener Configuration
SID_LIST_CALLOUT_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = <oracle_home_directory>)
(PROGRAM = extproc)
(ENV = "EXTPROC_DLLS=ONLY:
<oracle_home>/<custom_library>/writestr1.so,
LD_LIBRARY_PATH=<oracle_home>/lib")
)
)
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 8
External Routines
C Library: Sample Source
/* Include standard IO. */
#include <stdio.h>
/* Declare a writestr function. */
void writestr1(char *path, char *message) {
/* Declare a FILE variable. */
FILE *file_name;
/* Open the File. */
file_name = fopen(path,"w");
/* Write to file the message received. */
fprintf(file_name,"%s\n",message);
/* Close the file. */
fclose(file_name); }
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 9
External Routines
C Library: Compilation

Unix C Compiler that supports the –G option
cc –G –o writestr1.so writestr1.c

Unix C Compiler that supports the –shared option
cc –shared –o writestr1.so writestr1.c
or
gcc –shared –o writestr1.so writestr1.c
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 10
External Routines
C Library: Defining Library
CREATE [OR REPLACE] LIBRARY <library_name> {AS | IS}
'<oracle_home>/<custom_library>/<file_name>.<file_ext>';
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 11
External Routines
Java Library: I/O Permissions
-- Grant Java permission to file I/O against a file.
DBMS_JAVA.GRANT_PERMISSION('PLSQL'
'SYS:java.io.FilePermission'
'/tmp/file.txt'
'read');
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 12
External Routines
Java Library: Loading Class File

Load the Java class file into the database:
$ loadjava –r –f –o –user plsql/plsql ReadFile1.class
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 13
External Routines
PL/SQL Library Wrapper: C Language
CREATE [OR REPLACE] PROCEDURE write_string
(path
VARCHAR2
,message VARCHAR2) AS EXTERNAL
LIBRARY library_write_string
NAME "writestr "
PARAMETERS
(path
STRING
,message STRING);
/
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 14
External Routines
PL/SQL Library Wrapper: Java Language
CREATE [OR REPLACE] PROCEDURE read_string
(file
IN VARCHAR2)
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'ReadFile.readString(java.lang.String) return String';
/
2006
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 15
External Routines
Troubleshooting Shared Libraries

Listener ENV parameter is incorrect when
an ORA-06520 is raised:


2006
Incorrect synchronization of file path,
EXTPROC_DLLS value, and PL/SQL wrapper
NAME parameter.
Incorrect value for EXTPROC_DLLS or
LD_LIBRARY_PATH environment variables.
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 16
External Routines
Troubleshooting Shared Libraries




2006
Listener KEY parameter is incorrect or inconsistent
between the listener.ora and tnsnames.ora files
when an ORA-28576 is raised.
An ORA-28576 is also raised when the extproc listener
is shutdown or not running.
An ORA-28576 is also raised when the extproc listener
for IPC is not separated from the listener running for TCP
communication.
Other errors occur when the name in the PL/SQL
wrapper fails to resolve to a library file name.
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 17
Summary







2006
Architecture
Multithreaded Heterogeneous Agent
Oracle Listener Configuration
C Libraries
Java Libraries
PL/SQL Library Wrappers
Troubleshooting Shared Libraries
Oracle Database PL/SQL 10g Programming
(Chapter 12)
Page 18