Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-904 in EXP80

Re: ORA-904 in EXP80

From: Christoph Kukulies <kuku_at_gil.physik.rwth-aachen.de>
Date: 11 Aug 1999 15:40:45 GMT
Message-ID: <7os5ht$t4f$1@nets3.rz.RWTH-Aachen.DE> 





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)?



Why can't I raise some logging level and get a precise error message about what
column name is invalid?




: 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
Received on Wed Aug 11 1999 - 10:40:45 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US