Oracle 7.3.3 PL/SQL Stored procedures compiler bug & workaround

From: Philippe Verdy <verdy>
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

Original text of this message