While compiling good Trigger Ora-04029 error ORA-942

[vistabud]

karluk,
I need your interpetation. I have attempted to to compile the following Trigger (When-Validate-Item) using Forms developer on Oracle 8.1.7 Example:

DECLARE
DUMMY VARCHAR2(9);
err-msg CHAR(100);
BEGIN
--VERIFY NEW RECORD
IF :BEEMER.SSN IS NOT NULL THEN
SELECT SSN INTO DUMMY
FROM BASS_V1.BEEMER
WHERE TO_NUMBER(SSN) = :BEEMER.SSN;
IF sql%found THEN
BELL;
MESSAGE ('Error - This SSN already exists.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
SELECT TO_NUMBERBEEMER.SSN) INTO DUMMY FROM DUAL;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
MESSAGE (New SSN verified. This SSN is a new entry.');
WHEN OTHERS THEN
err-msg := SUBSTR(sqlerrm, 1, 100);
message ('Unhandled error -'||err-msg);
END;
**********************************************************
ON COMPILE RETURNED ERROR:
ora-04029 error ORA-942 occurred when querying ORA KGLR IDL-SB4.
***********************************************************
ALTHOUGH THIS SCRIPT HAS COMPILED IN THE PASS-SUDDENLY IT
DOESN'T WANT TO WITHOUT ERRORS. We have had tablespace
problems but pretty much resolved. Don't have a clue.
I have not been able to find complete error ref.

 

[jee]

Copied/Pasted from Oracle Error Messages manual:

ORA-00942 table or view does not exist

Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

Action: Check each of the following:

the spelling of the table or view name.

that a view is not specified where a table is required.

that an existing table or view name exists.

Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.

Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.

 

[vistabud]

KARLUK,
THANK YOU. I followed your advice and found that tables
are in fact valid, some of which have been used for nearly
a year with various forms. Since the last patch I recall
some squirlly activities but have not had or taken the time
to research. Anyhow, it occurred to me to do a SELECT * FROM SYS.ora_KGLR7_IDL_SB4 the table listed in the error
condition above and this is the return:

SQL> SELECT * FROM SYS.ORA_KGLR7_IDL_SB4;
ERROR:
ORA-00600: internal error code, arguments: [12338],[],[]

no rows selected

SQL>
************************************************************
I did a lookup on ORA-00600 and found "HOW TO FIX IT" but was referred to Oracle Support. I did not find any other info concerning the argument:[12338]. WHAT DO YOU THINK?

 

[jee]

No clue - This is obviously some sort of system view and your copy must have some problem. Actually when I execute

SELECT * FROM SYS.ORA_KGLR7_IDL_SB4;

I get an error - 942 - inconsistent datatypes. I see that one of the columns has an UNDEFINED datatype. I can select other columns from this view individually. I think this is probably normal.

 

[jee]

Whoops - I keep making typos today! The error I get is 932, not 942.

 

[karluk]

According to Metalink, your attempts to directly query ORA_KGLR7_IDL_SB4 won't work and will give the errors you were getting. The difference in error code between Jee and Vistabud was caused because they are on different versions of Oracle. See Metalink document 132787.1.

Your original problem with the trigger is most likely a byproduct of the patch installation. ORA_KGLR7_IDL_SB4 is created in the catproc.sql script. Your DBA should have run this script after the patch installation. Perhaps there was a problem that went undetected.

Ask your DBA to rerun catalog.sql and catproc.sql as sys and see if it makes a difference. Please note that there is a reference on Metalink to this problem. The document id is 80213.999.