Re: Error ORA-604/1000

Wayne Linton (lintonw_at_cadvision.com) wrote:
: What does your delete code look like? Is it within a program or
: PL/SQL loop?
 

: Wayne Linton
: Shell Canada Ltd.

The code is for a Delete_All routine which removes all records from a table.

When I first wrote the code, I had a statement like:

        EXEC SQL DELETE FROM mis_02_noise_source;

where 'mis_02_noise_source' is the name of the table. Due to shared memory problems (we kept on getting ORA-04031 errors - server running out of shared memory), I rewrote it to be as follows (note: this is an Ada application, so this won't look like C code):

• begin Ada snippet ---------------------

        subtype NAME_T is STRING( 1 .. 32 );

	declare
		select_sql_cmd : STRING := "SELECT name FROM mis_02_noise_source";
		delete_sql_cmd : STRING
			:= "DELETE FROM mis_02_noise_source WHERE name=:name";
		name : NAME_T;
	begin

		EXEC SQL PREPARE select_for_delete_all FROM :select_sql_cmd;
		EXEC SQL PREPARE delete_for_delete_all FROM :delete_sql_cmd;

		EXEC SQL DECLARE select_for_delete_all_cursor
			CURSOR FOR select_for_delete_all;

		loop
			-- try to get a record
			EXEC SQL OPEN select_for_delete_all;
			EXEC SQL FETCH select_for_delete_all INTO :name;
			EXEC SQL CLOSE select_for_delete_all;

			-- exit if couldn't retrieve anything (i.e., table is empty)
			exit when sqlca.sqlerrd( 3 ) /= 1;

			-- got a record, so delete it
			EXEC SQL EXECUTE delete_for_delete_all USING :name;
			EXEC SQL COMMIT WORK;
		end loop;

-- end Ada snippet -----------------------

This seems to work for other tables in our database.... Maybe of note is that several other tables contain foreign keys to this table....

• Alan

: Alan Screen wrote:
: >
: > I'm running Oracle 7.3 on an UltraSPARC-2 with 512 MB of memory. I'm
: > trying to be able to delete a record from one of my tables, but I quite
: > often get the following sequence of errors:
: >
: > ORA-00604 error occurred at recursive level 1
: > ORA-01000 maximum open cursors exceeded
: >
: > The Server Messages manual suggests increasing the OPEN_CURSORS value
: > for the server. The system administrator has raised this number to
: > 1024, but that still doesn't solve my problem. Does anyone know how
: > to solve this?
: >
: > ----------------------------------------------------------------
: > Alan Screen | Phone: (416) 736-0900 ext. 287
: > Array Systems Computing Inc. | Fax: (416) 736-4715
: > 1120 Finch Ave. W, 8th Floor | E-mail: alans_at_array.ca
: > North York, ON M3J 3H7 | alan_screen_at_acm.org
: > Canada (eh?) | alan_screen_at_computer.org
: > http://www.array.ca | "I'd rather be snowboarding!!"
Received on Tue Sep 30 1997 - 00:00:00 CDT