asked on

Ora-00604

I am Using form (Form6i) and back-end being oracle 8.1.7PO on a P4 machine 256mb RAM Win-98 PC.
The form has been tested else where and has no bugs. But here it is showing "Un Handelled Exception (604)" in executing a simple insert inside a loop.
Kindly tell me a way to debug. My init.ora has open_cursor=5000 and proper rollback_segments.
Regards....
Sujit

Mark Geerlings

My guess is lack of space in a data or index tablespace. If you cannot provide any more details, it will be very difficult for us to troubleshoot.

Here is the Oracle documentation for ORA-00604:

ORA-00604 error occurred at recursive SQL level...

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Customer Support.

- - - -

So, did you see other Oracle errors as well? Can you post them?

nitinocp

would you just post give the output for ...
show sga command in backend...i.e. from sql*plus

how many rollback segments you have created and are online...plzzz confirm..

Avotar

a ref-cursor that has not been defined properly will generate this error...

sujit_kumar

ASKER

Nice comments. But i can't see the second error as i am experincing it in Forms.
Any other suggestions.
Sujit

Mark Geerlings

Based on the limited amount of information you've give us, I still think the most likely cause of the problem is a space issue. This could be O/S disk space (if all of the datafiles in the database are set to autoextend) or if the tablespaces are not set up to autoextend, then either the data, index or system tablespace are probably out of room. Have you confirmed that there is free space available in both the database and the O/S?

Have you checked the Oracle alert.log file to see if it gives you any clues to the underlying problem?

sujit_kumar

ASKER

Hi ,
Yes i have checked for System resources and also my tablespaces have auto extend ON, I have checked for the rollback segments and added one more to it. set the perticulat transaction (which is showing message) to the newly created roll back segment. But nothing helps. I tried it in diff machines it works perfectly. I just want to know the possible cause. Because ORA-00604 error is based on the second error i know.. But the error is never appearing from the form run time.
Regards...
Sujit

Mark Geerlings

Have you checked the alert.log file on the server?

You can add exception-handling to the procedure/program unit that has the loop and the insert. That should make it possible to trap the actual error.

Otherwise, can you make that loop and insert procedure a stand-alone procedure that you can run from SQL*Plus? That would give you another way to trap the actual error.

sujit_kumar

ASKER

Thanks markgeer ,
But i have tried all of it. I executed the loop in back-end. It is working fine. I tried exception but it is coming to exception on ORA-00604
I have also executed the form from anather back-end (of same configuration) it is working .
Any other suggestions ??
Sujit

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

There is an error in the "freespac.sql" script I posted. I had removed part of a comment in that script before posting it here, but not the entire comment. The line:
'to "temporary" tablespaces'
should be removed.

sujit_kumar

ASKER

hi markgeer,
I have uninstalled that database and re-installed oracle after that thing are working fine. But thanks for the scripts.
Sujit