Re: Calling a Programm from a stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/01
Message-ID: <34329f13.30564299_at_newshost>#1/1

On Tue, 30 Sep 1997 09:22:32 -0400, Carl Christianson <Cchristi_at_fred.net> wrote:

>Just Curious,
>How do you do this in Oracle 8?
>
>Carl Christianson
>
>mconnors_at_mother.com wrote:
>

 [snip]
>>
>> Joe <josef.huber_at_ennstal.at> wrote in article
>> <01bcc4ff$27db9da0$019a70c2_at_AnonymousIPX>...
>> > Hi,
>> > Is it possible to call a Programm from a stored Procedure.
>> >
>> > System: Oracle Workgroupserver 7.3.2 Windows NT 4.0
>
>

In oracle8, this is called an external procedure. It allows you to implement your pl/sql routines in a language other then pl/sql. On NT you would create a .dll file, on unix you would create a .so (or .sl depending on platform) file. We dynamically pull in this code when you execute the stored procedure. You can implement method procedures (attached to objects) or regular pl/sql functions/procedures using this mechanism.

This is documented in the pl/sql reference for Oracle8. Here is a real small example that shows what you would put in the database and what the C prototypes would look like.

CREATE FUNCTION gcd (
-- find greatest common divisor of x and y

   x BINARY_INTEGER,
   y BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
   LIBRARY c_utils
   NAME "c_gcd" -- quotes preserve lower case    LANGUAGE C; The C prototype for c_gcd follows:

sb4 c_gcd(sb4 x_val, sb4 y_val);

You have control over how datatypes and such are mapped as well. A more complex example would be:

CREATE FUNCTION parse (

   x IN BINARY_INTEGER,
   Y IN OUT CHAR)
RETURN CHAR AS EXTERNAL
   LIBRARY c_utils
   NAME "c_parse"
   LANGUAGE C
   CALLING STANDARD PASCAL
   PARAMETERS (

      x,            -- stores value of x
      x INDICATOR,  -- stores null status of x
      y,            -- stores value of y
      y LENGTH,     -- stores current length of y
      y MAXLEN,     -- stores maximum length of y
      RETURN INDICATOR);

With this PARAMETERS clause, the C prototype becomes

char * c_parse(sb4 *x, sb2 x_ind, char *y, sb4 *y_len,

                  sb4 *y_maxlen, sb2 *retind);

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 01 1997 - 00:00:00 CEST