In this post I’ll explain what you need to do to setup external C procedure call with Oracle 18c.

I’ll also show for a CPU intensive task, what performance boost you might expect 거승체 다운로드.

For those of you who remember how difficult it was to configure LISTENER and TNSNAMES to work with C external procedures, will appreciate how easy it is to do the same task now Download the Satin machine lecture.

To setup external C procedures, you need to ssh to the database server and navigate into the following directory:

cd $ORACLE_HOME/hs/admin/

open the extproc.ora file (e.g 400kbps. with vi / nano or whatever text editor you have on server):

vi extproc.ora

and finally add a line at the end of file:

SET EXTPROC_DLLS=ANY

There are also other options you might set based on your security settings such as:

SET EXTPROC_DLLS=ONLY:DLL

(for maximum security) where only the specified DLL can be loaded 마이크로소프트 오피스 2013 무료 다운로드.

To verify the extproc program is available and executable (especially if you receive ORA‐28575: unable to open RPC connection to external procedure agent error), execute the following:

cd $ORACLE_HOME/bin

./extproc 

Oracle Corporation --- 2020-02-20 17:33:15.793876000

Heterogeneous Agent Release 18.0.0.0.0 - Production 

To create a real C external procedure, create a file cpuIntensive.c and insert the following code:

#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <math.h>

int cpuIntensive()
{

    long f,j;
    long n = 1000000000;
    double x;

    clock_t t;
    t = clock();

    for(f = 1;f <= n; f++)
    {
        x = pow(round(f % 9 + sqrt(f)), 2);
    }

    t = clock() - t;
    double time_taken = ((double)t)/CLOCKS_PER_SEC;
}

Now you need to generate shared library you need to have gcc installed (it’s usually already installed in case of Linus server), and execute the following:

gcc -m64 -shared -fPIC -lm -o cpuIntensive.so cpuIntensive.c

Next you need to create directory where your Oracle database have full read/writes permissions, e.g Click to download to twandwick.

mkdir /u01/proc

and copy generated shared object into the created directory:

cp cpuIntensive.so /u01/proc

Your DBA need to grant the following permission (I’m using a user SCOTT which is part of the sample schema):

GRANT CREATE LIBRARY TO SCOTT;

Log in as a SCOTT user and execute the following command to create a library:

CREATE LIBRARY cpuintensive_lib is '/u01/proc/cpuIntensive.so';

Final step is to create a PL/SQL wrapper to be able to call your external C procedure from the PL/SQL:

create or replace procedure cpuintensive_external
as external
   name "cpuIntensive"
   library cpuintensive_lib
   language C;

On my laptop when I call the wrapper:

exec cpuintensive_external;

it completes in 16 sec 나의 소녀시대 ost 다운로드.

By tweaking gcc option flags I can get even a better result.

Now you can create stored procedure in PL/SQL language to check how it compares with external C procedures Download ignored.

create or replace procedure cpu_speed_interpreted
as

x number := 0;

begin 
    for f in 1..1000000000
    loop
        x := power(round(mod(f, 9) + sqrt(f)), 2);
    end loop;
end;

From SQL Developer / TOAD / sql*Plus you can execute stored procedure:

exec cpu_speed_interpreted

783 sec

As you can see, for CPU intensive task PL/SQL is a poor choice I believe the.

That’s the main reason why the C external procedures was invented.

Still I don’t want to mislead you to start coding in external C procedures windows 10 버전별 다운로드.

Where PL/SQL really shines is in data intensive tasks which is most common use case for your database.

Additionally, although to setup external C procedures all you need to do is to add just one line, the most tedious task is to pass and to receive variables, arrays, collections to and from external procedures 더 지니어스 시즌3.

Debugging is also nightmare, especially if you are getting errors only when many users are calling it.

As of now, the best option is to use Java Stored procedures which will provide almost the same performance as C external procedures for CPU intensive tasks, while debugging, monitoring and data mapping is really simple (or at least much simpler than in C).

Although the field of usage for external C procedures (as well as for Pro*C) is narrowed, there are still some specific cases where you might need it.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.