1 / 18

External Routines Oracle Database PL/SQL 10g Programming

External Routines Oracle Database PL/SQL 10g Programming. Chapter 12. External Routines. Architecture Multithreaded Heterogeneous Agent Oracle Listener Configuration C Libraries Java Libraries PL/SQL Library Wrappers Troubleshooting Shared Libraries. External Routines Architecture.

lluvia
Download Presentation

External Routines Oracle Database PL/SQL 10g Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. External RoutinesOracle Database PL/SQL 10g Programming Chapter 12

  2. External Routines • 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)

  3. External RoutinesArchitecture • External Procedures: • 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)

  4. External RoutinesArchitecture Oracle Database PL/SQL 10g Programming (Chapter 12)

  5. External RoutinesMultithreaded Heterogeneous Agent • Monitoring thread manages dispatcher threads. • Dispatcher threads manage task threads. • Task threads: • Manages external programs. • Exchanges variable values with external programs. Oracle Database PL/SQL 10g Programming (Chapter 12)

  6. External RoutinesOracle Listener Configuration • The listener.ora file contains the configuration information for communicating with extproc programs. • The listener.ora defines: • 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)

  7. External RoutinesOracle Listener Configuration CALLOUT_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = extproc) ) ) ) ) Oracle Database PL/SQL 10g Programming (Chapter 12)

  8. External RoutinesOracle 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") ) ) Oracle Database PL/SQL 10g Programming (Chapter 12)

  9. 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); } Oracle Database PL/SQL 10g Programming (Chapter 12)

  10. External RoutinesC 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 Oracle Database PL/SQL 10g Programming (Chapter 12)

  11. External Routines C Library: Defining Library CREATE [OR REPLACE] LIBRARY <library_name> {AS | IS} '<oracle_home>/<custom_library>/<file_name>.<file_ext>'; Oracle Database PL/SQL 10g Programming (Chapter 12)

  12. 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'); Oracle Database PL/SQL 10g Programming (Chapter 12)

  13. External Routines Java Library: Loading Class File • Load the Java class file into the database: $ loadjava –r –f –o –user plsql/plsql ReadFile1.class Oracle Database PL/SQL 10g Programming (Chapter 12)

  14. External RoutinesPL/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); / Oracle Database PL/SQL 10g Programming (Chapter 12)

  15. External RoutinesPL/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'; / Oracle Database PL/SQL 10g Programming (Chapter 12)

  16. External RoutinesTroubleshooting Shared Libraries • Listener ENV parameter is incorrect when an ORA-06520 is raised: • 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)

  17. External RoutinesTroubleshooting Shared Libraries • 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)

  18. Summary • 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)

More Related