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 -> ORA-904 in EXP80

ORA-904 in EXP80

From: Christoph Kukulies <kuku_at_gil.physik.rwth-aachen.de>
Date: 10 Aug 1999 09:43:02 GMT
Message-ID: <7oos76$kot$1@nets3.rz.RWTH-Aachen.DE> 





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;



Received on Tue Aug 10 1999 - 04:43:02 CDT












Original text of this message













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