Oracle 7.3.3 PL/SQL Stored procedures compiler bug & workaround
Date: 1997/12/31
Message-ID: <#ci30jYF9GA.328_at_upnetnews04>
The 7.3.3 Oracle PL/SQL server package for Solaris 2.x has an internal
memory access bug.
This bug may affect other versions of Oracle Server or other platforms.
When you create a stored procedure having 15 parameters (or 31 or 47...,
by step of 16), the server goes into an infinite loop or recursion while
compiling the SPL procedure and unexpectedly breaks the communication
with the client after a long delay.
No error is returned to the client (under Solaris, sqlplus simply exits
without any warning message, under Windows 95, an unexpected end of
commnication occurs which is displayed in the SQLPLUS message window).
No stack trace is generated on the server and nothing appears in the
RDBMS or SQL*Net logs and trace files.
It took me a couple of days to find this bug and how to solve it. Renaming the parameters does not change anything. Removing or adding local declarations does not change anything. The complexity of the stored procedure has nothing to do wih this bug: even using a "BEGIN NULL; END;" stored procedure body does not solve the problem, and produces the same effect.
In fact:
It seems that the Oracle PL/SQL procedure compiler incorrectly allocates
the memory used to represent the vector of procedure parameters,
underevaluating the memory requirements for their representation within
the allocated buffer which will store the procedure parameters
descriptor. Then it uses this too small block of memory and overwrites
some data around the allocated page limit.
Workaround:
Simply add a dummy parameter for your stored proc with any name and
type, with DEFAULT NULL.
For example, you can change:
CREATE PROCEDURE foo(
p 1 INTEGER,
p 2 INTEGER,
p 3 INTEGER DEFAULT NULL,
p 4 VARCHAR2 DEFAULT ' ',
p 5 VARCHAR2,
p 6 DATE DEFAULT SYSDATE,
p 7 INTEGER,
p 8 NUMBER(16,2),
p 9 INTEGER DEFAULT 0,
p 10 INTEGER,
p 11 INTEGER DEFAULT NULL,
p 12 VARCHAR2 DEFAULT ' ',
p 13 VARCHAR2,
p 14 DATE DEFAULT SYSDATE,
p 15 INTEGER DEFAULT 0
) IS
v 1 VARCHAR2(32),
...
BEGIN
...
END;
into the following:
CREATE PROCEDURE foo(
p 1 INTEGER,
p 2 INTEGER,
p 3 INTEGER DEFAULT NULL,
p 4 VARCHAR2 DEFAULT ' ',
p 5 VARCHAR2,
p 6 DATE DEFAULT SYSDATE,
p 7 INTEGER,
p 8 NUMBER(16,2),
p 9 INTEGER DEFAULT 0,
p 10 INTEGER,
p 11 INTEGER DEFAULT NULL,
p 12 VARCHAR2 DEFAULT ' ',
p 13 VARCHAR2,
p 14 DATE DEFAULT SYSDATE,
p 15 INTEGER DEFAULT 0,
dummy INTEGER DEFAULT NULL /**** THIS PARAMETER ****/
) IS
v 1 VARCHAR2(32),
...
BEGIN
...
END;
--Received on Wed Dec 31 1997 - 00:00:00 CET