Why does Oracle call external procedures multiple times?

From: Michael Schaefers <schaefe3_at_cs.uni-bonn.spam-remove.de>
Date: Tue, 08 Feb 2005 17:09:41 +0100
Message-ID: <cuao86$rka$1@f1node01.rhrz.uni-bonn.de>

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 Received on Tue Feb 08 2005 - 10:09:41 CST