Outer Joins and Select ... for update of statements

From: Stephen Walsh <stevew_at_helix.com.au>
Date: 1998/02/27
Message-ID: <34F60FE0.2F8C152B@helix.com.au>#1/1

In Oracle 7.3 on NT and HPUX 10, the following select statement is part of a database procedure. When the procedure is executed, the database procedure hangs when this statement is reached:

SELECT lt.ROWID,

                lt.job_no,
                TO_CHAR(lt.sequence_no),
                lt.cost,
                lt.item_used_date,
                lt.posting_code,
                fi.order_no
FROM    ledger_tfr lt,
                res_expense_element re,
                facility_extra_data fi                 /* outer join */
WHERE   lt.facility_code            = fi.facility_code(+)
AND     re.resource_code          = lt.resource_code
AND     re.gl_ctrl_account          =  '1234'
AND     lt.gl_batch_id                 = '000011'
AND     SUBSTR(lt.job_no,1,1)   = 'J'
AND    (lt.gl_period =  2 OR lt.projects_period = 2)
AND     SUBSTR(lt.posting_code,1,4) = '0101'

FOR UPDATE OF lt.job_no;

It was narrowed down to the combination of the outer join and FOR UPDATE OF lt.job_no.

Without the FOR UPDATE statement, the select statement runs as expected.

Does anyone know why an outer join and for update statement combination causes a statement to hang.

Any comments would be appreciated.

--
Regards
Stephen Walsh


===========================================

Stephen Walsh
Helix Systems Pty Ltd
Ph:  +61 7 3846 1999
Fax:  +61 7 3846 2122
Email:  stevew_at_helix.com.au
Web: http://www.helix.com.au/


===========================================

Received on Fri Feb 27 1998 - 00:00:00 CST