Re: ORA-03106

From: Leigh Warren <leighw_at_midsys.co.uk>
Date: Wed, 1 Dec 1999 09:35:34 -0000
Message-ID: <822q7j$dl$1@soap.pipex.net>

Yass,

We have solved the problem using reference cursors. There is a BUG in 8i which is as follows:

OLIB bug with Oracle 8i

---

When binding buffers to PL/SQL code using OCIBindByName (and possibly with OCIBindByPos), certain structures produce the ORA-03106 error.

PL/SQL code blocks which do not contain any placeholders of type REF CURSOR work OK.
When there is one or more REF CURSOR placeholder, the error occurs when an lvalue placeholder of another type (eg NUMBER) appears before the REF CURSOR placeholders in the code.

Examples of code which produces the error: Example 1

---

 BEGIN
:retval := FunctionName(:r0, :p0, :p1, ...);

• r0 is a REF CURSOR END;

Example 2

---

 BEGIN
:dummy = 123;

   open :r0 for select 1 cola from dual;  END; Example 3

---

 BEGIN
:dummy = 123;

   open :r0 for select 1 cola from dual;    open :r1 for select 1 cola from dual;  END; Examples of code which work:

Example 4

---

 DECLARE
   temp NUMBER;
 BEGIN
   temp := FunctionName(:r0, :r1, :p0, :p1, ...);

• r0 and r1 are REF CURSORs
  :retval = temp;
  END;

Example 5

---

 BEGIN
   open :r0 for select 1 cola from dual;
:dummy = 123;

 END; Example 6

---

 BEGIN
   open :r0 for select 1 cola from dual;    open :r1 for select 1 cola from dual;
:dummy = 123;

 END; Example 7

---

 DECLARE
   temp NUMBER;
 BEGIN

   temp := :p0
   open :r0 for select 1 cola from dual;
   open :r1 for select 1 cola from dual;

:dummy = 123;

 END; Leigh.. Received on Wed Dec 01 1999 - 03:35:34 CST