Why does Oracle call external procedures multiple times?

Michael Schaefers · *nix forums beginner Joined: 03 Feb 2005 Posts: 3

Hi there,

I have written a function within a package, that returns a new instance of an
object type. This function is implemented in C. Whenever I call this function
once, the C code is executed FOUR times! Please see the following example:

CREATE TYPE foo AS OBJECT(
type INT,
len INT
) NOT FINAL INSTANTIABLE;

CREATE PACKAGE pkg AS
FUNCTION fooTest(w IN int) RETURN foo;
END;

CREATE PACKAGE BODY pkg AS
FUNCTION fooTest(w IN int) RETURN foo
IS EXTERNAL LANGUAGE C LIBRARY myLib NAME "fooTest"
WITH CONTEXT
PARAMETERS (
CONTEXT,
w,
w INDICATOR SHORT,
return INDICATOR struct
);
END;

My C function just creates a new foo instance and returns it:

foo* fooTest(OCIExtProcContext* ctx, OCINumber* i,
OCIInd *i_ind, foo_ind **ret_ind)
{
debugf(">>> entering fooTest");
foo *ret;
// here follows the OCIObjectNew Code
return ret;
}

When I say

DECLARE
f foo;
BEGIN
f := pkg.fooTest(42);
INSERT INTO tab VALUES (f);
END;

the C function is executed only once (as expected).
But when I execute something like

INSERT INTO tab VALUES (pkg.fooTest(42));

my C function is executed 4 times unless I declare the function fooTest
DETERMINISTIC (unfortunately, in the real world it isn't).

Does anybody know what I am doing wrong / what the reasons for this behavior are?

We are running Oracle 10g on a SuSE 9.0 Linux system.

Thank you very much.
Regards, Michael

Google