Re: ORA-904 in EXP80

Chris

You can get this error if you create a view that selects a column on the base table that does not exist. One reason is that you are running a later version of catalog.sql or catproc.sql that has a create view statement in it that references a column on a dictionary table that only exists in the later release. I would look at the columns chosen in the create view statements and see if they match the underlying data dictionary.

HTH. Pete

Christoph Kukulies wrote:

> Thanks for giving the hints regarding EXP80.
> What I'm getting now is ORA-904 invalid column name (translated
> from the error message in german - ungueltiger Spaltenname).
>
> Is there a way to make EXP80 more verbose or look up in some
> logs what exactly that invalid column name is?
>
> It doesn't seem to be a user column name. At most it could be
> from the VIEW8.ORA SQL script that Centura delivers for
> making an Oracle DB more SQLWindows look a like (SYSADM views
> and such).
>
> I'm appending the view8.ora file. Otherwise I would be clueless
> what would be causing this invalid column name error.
>
> --
> Chris Christoph P. U. Kukulies kuku_at_gil.physik.rwth-aachen.de
> DROP VIEW SYSCOLCOUNT;
> DROP VIEW SYSTABLES;
> DROP VIEW SYSTABLES1;
> DROP VIEW SYSCOLUMNS;
> DROP VIEW SYSIND_COL#;
> DROP VIEW SYSINDEXES;
> DROP VIEW SYSKEYS;
> DROP VIEW SYSTABAUTH;
> DROP VIEW SYSCOLAUTH;
> DROP VIEW SYSSYNONYMS;
>
> CREATE VIEW SYSCOLCOUNT (OBJ#, COLCOUNT)
> AS
> SELECT C.OBJ#, COUNT(*)
> FROM SYS.COL$ C
> GROUP BY C.OBJ#
> ;
>
> CREATE VIEW SYSTABLES (CREATOR, NAME, COLCOUNT, TYPE, REMARKS, PERCENTFREE)
> AS
> SELECT U.NAME, O.NAME, CO.COLCOUNT,
> DECODE(O.TYPE#, 2, 'T', 4, 'V', 5, 'S', 'S'),
> C.COMMENT$,
> 0
> FROM SYS.OBJ$ O, SYS.USER$ U, SYS.COM$ C, SYSCOLCOUNT CO
> WHERE O.OWNER# = U.USER#
> AND O.OBJ# = C.OBJ#(+)
> AND C.COL#(+) IS NULL
> AND O.TYPE# IN (2, 4)
> AND O.OBJ# = CO.OBJ#
> and o.linkname is null
> and (o.owner# in (uid, 1)
> or
> o.obj# in ( select obj#
> from sys.objauth$
> where grantee# in ( select kzsrorol
> from x$kzsro
> )
> )
> )
> ;
>
> CREATE VIEW SYSTABLES1 (CREATOR, NAME, TYPE, REMARKS, PERCENTFREE)
> AS
> SELECT U.NAME, O.NAME,
> DECODE(O.TYPE#, 2, 'T', 4, 'V', 5, 'S', 'S'),
> C.COMMENT$,
> 0
> FROM SYS.OBJ$ O, SYS.USER$ U, SYS.COM$ C
> WHERE O.OWNER# = U.USER#
> AND O.OBJ# = C.OBJ#(+)
> AND C.COL#(+) IS NULL
> AND O.TYPE# IN (2, 4)
> and o.linkname is null
> and (o.owner# in (uid, 1)
> or
> o.obj# in ( select obj#
> from sys.objauth$
> where grantee# in ( select kzsrorol
> from x$kzsro
> )
> )
> )
> ;
>
> CREATE VIEW SYSCOLUMNS
> (TBCREATOR, NAME, TBNAME, COLNO, COLTYPE, LENGTH,
> NULLS, UPDATES, REMARKS, SCALE)
> AS
> SELECT U.NAME, C.NAME, O.NAME, C.COL#,
> decode(c.type#, 1, 'VARCHAR2',
> 2, decode(c.scale, null,
> decode(c.precision#, null, 'NUMBER', 'FLOAT'),
> 'NUMBER'),
> 8, 'LONG', 9, 'VARCHAR',
> 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
> 69, 'ROWID', 96,'CHAR', 'UNDEFINED'),
> C.LENGTH,
> DECODE(C.NULL$, 0, 'Y', ' '),
> 'Y',
> CO.COMMENT$,
> C.SCALE
> FROM SYS.COL$ C, SYS.OBJ$ O, SYS.USER$ U, SYS.COM$ CO
> WHERE O.OBJ# = C.OBJ#
> AND O.OWNER# = U.USER#
> AND O.TYPE# IN (2, 4)
> AND C.OBJ# = CO.OBJ#(+)
> AND C.COL# = CO.COL#(+)
> and (o.owner# = uid
> or
> o.obj# in ( select obj#
> from sys.objauth$
> where grantee# in ( select kzsrorol
> from x$kzsro
> )
> )
> )
> ;
>
> CREATE VIEW SYSIND_COL#
> (IND_OBJ, COL_COUNT)
> AS
> SELECT OBJ#, COUNT(*)
> FROM SYS.ICOL$
> GROUP BY OBJ#
> ;
>
> CREATE VIEW SYSINDEXES
> (TBCREATOR, NAME, TBNAME, CREATOR, UNIQUERULE, COLCOUNT, IXTYPE,
> CLUSTERRULE, IXSIZE, PERCENTFREE)
> AS
> SELECT IU.NAME, O.NAME, IO.NAME, U.NAME,
> DECODE(bitand (I.property, 1), 0, 'D', 1, 'U', 'D'),
> COL_COUNT, 'B', 'N', 0, 0
> FROM sys.ts$ ts, SYS.SEG$ S, SYS.USER$ IU, SYS.OBJ$ IO,
> SYS.USER$ U, SYS.IND$ I, SYS.OBJ$ O, SYSIND_COL# CNT
> WHERE U.USER# = O.OWNER#
> AND O.OBJ# = I.OBJ#
> AND O.OBJ# = CNT.IND_OBJ
> AND I.BO# = IO.OBJ#
> AND IO.OWNER# = IU.USER#
> and i.ts# = ts.ts#
> AND I.FILE# = S.FILE#
> AND I.BLOCK# = S.BLOCK#
> AND (IO.OWNER# = UID
> OR
> IO.OBJ# IN ( SELECT OBJ#
> from objauth$
> where grantee# in ( select kzsrorol
> from x$kzsro
> )
> )
> )
> ;
>
> CREATE VIEW SYSKEYS
> (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, ORDERING, FUNCTION)
> AS
> SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', ''
> FROM SYS.COL$ C, SYS.OBJ$ IDX,
> SYS.OBJ$ BASE, SYS.ICOL$ IC,
> SYS.USER$ IO, SYS.USER$ BO
> WHERE BASE.OBJ# = C.OBJ#
> AND IC.COL# = C.COL#
> AND IC.BO# = BASE.OBJ#
> AND IO.USER# = IDX.OWNER#
> AND BO.USER# = BASE.OWNER#
> AND IC.OBJ# = IDX.OBJ#
> AND (IDX.OWNER# = UID OR
> BASE.OWNER# = UID
> OR
> BASE.OBJ# IN ( SELECT OBJ#
> from sys.objauth$
> where grantee# in ( select kzsrorol
> from x$kzsro
> )
> )
> )
> ;
>
> CREATE VIEW SYSSYNONYMS (NAME, CREATOR, TBNAME, TBCREATOR)
> AS
> SELECT O.NAME, U.NAME, S.NAME, S.OWNER
> FROM SYS.USER$ U, SYS.SYN$ S, SYS.OBJ$ O
> WHERE O.OBJ# = S.OBJ#
> AND O.TYPE# = 5
> AND O.OWNER# = U.USER#
> and o.owner# in ( select kzsrorol from x$kzsro )
> ;
>
> CREATE VIEW SYSTABAUTH
> (GRANTEE, TCREATOR, TTNAME, UPDATECOLS, SELECTCOLS, ALTERAUTH, DELETEAUTH,
> INDEXAUTH, INSERTAUTH, SELECTAUTH, UPDATEAUTH)
> AS
> SELECT UE.NAME, U.NAME, O.NAME,
> decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'),
> 5, 2),'01', 'N', '11', 'N',
> decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
> 12, '0'), 2, 1), '0', 'N', '*')),
> decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'),
> 3, 2), '01', 'N', '11', 'N',
> decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
> 12, '0'), 1, 1), '0', 'N', '*')),
> decode(substr(lpad(sum(power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2+1))), 26, '0'), 25, 2),
> '00', 'N', '01', 'Y', '11', 'Y', 'N'),
> decode(substr(lpad(sum(power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2+1))), 26, '0'), 19, 2),
> '00', 'N', '01', 'Y', '11', 'Y', 'N'),
> decode(substr(lpad(sum(power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2+1))), 26, '0'), 15, 2),
> '00', 'N', '01', 'Y', '11', 'Y', 'N'),
> decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'),
> 13, 2), '01', 'Y', '11', 'Y',
> decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
> 12, '0'), 6, 1), '0', 'N', 'Y')),
> decode(substr(lpad(sum(power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 7, 2),
> '00', 'N', '01', 'Y', '11', 'Y', 'N'),
> decode(substr(lpad(sum(decode(col#, null, power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2 + 1)), 0)), 26, '0'),
> 5, 2),'01', 'Y', '11', 'Y',
> decode(substr(lpad(sum(decode(col#, null, 0, power(10, privilege#))),
> 12, '0'), 2, 1), '0', 'N', 'N'))
> FROM SYS.OBJAUTH$ OA, SYS.OBJ$ O, SYS.USER$ U, SYS.USER$ UR, SYS.USER$ UE
> WHERE OA.OBJ# = O.OBJ#
> AND OA.GRANTOR# = UR.USER#
> AND OA.GRANTEE# = UE.USER#
> AND U.USER# = O.OWNER#
> and (oa.grantor# = uid or
> oa.grantee# in (select kzsrorol from x$kzsro) or
> o.owner# = uid)
> group by u.name, o.name, ur.name, ue.name
> ;
>
> CREATE VIEW SYSCOLAUTH
> (GRANTEE, CREATOR, PRIVILEGE, TNAME, COLNAME)
> AS
> SELECT UE.NAME, U.NAME,
> decode(substr(lpad(sum(power(10, privilege#*2) +
> decode(option$, null, 0, power(10, privilege#*2 + 1))), 26, '0'), 5, 2),
> '00', 'SELECT', '01', 'UPDATE', '11', 'UPDATE', 'SELECT'),
> O.NAME, C.NAME
> FROM SYS.OBJAUTH$ OA, SYS.COL$ C, SYS.OBJ$ O, SYS.USER$ U, SYS.USER$ UR, SYS.USER$ UE
> WHERE OA.COL# is not null
> and OA.OBJ# = C.OBJ#
> AND OA.COL# = C.COL#
> and OA.OBJ# = O.OBJ#
> AND OA.GRANTOR# = UR.USER#
> AND OA.GRANTEE# = UE.USER#
> AND U.USER# = O.OWNER#
> and (oa.grantor# = uid or
> oa.grantee# in (select kzsrorol from x$kzsro) or
> o.owner# = uid)
> group by u.name, o.name, c.name, ur.name, ue.name
> ;
>
> DROP PUBLIC SYNONYM SYSTABLES;
> DROP PUBLIC SYNONYM SYSTABLES1;
> DROP PUBLIC SYNONYM SYSCOLUMNS;
> DROP PUBLIC SYNONYM SYSINDEXES;
> DROP PUBLIC SYNONYM SYSKEYS;
> DROP PUBLIC SYNONYM SYSTABAUTH;
> DROP PUBLIC SYNONYM SYSCOLAUTH;
> DROP PUBLIC SYNONYM SYSSYNONYMS;
>
> CREATE PUBLIC SYNONYM SYSTABLES FOR SYSADM.SYSTABLES;
> CREATE PUBLIC SYNONYM SYSTABLES1 FOR SYSADM.SYSTABLES1;
> CREATE PUBLIC SYNONYM SYSCOLUMNS FOR SYSADM.SYSCOLUMNS;
> CREATE PUBLIC SYNONYM SYSINDEXES FOR SYSADM.SYSINDEXES;
> CREATE PUBLIC SYNONYM SYSKEYS FOR SYSADM.SYSKEYS;
> CREATE PUBLIC SYNONYM SYSTABAUTH FOR SYSADM.SYSTABAUTH;
> CREATE PUBLIC SYNONYM SYSCOLAUTH FOR SYSADM.SYSCOLAUTH;
> CREATE PUBLIC SYNONYM SYSSYNONYMS FOR SYSADM.SYSSYNONYMS;
--
Regards

Pete

Received on Tue Aug 10 1999 - 11:39:07 CDT