ORA-00604: error occurred at recursive SQL level 1

[gaegoori]

Got this error message after running this query.

select DISTINCT
fmuln.uln_id "REQID"
,chr(28),fmuln.oplan_id "PLAN_ID"
,chr(28),fmuln.FM_ID "FM_ID"
,chr(28),'1' "TYPE"
,chr(28)
FROM
test.force_module_uln fmuln
where fmuln.plan_seq_id ='179071101'
order by REQID,PLAN_ID,FM_ID
/


ERROR at line 8:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

I get this error message when I run this as a user who has access to this table.

I do not get error message when I run this as a sys or table owner "test".

This is on Solaris 2.7 with Oracle 8.1.6.

Please help me...is there anything wrong on select query?

[dknight]

If you log on as the user running the query, can you see the table

force_module_uln

owned by TEST in the ALL_OBJECTS view?

[gaegoori]

I can see the table log in as user.
I'm not sure if this effects, but this is a partitioned table.....

[molonede]

There are similar problems after upgrading from the database from 7x/805 to 816. Have you recently upgraded this database?

[gaegoori]

this database was upgraded from 805 to 816 about four month ago....so what do I have to do?

[molonede]

Perform a sqltrace on the query and view the output to see which table it is having trouble locating. Also, there is a bug in Metalink that looks real similar to this. Bug 1297277, which indicates to create a synonym instead of a view. Alos, it references init parameter star_transformation_enabled=true. But this is something you will have to do more research on, I've not used this parameter as of yet.