| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyone run into this strange ORA-00904 error ??
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks for all the ideas folks.... we decided to restore the TEST =
database instead=20
of pursue so we would have less down time (problem was relatively =
recent).=20
THEN in the middle of cold backup restore, I started seeing the same =
thing in my=20
development instance ..... !!!
=20
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....
=20
When you have a function-based index, based on a user function and you =
do a=20
fromuser/touser import, the touser tables that had the function-based =
index will=20
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.=20
=20
For those of you who wish to test for yourself (on 8.1.7), here is the =
test case we came up with:
=20
=3D=3D 1 =3D=3D Create BTU1 user =3D=3D=20
drop user btu1 cascade;=20
create user btu1 identified by btu1 ;=20
grant connect,resource to btu1;=20
grant query rewrite to btu1;=20
.=20
=3D=3D 2 =3D=3D Create table and function-based index =3D=3D=3D=20
connect btu1/btu1=20
create or replace package this_pkg IS=20
function f_upper (in1 varchar2 ) return varchar2 deterministic;=20
PRAGMA RESTRICT_REFERENCES( f_upper, WNDS, RNDS, WNPS);=20
end this_pkg;=20
/=20
.=20
create or replace package body this_pkg IS=20
function f_upper ( in1 varchar2 ) return varchar2=20
is=20
begin=20
return upper(in1);=20
end;=20
end this_pkg;=20
/=20
create table btu1_table ( col1 number, col2 varchar2(30))=20
/=20
insert into btu1_table values (1, 'hello')=20
/=20
insert into btu1_table values (2, 'goodbye')=20
/=20
create index btu1_index=20
on btu1_table ( substr ( this_pkg.f_upper ( col2 ) ,1,30) )=20
/=20
.=20
=3D=3D 3 =3D=3D export this user =3D=3D=20
exp dba/passwd file=3Dbtu1.dmp owner=3Dbtu1=20
.=20
=3D=3D 4 =3D=3D Create 2nd user to import objects into=20
drop user btu2 cascade;=20
create user btu2 identified by btu2 ;=20
grant connect,resource to btu2;=20
grant query rewrite to btu2;=20
.=20
=3D=3D 5 =3D=3D Import objects into anothe user =3D=3D=20
imp dba/passwd file=3Dbtu1.dmp fromuser=3Dbtu1 touser=3Dbtu2=20
.=20
Export file created by EXPORT:V08.01.07 via conventional path=20
import done in WE8EBCDIC1047 character set and WE8EBCDIC1047 NCHAR =
character=20
set=20
. importing BTU1's objects into BTU2=20
. . importing table "BTU1_TABLE" 2 rows imported=20
Import terminated successfully without warnings.=20
.=20
=3D=3D 6 =3D=3D Select the newly imported objects and get ORA-904=20
sqlplus btu2/btu2=20
SQL> select * from btu1_table;=20
select * from btu1_table=20
*=20
ERROR at line 1:=20
ORA-00904: invalid column name=20
=20
=20
Babette Turner-Underwood=20
work: babette.turnerunderwood_at_hrdc-drhc.gc.ca=20
home: babette_at_rogers.com=20
954-3752 (Mon - Fri 7am - 3pm)=20
-----Original Message----- From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] 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...
=20 select internal_identifier int_id, title_cd,nonissue, nonissue2, = boy_this_could_be_a_problem a,nonissue3... from your_table where x=3Dy =20 HTH, Bambi.
=09
-----Original Message----- From: babette.turnerunderwood_at_hrdc-drhc.gc.ca = [mailto:babette.turnerunderwood_at_hrdc-drhc.gc.ca]=20 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.=20
My first choice is to run catalog / catproc. This did nothing to = resolve the problem.=20
Why am I able to describe an object, but get ORA-00904 when I try to = select from the table...=20
SQL> desc ispownre3.individual_names;=20 Name Null? Type=20 ----------------------------------------- -------- ------------------ = INTERNAL_IDENTIFIER NUMBER(12)=20 TITLE_CD NUMBER(3)=20 . . .=20 SQL> select * from ispownre3.individual_names;=20 select * from ispownre3.individual_names=20 *=20 ERROR at line 1:=20 ORA-00904: invalid column name=20
------_=_NextPart_001_01C3B9A0.5DFF01FF
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>Message</TITLE> <META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4926.2500" name=3DGENERATOR></HEAD> <BODY> <DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =size=3D2>Thanks=20
size=3D2>development instance ..... !!!</FONT></SPAN></DIV> <DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>I got=20
help from a fellow DBA and we managed to track it down, but I was too =
stressed=20
out</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>from=20
dealing with this all day to respond to let everyone know what happened. =
Bambi's=20
idea</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>was=20
closet to problem....</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>When=20
you have a function-based index, based on a user function and you =
do a=20
</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>fromuser/touser import, the touser tables that had the =
function-based=20
index will </FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>get=20
ORA-904, until the function-based index is dropped and=20
re-created.</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>According to Oracle, it is supposed to work this way. **I** =
think they=20
should at</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>least=20
have an import warning. </FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2>For=20
those of you who wish to test for yourself (on 8.1.7), here is the test =
case we=20
came up with:</FONT></SPAN></DIV>
<DIV><SPAN class=3D629131713-03122003><FONT face=3DArial color=3D#0000ff =
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D629131713-03122003> <P>=3D=3D 1 =3D=3D Create BTU1 user =3D=3D <BR>drop user btu1 cascade; = <BR>create user btu1=20
<BR>create table btu1_table ( col1 number, col2 varchar2(30)) <BR>/ =
<BR>insert=20
into btu1_table values (1, 'hello') <BR>/ <BR>insert into btu1_table =
values (2,=20
'goodbye') <BR>/ <BR>create index btu1_index <BR>on btu1_table ( substr =
(=20
this_pkg.f_upper ( col2 ) ,1,30) ) <BR>/ <BR>. <BR>=3D=3D 3 =3D=3D =
export this user =3D=3D=20
<BR>exp dba/passwd file=3Dbtu1.dmp owner=3Dbtu1 <BR>. <BR>=3D=3D 4 =
=3D=3D Create 2nd user to=20
import objects into <BR>drop user btu2 cascade; <BR>create user btu2 =
identified=20
by btu2 ; <BR>grant connect,resource to btu2; <BR>grant query rewrite to =
btu2;=20
<BR>. <BR>=3D=3D 5 =3D=3D Import objects into anothe user =3D=3D <BR>imp =
dba/passwd=20
file=3Dbtu1.dmp fromuser=3Dbtu1 touser=3Dbtu2 <BR>. <BR>Export file =
created by=20
EXPORT:V08.01.07 via conventional path <BR>import done in WE8EBCDIC1047=20
character set and WE8EBCDIC1047 NCHAR character <BR>set <BR>. importing =
BTU1's=20
objects into BTU2 <BR>. . importing table "BTU1_TABLE" 2 rows imported=20
<BR>Import terminated successfully without warnings. <BR>. <BR>=3D=3D 6 =
=3D=3D Select=20
the newly imported objects and get ORA-904 <BR>sqlplus btu2/btu2 =
<BR>SQL>=20
select * from btu1_table; <BR>select * from btu1_table <BR>* <BR>ERROR =
at line=20
1: <BR>ORA-00904: invalid column name </P><FONT face=3DArial =
color=3D#0000ff=20
size=3D2></FONT></SPAN></DIV>
<DIV> </DIV> <DIV> </DIV><!-- Converted from text/rtf format --> <P><SPAN lang=3Den-us><FONT face=3DArial size=3D2>Babette=20Turner-Underwood</FONT></SPAN> <BR><SPAN lang=3Den-us><FONT face=3DArial =
size=3D2>work: babette.turnerunderwood_at_hrdc-drhc.gc.ca</FONT></SPAN> =
<BR><SPAN=20
lang=3Den-us><FONT face=3DArial size=3D2>home: =
babette_at_rogers.com</FONT></SPAN>=20
<BR><SPAN lang=3Den-us><FONT face=3DArial size=3D2>954-3752 (Mon - Fri =
7am -=20
3pm)</FONT></SPAN> </P>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr =
align=3Dleft><FONT=20
face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B>=20 ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] <B>On Behalf Of=20 </B>Bellow, Bambi<BR><B>Sent:</B> 2003-12-02 6:14 PM<BR><B>To:</B> =Multiple=20
word. You didn't give us all the column names, but try something =
like=20
this...</FONT></FONT></SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D738340423-02122003></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D738340423-02122003>select internal_identifier int_id, =
title_cd,nonissue,=20
nonissue2, boy_this_could_be_a_problem =
a,nonissue3...</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN =
class=3D738340423-02122003>from=20
your_table</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D738340423-02122003>where x=3Dy</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D738340423-02122003></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D738340423-02122003>HTH,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D738340423-02122003>Bambi.</SPAN></FONT></DIV>
<DIV><FONT face=3DTahoma><SPAN class=3D738340423-02122003><FONT =
face=3DArial=20
color=3D#0000ff size=3D2></FONT></SPAN><BR><FONT size=3D2><SPAN=20 class=3D738340423-02122003><FONT face=3DArial=20 color=3D#0000ff> </FONT></SPAN>-----Original =Message-----<BR><B>From:</B>=20
<P><FONT face=3DArial size=3D2>We started having a weird problem = that looks like=20
some kind of data dictionary corruption.</FONT> </P> <P><FONT face=3DArial size=3D2>My first choice is to run catalog / = catproc. This=20
did nothing to resolve the problem.</FONT> </P> <P><FONT face=3DArial size=3D2>Why am I able to describe an object, = but get=20
ORA-00904 when I try to select from the table...</FONT> </P><BR> <P><FONT face=3DArial size=3D2>SQL> desc = ispownre3.individual_names;</FONT>=20
<BR><FONT face=3DArial=20
=
size=3D2> Name = &= nbsp; &n=bsp; =20
Null? Type</FONT> <BR><FONT face=3DArial=20
size=3D2> ----------------------------------------- --------=20
------------------</FONT> <BR><FONT face=3DArial=20
=
size=3D2> INTERNAL_IDENTIFIER &nb= sp; &nbs= p;  =;=20
NUMBER(12)</FONT> <BR><FONT face=3DArial=20 =
size=3D2> TITLE_CD &n= bsp; &nb= sp; &nbs=p; =20
NUMBER(3)</FONT> <BR><FONT face=3DArial size=3D2>. . . </FONT></P> <P><FONT face=3DArial size=3D2>SQL> select * from=20 ispownre3.individual_names;</FONT> <BR><FONT face=3DArial = size=3D2>select * from=20
ispownre3.individual_names</FONT> <BR><FONT face=3DArial=20
=
size=3D2> &nbs=
p;  =
; =20
*</FONT> <BR><FONT face=3DArial size=3D2>ERROR at line 1:</FONT> = <BR><FONT=20
face=3DArial size=3D2>ORA-00904: invalid column name</FONT>=20
</P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
=00
------_=_NextPart_001_01C3B9A0.5DFF01FF--
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <babette.turnerunderwood_at_hrdc-drhc.gc.ca INET: babette.turnerunderwood_at_hrdc-drhc.gc.ca Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Dec 03 2003 - 07:24:24 CST
 |
 |