Oracle 7 Stored Procedure Bug (was Re: Oracle 7 - 'UPDATE' fails in Stored Procedure)

I spoke with the Oracle help desk today . This is a known bug Oracle 7.

Evidently when you pass parameters to a stored procedure during an UPDATE tablename ... it will not perform the update, even if you COMMIT the work.

The work around is to copy each parameter passed to the stored procedure to locally declared variables within the stored procedure. Use the same data type. Then, use those locally declared variables in your UPDATE tablename ... statement. Of course, COMMIT your work. That works.

I have only discovered this with the UPDATE statement. INSERT INTO and DELETE FROM work ok without copying. It might be a good idea to copy all of your parameters to local variables in all cases if you're using stored procedures, since I do not know what triggers this bug to occur.

Jim

In comp.databases.oracle I previously wrote:
:
: I am attempting to do table updates (UPDATE tablename ...)
: from within a stored procedure, but the table will not get updated.
:
: In all instances, I get no error messages and I have a clean compile,
: so I know there are no syntax errors.
:
: I also put in debug statements to print out the parameters to the
: stored procedure. They are *exactly* what they should be, with
: no hidden white spaces.
:
: There are 3 scenarios that I try:
:
: 1) Called from a Pro*C program
: 2) Called from the SQL> prompt with: execute procedurename(arg1,arg2...)
: 3) Manually type in the exact UPDATE tablename... statement at the SQL> prompt
:
: When I create/compile the procedure I get:
:
: Procedure created.
:
: And when I run it from the SQL> prompt with execute, I get:
:
: PL/SQL procedure successfully completed.
:
:
: Scenarios 1) and 2) above fail to update the table. And I am
: doing a COMMIT. I then look at the table entry with SELECT and
: it's the old data. %ROWCOUNT returns as 1. I would assume its
: telling me one row was updated, but it's not.
:
: Scenario 3) will update the table, but I'm bypassing the programatic
: language portion and stored procedure.
:
:
:
: Version Info:
: ORACLE7 Server Release 7.0.13.1.0 - Production
: With the procedural and distributed options
:
: PL/SQL Release 2.0.15.1.0 - Production
: SQL*Plus: Release 3.1.2.2.1 - Production
: Pro*C: Release 1.5.7.0.1 - Production
:
:
:
: This is on a Sparc, Sun OS 4.1.3. Is there version incompatibility?
:
: Any assistance would be appreciated. Responses in email are preferred.
: Thanks.
:
: ------------------------------+------------------------------------
: Jim Yorton | Telephone: +1 708-632-6695
: Motorola Inc. |
: General Systems Sector | Internet: yorton_at_cig.mot.com
: Cellular Infrastructure Group | UUCP: ...!uunet!motcid!yorton
: Arlington Heights, IL USA |
Received on Fri Dec 17 1993 - 18:12:46 CET