This discussion is archived
11 Replies Latest reply: Feb 10, 2012 4:51 PM by jschellSomeoneStoleMyAlias RSS

Oracle is adding "RowId" to select statement => ORA-00918 follows

557295 Newbie
Currently Being Moderated
Hi,

i've a problem with a strange case:

Oracle adds a rowId column to the select - and this select has joins to 2 other tables and a ora-00918 is the result!

But my application don't throw a java-excpetion - this appears only in the database log files - in the application, no problem is determined!

Database is suffering under this statement because in an hour, 1800 statements are failing... so what could this be and how i can prevent it to add this column!?


select rowid, t1.name, .......... from t1, t2, t3 etc....

I use the following statement:
Statement selStmt = stmt.getConnection().createStatement(
               ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

Thx for help!

lg
Manny
  • 1. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    Tolls Journeyer
    Currently Being Moderated
    What do you mean "Oracle adds a rowId"?

    If the above is your SQL statement then you are adding the rowId, not Oracle.

    What is the full error message and the full SQL?
  • 2. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    557295 Newbie
    Currently Being Moderated
    my select looks like:

    select pa.name, sel.status, pa.taken from table1 pa, table2 sel where ............

    this select will be put into executeQuery...

    but in database log (trace) files, there are a lot of error statements which looks like:

    select rowid, pa.name, sel.status, pa.taken from table1 pa, table2 sel where ............

    this leads to an "ORA-918 column ambiguously defined" error because there is a join to another table and rowid needs e.g the pa prefix!

    so - my question, why this rowid is added to the select - my source code wasn't it....

    In the internet, a can read a little bit about the resultSet (scroll type sensitive and downgrading) but how to prevent this - i don't.
  • 3. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    Tolls Journeyer
    Currently Being Moderated
    OK.
    What version of everything are you using?
    Oracle?
    JDBC Driver?
    Java?

    Are you sure that all of your columns are referenced correctly in your SQL?
    I ask because if this error is caused by Oracle doing something strange with rowIds then I would expect there to be rather more noise about this that just you.
  • 4. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    I ask because if this error is caused by Oracle doing something strange with rowIds then I would expect there to be rather more noise about this that just you.
    I have seen this behavior before (a ROWID showing up where it shouldn't). But that was only when using Hibernate, it should not be happening if you use JDBC directly.
  • 5. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    rp0428 Guru
    Currently Being Moderated
    Tough to help if you don't provide you Java and OS infor and the Oracle JDBC driver version.

    Post all of the relevant Java code that is used to setup and execute the prepared statement and handle the result set.

    The Oracle JDBC driver uses ROWID to support scroll sensitive or updatable result sets so it is probably being added for this reason.
    Remove the 'TYPE_SCROLL_SENSITIVE' setting and see if the problem goes away.

    Seacrh for ROWID in the JDBC Dev guide (Chap 17) - http://www.stanford.edu/dept/itss/docs/oracle/10g/java.101/b10979/resltset.htm

    >
    17.1.2.2 Oracle JDBC Implementation for Result Set Updatability

    To support updatability, Oracle JDBC uses ROWIDs to uniquely identify database rows that appear in a result set. For every query into an updatable result set, the Oracle JDBC driver automatically retrieves the ROWID along with the columns you select.
    >

    And since you are trying to produce a scroll sensitive result set you should do a test as described in this doc hint:
    >
    There is a simple way to determine if your query will probably produce a scroll-sensitive or updatable result set: If you can legally add a ROWID column to the query list, then the query is probably suitable for either a scroll-sensitive or an updatable result set. (You can try this out using SQL*Plus, for example.)
    >

    and:
    >
    The Oracle JDBC drivers use the ROWID to uniquely identify a row in a database table. As long as the ROWID is still valid when a driver tries to send an UPDATE or DELETE operation to the database, the operation will be executed.
    >

    Hope that helps.
  • 6. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    Tolls Journeyer
    Currently Being Moderated
    gimbal2 wrote:
    I have seen this behavior before (a ROWID showing up where it shouldn't). But that was only when using Hibernate, it should not be happening if you use JDBC directly.
    I did think of Hibernate but, as you say, this is clearly JDBC stuff.

    However, rp0428's post has filled a hole in my knowledge, and also highlights why I avoid using anything other than fairly basic ResultSets.
    I don't think I've ever used a scroll sensitive one, and I know I've not used an updateable one.
  • 7. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    However, rp0428's post has filled a hole in my knowledge, and also highlights why I avoid using anything other than fairly basic ResultSets.
    I don't think I've ever used a scroll sensitive one, and I know I've not used an updateable one.
    Same here, it makes lots of sense especially when I compare to usage in for example PL/SQL where you have to add ROWID to make a record updateable (or add "FOR UPDATE" but I believe that locks rows or even the whole table).
  • 8. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    rp0428 Guru
    Currently Being Moderated
    Could be a little off topic I guess but Sqldeveloper can also adds ROWID to queries to make updateable result sets.

    Usually this is not a problem but if the object being queried is a view the needed rowid might not be available. This can cause a similar problem especially if the view has an INSTEAD Of trigger. Here's an old post but there are others: SQL Developer adding rowid column to view query causes ORA-01445

    Like you guys keep trying to tell folks they need to check the docs first and then try simple changes to see what effect they have.
  • 9. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    557295 Newbie
    Currently Being Moderated
    Hey guys,

    i've solved the problem.

    This rowid is added through Scroll type Sensitive of resultSet!

    the exception is not catched in java but from the jdbc driver i think... first, rowid is added and the stmt is executed -

    ora00918 is thrown... the stmt is downgraded to SCROLL Type INSENSITIVE and it succeeds..

    even define as insensitive solved the problem!:)

    thx @ all
  • 10. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    Tolls wrote:
    ...and also highlights why I avoid using anything other than fairly basic ResultSets.
    I don't think I've ever used a scroll sensitive one, and I know I've not used an updateable one.
    Ditto.

    And far as I can tell the only reason to use them is in an attempt to get the database to do work that the GUI should be doing.
  • 11. Re: Oracle is adding "RowId" to select statement => ORA-00918 follows
    rp0428 Guru
    Currently Being Moderated
    >
    i've solved the problem.

    This rowid is added through Scroll type Sensitive of resultSet!
    >
    That's what I said in my post above:
    >
    The Oracle JDBC driver uses ROWID to support scroll sensitive or updatable result sets so it is probably being added for this reason.
    Remove the 'TYPE_SCROLL_SENSITIVE' setting and see if the problem goes away.
    >
    Glad that solved your problem!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points