Re: Error ORA-604/1000

This is an awful lot of work to clear out a table - use the TRUNCATE command.

Wayne Linton

Alan Screen wrote:
>
> 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 Thu Oct 02 1997 - 00:00:00 CDT