RE: Anyone run into this strange ORA-00904 error ??

[babette.turnerunderwood]

Title: Message

Thanks for all the ideas folks.... we decided to restore the TEST database instead

of pursue so we would have less down time (problem was relatively recent).

THEN in the middle of cold backup restore, I started seeing the same thing in my

development instance ..... !!!

 

I got help from a fellow DBA and we managed to track it down, but I was too stressed out

from dealing with this all day to respond to let everyone know what happened. Bambi's idea

was closet to problem....

 

When you have a function-based index, based on a user function and you do a

fromuser/touser import, the touser tables that had the function-based index will

get ORA-904, until the function-based index is dropped and re-created.

According to Oracle, it is supposed to work this way. **I** think they should at

least have an import warning.

 

For those of you who wish to test for yourself (on 8.1.7), here is the test case we came up with:

 

== 1 == Create BTU1 user ==
drop user btu1 cascade;
create user btu1 identified by btu1 ;
grant connect,resource to btu1;
grant query rewrite to btu1;
.
== 2 == Create table and function-based index ===
connect btu1/btu1
create or replace package this_pkg IS
function f_upper (in1 varchar2 ) return varchar2 deterministic;
PRAGMA RESTRICT_REFERENCES( f_upper, WNDS, RNDS, WNPS);
end this_pkg;
/
.
create or replace package body this_pkg IS
function f_upper ( in1 varchar2 ) return varchar2
is
begin
return upper(in1);
end;
end this_pkg;
/
create table btu1_table ( col1 number, col2 varchar2(30))
/
insert into btu1_table values (1, 'hello')
/
insert into btu1_table values (2, 'goodbye')
/
create index btu1_index
on btu1_table ( substr ( this_pkg.f_upper ( col2 ) ,1,30) )
/
.
== 3 == export this user ==
exp dba/passwd file=btu1.dmp owner=btu1
.
== 4 == Create 2nd user to import objects into
drop user btu2 cascade;
create user btu2 identified by btu2 ;
grant connect,resource to btu2;
grant query rewrite to btu2;
.
== 5 == Import objects into anothe user ==
imp dba/passwd file=btu1.dmp fromuser=btu1 touser=btu2
.
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8EBCDIC1047 character set and WE8EBCDIC1047 NCHAR character
set
. importing BTU1's objects into BTU2
. . importing table "BTU1_TABLE" 2 rows imported
Import terminated successfully without warnings.
.
== 6 == Select the newly imported objects and get ORA-904
sqlplus btu2/btu2
SQL> select * from btu1_table;
select * from btu1_table
*
ERROR at line 1:
ORA-00904: invalid column name

 

 

Babette Turner-Underwood
work: [EMAIL PROTECTED]
home: [EMAIL PROTECTED]
954-3752 (Mon - Fri 7am - 3pm)

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bellow, Bambi
Sent: 2003-12-02 6:14 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Anyone run into this strange ORA-00904 error ??

I have run into this problem in the past in two different situations.  The first is where there is a reserved word somewhere that you managed to sneak through somehow... the second is where there is an internal view created for some reason that barfs... e.g., VW_NSO_1.$NSO_COL_1.  Check your alert log.  If it's the latter, it's a bug.  If it's the former, try specifying the columns and adding a column alias where the column could conceivably be a reserved word.  You didn't give us all the column names, but try something like this...

 

select internal_identifier int_id, title_cd,nonissue, nonissue2, boy_this_could_be_a_problem a,nonissue3...

from your_table

where x=y

 

HTH,

Bambi.

 -----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L
Subject: Anyone run into this strange ORA-00904 error ??

We started having a weird problem that looks like some kind of data dictionary corruption.

My first choice is to run catalog / catproc. This did nothing to resolve the problem.

Why am I able to describe an object, but get ORA-00904 when I try to select from the table...

SQL> desc ispownre3.individual_names;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 INTERNAL_IDENTIFIER                                NUMBER(12)
 TITLE_CD                                           NUMBER(3)
. . .

SQL> select * from ispownre3.individual_names;
select * from ispownre3.individual_names
                         *
ERROR at line 1:
ORA-00904: invalid column name