| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-904 in EXP80
Chris
Comments inline
Pete
Christoph Kukulies wrote:
> Pete Sharman <psharman_at_us.oracle.com> wrote:
> : This is a multi-part message in MIME format.
> : --------------7C830B8C76FC2E7613ED8EB1
> : Content-Type: text/plain; charset=us-ascii
> : Content-Transfer-Encoding: 7bit
>
> : 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
>
> You mean 'my' (View8.ora) create view statements?
>
> : dictionary.
>
> Excuse my ignorance.
>
> What is catalog.sql (resp. catproc.sql)?
These two scripts build a lot of data dictionary views and PL/SQL packages. They are scripts that must be run for the database to be of any use.
> Why can't I raise some logging level and get a precise error message about what
> column name is invalid?
Here's the sort of thing I'm getting if I edit one of the create view statements you listed:
SQL> CREATE VIEW SYSTABLES (CREATOR, NAME, TYPE, REMARKS, PERCENTFREE)
2 AS
3 SELECT U.NAME, O.THIS_IS_A_TYPO,
4 DECODE(O.TYPE#, 2, 'T', 4, 'V', 5, 'S', 'S'), 5 C.COMMENT$, 6 0
8 WHERE O.OWNER# = U.USER# 9 AND O.OBJ# = C.OBJ#(+) 10 AND C.COL#(+) IS NULL 11 AND O.TYPE# IN (2, 4) 12 and o.linkname is null 13 and (o.owner# in (uid, 1) 14 or 15 o.obj# in ( select obj# 16 from sys.objauth$ 17 where grantee# in ( select kzsrorol 18 from x$kzsro 19 ) 20 ) 21 )
*
If you're using fixed font to display this, you'll see the star is under the column name "THIS_IS_A_TYPO". So you don't need to raise any logging level to get the column name.
HTH. Pete
>
>
> : 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
>
> : --------------7C830B8C76FC2E7613ED8EB1
> : Content-Type: text/x-vcard; charset=us-ascii;
> : name="psharman.vcf"
> : Content-Transfer-Encoding: 7bit
> : Content-Description: Card for Pete Sharman
> : Content-Disposition: attachment;
> : filename="psharman.vcf"
>
> : begin:vcard
> : n:Sharman;Peter
> : tel;cell:+1.650.868.9969
> : tel;fax:+1.650.633.1669
> : tel;work:+1.650.607.0109
> : x-mozilla-html:FALSE
> : url:http://www.oracle.com
> : org:Oracle University, Consulting and Vertical Education;Oracle Corporation
> : adr:;;500 Oracle Parkway M/S OPL-B1024;Redwood Shores;California;94065;USA
> : version:2.1
> : email;internet:psharman_at_us.oracle.com
> : title:Project Manager
> : note;quoted-printable:=0D=0A=0D=0A **** The statements and opinions expressed here are my **** =0D=0A **** own and do not necessarily represent those of **** =0D=0A **** Oracle Corporation. =20 ****=0D=0A=0D=0A"Controlling application developers is like herding cats." =0D=0AKevin Loney, ORACLE DBA Handbook =0D=0A=0D=0A"Oh no it's not! It's much harder than that!" =0D=0ABruce Pihlamae, long term ORACLE DBA
> : x-mozilla-cpt:;9200
> : fn:Pete Sharman
> : end:vcard
>
> : --------------7C830B8C76FC2E7613ED8EB1--
>
> --
> --Chris Christoph P. U. Kukulies kuku_at_gil.physik.rwth-aachen.de
--
Regards
Pete
Received on Wed Aug 11 1999 - 17:51:54 CDT
 |
 |