RE: Cannot delete due to lock

From: Dasko, Dan <Dan.Dasko_at_cdicorp.com>
Date: Tue, 13 Mar 2001 12:49:33 -0800
Message-ID: <F001.002CB6C9.20010313125052@fatcity.com>

Does the cursor access the rows you are wanting to delete? If so, then perhaps that's the problem. You might instead write the rowid or some other distinguishing characteristic to a delete file/table then close the cursor and work through the delete file/table.

Dan

-----Original Message-----
Sent: Tuesday, March 13, 2001 3:16 PM
To: Multiple recipients of list ORACLE-L

A developer has implemented the following code in production. The process hangs every day and doesn't complete the delete statement. All rows are written to the output report file as they should be but none of the rows are delete. Every day we must kill the process and delete the rows with a separate, manual delete statement. There is nothing else running while this process is run.

part of the code...

BEGIN

        --open the file for output
        fileID:=utl_file.fopen('d:\data\ap_interface\ep','lcchkreq','W');
        --open the file for output(Error Log)

fileID_Errlog:=utl_file.fopen('d:\data\ap_interface\Reports','lcchkreq.log', 'W');

        FOR chkreq_cur_rec IN chkreq_cur  LOOP
                Counter:=Counter+1;     
                buffer:=chkreq_cur_rec.chkreq_fld;
                UTL_FILE.PUT_LINE(fileID,buffer);
        END LOOP;
        DELETE FROM kmg_checkreq_api WHERE capi_org_id ='8';
        -- close the output file
        UTL_FILE.FCLOSE(fileid);
        -- close the Error log file
        UTL_FILE.PUT_LINE (fileID_Errlog,'Number of records: '||Counter); 
        UTL_FILE.FCLOSE(fileID_Errlog);

etc...
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Smith, Ron L.
INET: rlsmith_at_kmg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

This e-mail message has been scanned for the presence of all known computer viruses by the MessageLabs Virus Control Center. However, it is still recommended that you use local virus scanning software to monitor for the presence of viruses.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Dasko, Dan
INET: Dan.Dasko_at_cdicorp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------