Creating a Library in Oracle in Unix/Linux

In this blog, I would demonestrate how to write an external procedure in C and build as a Shared Object(*.so) and publish the same as an Oracle Library which can be called as PL/SQL Stored Procedure or Function in our programs. 
I am defining a function in C to Reverse a String. The signature is given below. 

1. char *reverseString(char *);  

This function taken a String argument and return the reverse of the String. 

Following are the Steps to Define the Library. 

Create an Header file to Include the Signature of the Function. I have named this file as OracleLibraryExample.h.

1. #ifndef _ORACLE_LIBRARY_EXAMPLE_  
2.       #define _ORACLE_LIBRARY_EXAMPLE_             
3.           extern char *reverseString(char *);  
4. #endif  

Create OracleLibraryExample.c File and define the Function reverseString.

1. #include "OracleLibraryExample.h"  
2. #include<stdio.h>  
3. #include<string.h>  
4. #include<stdlib.h>  
5.     char *reverseString(char *text) {  
6.         int length=strlen(text);  
7.         int i=0;  
8.         int i_idx;  
9.         char *tmpStr=malloc(length*sizeof(char *));  
10.         printf("Size of the String %d\n",length);  
11.         printf("String :%s\n",text);  
12.         for(i_idx=length-1;i_idx>=0;i_idx--)    {  
13.                 *(tmpStr+i++)=text[i_idx];  
14.         }  
15.         *(tmpStr+i)='\0';  
16.         return tmpStr;  
17.     }

Compile the *.c Files and Make it as Shared Object files(.so) files.

1. gcc -c -Wall -Werror -fpic OracleLibraryExample.c  
2. gcc -shared -o libOracleLibraryExample.so OracleLibraryExample.o  
3. This will generate a Shared Library (.so) file(libOracleLibraryExample.so)  

Login to Oracle Server machine through Unix Terminal.

export the ORACLE_HOME,ORACLE_SID,LDD_LIBRARY_PATH if it is not set already.

Create a new Schema say "Zenith" to create & test the Shared libraries.

Grant necessary privileges to "Zenith" such as CREATE PROCEDURE, EXECUTE PROCEDURE and CREATE LIBRARY etc.

Create a Library in this Schema.

1. CREATE OR REPLACE LIBRARY lib_string_function_sample AS '/home/oracle/libTest/libOracleLibraryExample.so';  

Publish the Function and Create a Wrapper to Call the C routine.

1. CREATE OR REPLACE FUNCTION reverse_string(text varchar2)   
2. RETURN varchar2  
3. AUTHID DEFINER   
4. AS LANGUAGE C  
5. LIBRARY lib_string_function_sample  
6. NAME "reverseString"  
7. parameters(text string);  
8. /  

Stop the Listener.

Edit the listener.ora file in the directory $ORACLE_HOME/network/admin.

1. EXT_LISTENER =  
2.   (DESCRIPTION_LIST =  
3.     (DESCRIPTION =  
4.       (ADDRESS_LIST =  
5.         (ADDRESS = (PROTOCOL = IPC)  
6.                    (KEY = EXTPROC1523)  
7.         )  
8.       )  
9.     )  
10.   )  
11. SID_LIST_EXT_LISTENER =  
12.   (SID_LIST =  
13.     (SID_DESC =  
14.       (SID_NAME = PLSExtProc)  
15.       (ORACLE_HOME = /opt/ora12c/product/12.1.0/dbhome_1 )  
16.       (PROGRAM = extproc)  
17.     (ENVS="EXTPROC_DLLS=ONLY:/home/oracle/libTest/libOraclePrintString.so:/home/oracle/libTest/libOracleLibraryExample.so,LD_LIBRARY_PATH=/opt/ora12c/product/12.1.0/dbhome_1/lib")  
18.     )  
19.   )  
20. Save & Exit

Edit the tnsnames.ora files. Add the following.

1. EXTPROC_CONNECTION_DATA =  
2.   (DESCRIPTION =  
3.     (ADDRESS_LIST =  
4.       (ADDRESS = (PROTOCOL = IPC)  
5.                  (KEY = EXTPROC1523)  
6.       )  
7.     )  
8.     (CONNECT_DATA =  
9.       (SID = PLSExtProc)  
10.       (PRESENTATION = RO)  
11.     )  
12.   )  

Start the listener by executing lsnrctl start.

1. export LD_LIBRARY_PATH=$LIBRARY_PATH:/home/oracle/libTest  

Start sqlplus and check whether library is working.

1. SQL> SELECT reverse_string('Hello Welcome to Oracle Library') FROM dual;  
2.   
3. REVERSE_STRING('HELLOWELCOMETOORACLELIBRARY')  
4. --------------------------------------------------------------------------------  
5. yrarbiL elcarO ot emocleW olleH  

If the library is accessing through SQL Developer you might get the below error.

1. ORA-28595: Extproc agent : Invalid DLL Path   
2. 28595. 00000 - "Extproc agent : Invalid DLL Path"   
3. *Cause: The path of DLL supplied for the extproc execution is invalid.   
4. *Action: Check if the DLL path is set properly using the EXTPROC_DLLS   
5. environment variable.   

To rectify this error, you need to set an enironment variable in your local machine as shown below. 

1. SET  EXTPROC_DLLS=ONLY:/home/oracle/libTest/libOraclePrintString.so:/home/oracle/libTest/libOracleLibraryExample.so  

Now start the SQL Developer and execute the function, you will get the expected result. 

Wow !!! Thats amazing..