In a secure application environment, the client team has segregated the string utility methods from the data layer to the client layer. We will take up a small program, which converts the case of a string input to uppercase, as an example. Check it out!
1. Creating and compiling the C program:
The following C program (CaseConvert.c) takes two numeric inputs
and returns their sum:
#include<stdio.h> #include<conio.h>
char ChangeCase(char *caps) { int index = 0; while (caps[ind]) { caps[index] = toupper(caps[index]); index++; } return caps; getch(); }
The file can be placed at any writable OS location on the server. In production environments, the privilege scheme to create or save a file on the operation system is decisive over the security factor on the server. For demonstration purposes, we will place the compiled file in the C:\Labs\C\ directory.
Note that compilation is necessary to check the validity of the program.
C:\Labs\C> gcc –c CaseConvert.c
As a result, the CaseConvert.o compiled module got generated at the
same location.
2. Generating the shared library for the C program:
The shared library (DLL) can be generated using the gcc command:
C:\Labs\C>gcc -shared CaseConvert.c -o CaseConvert.dll
Verify the generation of DLL at the same path. Moreover, the DLL path must be included in the ENVS="EXTPROC_DLLS=" parameter in the LISTENER.ora file.
3. Configuring the Oracle Net service:
Recheck the Oracle Net services configuration based on the recommendations made in the last section. The configuration checklist is as follows:
° The KEY value under the ADDRESS parameter of the ORACLR_CONNECTION_DATA TNS service is same as the
° The SID value under CONNECT_DATA parameter of TNS service ORACLR_CONNECTION_DATA matches the SID_NAME of the SID_LIST_LISTENER.
° The DLL path has been mapped correctly for the ENVS parameter
in the listener file.
° The ORACLR_CONNECTION_DATA pings successfully.
4. Creating a PL/SQL library object for the DLL:
This is the first programming step in the Oracle database. The PL/SQL library object acts as a database alias for the shared library location to be accessed by the PL/SQL subprograms. It is created either by a DBA or a user who enjoys the CREATELIBRARY or the CREATEANYLIBRARY privilege. If a DBA creates
the library, he must grant the EXECUTE privilege on the library to the user.
A PL/SQL library can be created as per the following syntax:
CREATE [OR REPLACE ] LIBRARY [Library name] [IS | AS] [DLL path with the name in single quotes]
AGENT [Agent DB link, if any]; /
In the syntax, the DLL path is the OS location of the DLL on the server. Note that the Oracle server never verifies the existence of the file specified in the library syntax.
In our case, we will follow the first method. The DBA or a user with DBA privileges grants the CREATELIBRARY privilege to the ORADEV user and the ORADEV user creates the library
/*Connect as SYSDBA*/
CONN sys/system AS SYSDBA Connected.
/*Grant the CREATE LIBRARY privilege*/
GRANT CREATE LIBRARY TO ORADEV; Grant succeeded.
Now, the ORADEV user can create the library in its own schema as follows: /*Connect as ORADEV*/
CONN ORADEV/ORADEV Connected.
/*Connect the library specifying the complete DLL path*/
CREATE OR REPLACE LIBRARY EXTDLL AS 'C:\Labs\C\CaseConvert.dll' /
5. Publishing the external program through call specification:
A PL/SQL wrapper method is created to invoke the external procedure from the database. It uses the library object to refer to the DLL which contains the C program as a linked module. It contains the external program method name which must be exactly the same as the one used in the external program. It maps the parameter based on inter compatibility between PL/SQL and the external program's base language. For example, char will
be mapped as VARCHAR2, int will be mapped as NUMBER, and so on.
This PL/SQL wrapper method is known as callspecification and the process is known as publishing the external program. The call specification serves the following objectives:
° Intercommunicates between the database engine and the external base language (C or Java)
° Dispatches the C-language program
° Parameter mode mappings and data type conversions
° Memory management
° Database purity state
The call specification can be a standalone procedure, function, or a package, too. It is the structure of the external C program which categorizes the call specification object type. The general structure of a call specification of function type looks as follows:
CREATE OR REPLACE FUNCTION [Name] [Parameters] RETURN [data type]
[IS | AS]
[call specification] END;
Similarly, call specification when linked to a procedures looks as follows:
CREATE OR REPLACE PROCEDURE [Name] [Parameters] [IS | AS]
[Call specification] END;
The call specification unites the details of the database library, external programming language, and the external program. It links the complete call to a subprogram such as procedure, function, package, or object type body.
The call specification follows the following syntax:
AS LANGUAGE C
[LIBRARY (library name)] [NAME (external program name)] [WITH CONTEXT]
[AGENT IN (formal parameters)] [PARAMETERS (parameter list)];
The components in the preceding syntax are listed as follows:
° LANGUAGEC states the base language of the external program. In this
case, it is C.
° LIBRARY is the database library object.
° NAME is the external program name. Note that it is case sensitive; it
must be the same as specified in the program code.
° The [WITH CONTEXT] clause directs PL/SQL to pass the "context
pointer" to the external program being invoked. Such parameters are of the OCIExtProcContext type.
° [AGENT IN (formal parameters)] is a list of formal parameters to
the call specification.
° [PARAMETERS (parameter list)] represents the parameter
mapping between PL/SQL and C by position.
For our illustration, the call specification can be coded as follows:
CREATE OR REPLACE FUNCTION F_CASE_CONVERT (P_STRING VARCHAR2)
/*Specify the RETURN type in compatibility with the external program*/
RETURN VARCHAR2
/*Specify the external program's base language*/
AS LANGUAGE C
/*Specify the PL/SQL library object name*/
LIBRARY EXTDLL
/*Specify the external function name*/
NAME "ChangeCase"
/*Specify the parameters*/
PARAMETERS (P_STRING STRING);
Let us verify the working of the preceding call specification with a PL/SQL anonymous block, shown as follows:
SQL> DECLARE
/*Declare a local parameter. Initialize with the test data*/
l_str VARCHAR2(1000) := 'oracle pl/sql developer'; BEGIN
/*Invoke the function and display he result*/
l_str := F_CASE_CONVERT (l_str); DBMS_OUTPUT.PUT_LINE(l_str); END;
/
ORACLE PL/SQL DEVELOPER
PL/SQL procedure successfully completed.