How to call C language programs in PL/SQL

Sometimes, we may find that some functions are difficult to complete through PL/SQL, while programming through C/C ++ is much easier. Therefore,OracleProvides the ability to directly call external functions (including C functions or Java methods) in PL/SQL programs, thus extending the PL/SQL program functions. Shows the process of calling an external function.

External function call Process

We can see that the process of calling an external function includes:

<! -- [If! Supportlists] --> 1) <! -- [Endif] --> the user process executes the PL/SQL program.

<! -- [If! Supportlists] --> 2) <! -- [Endif] --> during the execution of PL/SQL programs, a function written in C/C ++ is called: c_func. Here we need to use the alias library ). Alias library isDatabaseIs used to describe the path and name of the dynamic link library where an external function is located. Through the alias library, you can know which file the called external function is in.

<! -- [If! Supportlists] --> 3) <! -- [Endif] --> PL/SQL sends an external function call request to the listener.

<! -- [If! Supportlists] --> 4) <! -- [Endif] --> the listener generates an EXTPROC process, which is used to process calls to external functions. Each session generates an EXTPROC process that belongs to the session, and the EXTPROC process will always exist throughout the session lifecycle.

<! -- [If! Supportlists] --> 5) <! -- [Endif] --> the EXTPROC process loads the dynamic link library file specified by the alias Library to the memory.

<! -- [If! Supportlists] --> 6) <! -- [Endif] --> the EXTPROC process executes the specified external function, returns the result to the server process, and then returns it to the user process.

You need to configure the listener to start the EXTPROC process, as shown in Figure 11-3 above. (Address = (Protocol = IPC) (Key = extproc0) indicates the address of the EXTPROC process request listening. The sid_desc section describes the connection information of the EXTPROC process.

Then we need to configure the tnsnames. ora file. Note that this file must also be on the database server side. We need to add such content.

Configure tnsnames. ora

After the configuration is complete, you can try the tnsping connection string name. If the connection string is successful, the listener is correct.

[Oracle @ book admin] $ tnsping extproc_connection_data

Next, we will create a function written in C language, as shown below. This function provides a simple function. It calculates 15% of the input parameters and returns them to the caller as the tax amount.

[Oracle @ book ~] $ VI calc_tax.c

Calc_tax (N)

Int N;

{

Int tax;

Tax = (N * 15)/100;

Return (tax );

}

Compile the calc_tax.c file into a dynamic link library and copy the generated library file to the $ ORACLE_HOME/bin directory:

[Oracle @ book ~] $ CC-shared-O calc_tax.so calc_tax.c

[Oracle @ book ~] $ CP calc_tax.so $ ORACLE_HOME/bin

Then, we create an alias library to describe the library file where the C function to be called is located, and grant the permission to use the c_code alias Library to the HR User:

SQL> connect/As sysdba

SQL> Create or replace library c_code as '$ ORACLE_HOME/bin/calc_tax.so ';

2/

SQL> grant execute on c_code to HR;

To use this calc_tax function, you must also create a call declaration in the database. As follows:

SQL> connect HR/hr

SQL> Create or replace function call_c

2 (x binary_integer)

3 return binary_integer

4 As Language C

5 Library SYS. c_code

6 name "calc_tax ";

7/

In the call declaration, the passed-out parameters of the calc_tax function and the alias library name (library sys. c_code Section), and the function name (name "calc_tax") in the dynamic link library.

Now we can call call_c to call the calc_tax function. As follows:

SQL> set serveroutput on

SQL> var v_salary number;

SQL> var v_tax number;

SQL> Exec: v_salary: = 10000;

SQL> Exec: v_tax: = call_c (: v_salary );

SQL> Print v_tax;

V_tax

-----------------

1500

We can see from the returned results that the C function: calc_tax is successfully called.

Note: The above content is excerpted from Oracle Database Core Technology and practice-teach you how to become a 10g OCP.