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.. 

Oracle Pro*c Example to Query the Data from a Table using Cursors

The following example demonestrate how to Query a table using Oracle Pro*c Pre-compiler. In the example I have used the table employees. Following are the Steps.

Create a file CursorExample.pc and past the following contents.

1. #include <stdio.h>  
2. #include <string.h>  
3. #include <stdlib.h>  
4. #include <sqlca.h>  
5. #include <sqlda.h>  
6. #include <sqlcpr.h>  
7. #define NAME_LEN 30  
8. #define ERR_LEN 512  
9.   
10. VARCHAR username[NAME_LEN];  
11. VARCHAR password[NAME_LEN];  
12. VARCHAR database[NAME_LEN];  
13.   
14. /* 
15. ** host variables to hold results of query 
16. */  
17. EXEC SQL BEGIN DECLARE SECTION;  
18. struct {  
19.         int empId;  
20.         VARCHAR firstName[NAME_LEN];  
21.         VARCHAR lastName[NAME_LEN];  
22.         int salary;  
23.         int deptNo;  
24. } empRec;  
25.   
26. struct  
27. {  
28.         short emp_name_ind;  
29.         short sal_ind;  
30.         short comm_ind;  
31. } empRecInd;  
32.   
33. int     empId;  
34. VARCHAR firstName[NAME_LEN];  
35. VARCHAR lastName[NAME_LEN];  
36. int     salary;  
37. int     deptNo;  
38. EXEC SQL END DECLARE SECTION;  
39. /* 
40. ** standard Pro*C error handler function 
41. */  
42. void sql_error(char *msg)  
43. {  
44.         char err_msg[ERR_LEN];  
45.         size_t buf_len, msg_len;  
46.   
47.         EXEC SQL WHENEVER SQLERROR CONTINUE;  
48.   
49.         printf("\n");  
50.   
51.         if (msg)  
52.         {  
53.                 printf("%s\n", msg);  
54.         }  
55.   
56.         buf_len = sizeof (err_msg);  
57.         sqlglm(err_msg, &buf_len, &msg_len);  
58.         printf("%.*s", msg_len, err_msg);  
59.   
60.         EXEC SQL ROLLBACK RELEASE;  
61.   
62.         exit(EXIT_FAILURE);  
63. }  
64.   
65. int main(int argc, char *argv[])  
66. {  
67.         strncpy((char *) username.arr, "username", NAME_LEN);  
68.         username.len = (unsigned short) strlen((char *) username.arr);  
69.   
70.         strncpy((char *) password.arr, "password", NAME_LEN);  
71.         password.len = (unsigned short) strlen((char *) password.arr);  
72.   
73.         strncpy((char *) database.arr, "ip address:port_no/database_name", NAME_LEN);  
74.         database.len = (unsigned short) strlen((char *) database.arr);  
75.   
76.         /* 
77.         ** register the error handler function 
78.         */  
79.         EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error: \n");  
80.   
81.         /* 
82.         * attempt to connect to the database 
83.         */  
84.         EXEC SQL CONNECT :username IDENTIFIED BY :password USING :database;  
85.   
86.         /* 
87.         ** indicate we want to break out of the loop when we get a no data 
88.         ** found message (i.e. at end of fetch) 
89.         */  
90.         EXEC SQL WHENEVER NOT FOUND DO break;  
91.   
92.         /* 
93.         ** declare and open cursor to fetch the employee details 
94.         */  
95.         EXEC SQL DECLARE emp_cursor CURSOR FOR  
96.   
97.         SELECT employee_id,first_name,last_name,salary,department_id  
98.         FROM employees WHERE ROWNUM<=10;  
99.         EXEC SQL OPEN emp_cursor;  
100.   
101.         /* 
102.         ** loop through cursor fetching results 
103.         ** and printing as we go 
104.         */  
105.   
106.         /* 
107.         ** indicate we want to break out of the loop when we get a no data 
108.         ** found message (i.e. at end of fetch) 
109.         */  
110.         EXEC SQL WHENEVER NOT FOUND DO break;  
111.         printf("%-15s%-30s%-30s%-15s%-15s\n", "EmployeeId", "FirstName","LastName", "Salary","DepartmentNo");  
112.         printf("------------------------------------------------------------------------------------------------------\n");  
113.         for (;;)  
114.         {  
115.                 EXEC SQL FETCH emp_cursor  
116.                 INTO :empRec;  
117.   
118.                 empRec.firstName.arr[empRec.firstName.len] = '\0';  
119.                 empRec.lastName.arr[empRec.lastName.len]= '\0';  
120.                 printf("%-15d%-30s%-30s%-15d%-15d\n", empRec.empId, empRec.firstName.arr,empRec.lastName.arr, empRec.salary, empRec.deptNo);  
121.         }  
122.   
123.         /* 
124.         ** clean-up and exit 
125.         */  
126.         EXEC SQL CLOSE emp_cursor;  
127.   
128.         EXEC SQL COMMIT RELEASE;  
129.   
130.         return EXIT_SUCCESS;  
131. }  

Compile the program.

1. $ proc CursorExample.pc  
2.   
3. This will create CursorExample.c file  
4.   
5. Compile the CursorExample.c  
6.   
7. $ gcc -c CursorExample.c -I/$ORACLE_HOME/precomp/public/  
8.   
9. $ gcc CursorExample.o -o CursorExample -I$ORACLE_HOME/precomp/public/ -I$ORACLE_HOME/rdbms/public/ -L$ORACLE_HOME/lib/ -L$ORACLE_HOME/precomp/lib/ -lclntsh  
10.   
11. This will generate an Executable file CursorExample.  
12.   
13. Execute the process by running the following command.  
14.   
15. $ ./CursorExample  
16.   
17. Output  
18. --------  
19.   
20. EmployeeId     FirstName                     LastName                      Salary         DepartmentNo  
21. ------------------------------------------------------------------------------------------------------  
22. 100            Steven                        King                          240001         90  
23. 101            Neena                         Kochhar                       17000          90  
24. 102            Lex                           De Haan                       17000          90  
25. 103            Alexander                     Hunold                        9000           60  
26. 104            Bruce                         Ernst                         6000           60  
27. 105            David                         Austin                        4800           60  
28. 106            Valli                         Pataballa                     4800           60  
29. 107            Diana                         Lorentz                       4200           60  
30. 108            Nancy                         Greenberg                     12008          100  
31. 109            Daniel                        Faviet                        9000           100