ORA-00904 Invalid column when column exists
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-00904 Invalid column when column exists

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525

    ORA-00904 Invalid column when column exists

    Hi I might be missing something obvious here but I'm bashing my head against a wall on this problem.

    I have two users A and B, user B has two tables PUB and DETAIL which have SELECT granted to user A and have the following query:

    Code:
    SELECT 
      DISTINCT d.adno,d.vno,d.pubno,p.bwnet
    FROM B.DETAIL d, B.PUB p
    WHERE TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
    AND TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
    AND d.billflag = 'Y'
    AND d.adno NOT IN
      (SELECT DISTINCT adno
       FROM B.DETAIL
       WHERE TO_DATE(pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
       AND TO_DATE(pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
       AND (lastbillflag = '1' OR lastbillflag = '999'))
    AND d.adno = p.adno
    AND d.vno = p.vno
    AND d.pubno = p.pubno;
    When I run this code as user B (the object owner) it works fine, but when I run it as user A I get

    Code:
    SELECT DISTINCT d.adno,d.vno,d.pubno,p.bwnet
          *
    ERROR at line 1:
    ORA-00904: invalid column name
    Any help would be appreciated
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Forgot to mention this is 8.1.7.4 on Solaris
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    I know it sounds stupid, but you can describe those tables from user A cant you?

    also
    Code:
    WHERE TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
    AND TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
    Whats the point of the second and?

    it it passes the first one, no need to test for the second one and it
    Last edited by davey23uk; 11-17-2004 at 01:47 PM.

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Hi,

    yes I can describe the tables.

    Appreciate your comment about the crap code (it's from one of our developers) but at present I'm more concerned about why the statement fails when the columns are availiable.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Jim, since the geatest minds on the internet haven't come up with a solution in two days, I'll have go:

    - we are talking about a straight query NOT a proc (grants via roles and all that)?

    - where does it start to go wrong if you take it apart?
    1)
    SELECT * FROM B.DETAIL
    SELECT * FROM B.PUB
    2)
    SELECT * FROM B.DETAIL d, B.PUB p
    WHERE d.adno = p.adno
    AND d.vno = p.vno
    AND d.pubno = p.pubno;
    3)
    orginal query without the NOT IN (SELECT . . . ) clause

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Hi Dapi,

    Sorry about the delay in responding....

    The problem seems to lie with the B.PUB table, if I attempt any sort of select including a count(*) I get the same error. but only if I run the queries as user A

    This is a straight forward SQL*Plus query and all the grants are direct.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hmmmmmmm...... baffling.

    I'd look next at the data dict for oddities: are the grants really there? does user A have an object called PUB? what does B.PUB look like?

    Raise a TAR?

  8. #8
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Thanks Dapi but I've finally found the problem.

    On closer inspection of that table I discovered it has a function based index based on a function in schema A. Directly granting execute to that function to user B now allows user A to run the query.

    Can't believe I didn't spot it sooner, D'oh!

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width