21

I am trying to display the RowID alongside all columns from a Select * statement.

I am using Oracle with Toad to run the SQL statement.

I have two tables that I need to compare, but I do not have any unique identifiers to use when sorting the two tables for comparison. So I thought that using the RowID to sort the two tables in order to compare them could help.

Is there a way to add RowID to a Select * statement? I cannot add all the columns names as there are over 50 of them. I will be doing this to multiple sets of tables where the number and name of columns will vary.

Any help or ideas around this would be greatly appreciated.

Thanks in advance,

Marwan

Marwan مروان
  • 2,163
  • 8
  • 30
  • 40
  • ROWIDTOCHAR can be used to display a rowid. select ROWIDTOCHAR(rowid) from ;
     – S.P. Mar 01 '12 at 18:12
  • @S.P. - Is there an advantage to using `ROWIDTOCHAR` when displaying `ROWID`? – Marwan مروان Mar 01 '12 at 18:46
  • It helps because it converts it to a VARCHAR2 type. If you want to do something like this SELECT ROWID FROM WHERE ROWIDTOCHAR(ROWID) LIKE '%%'; – S.P. Mar 01 '12 at 18:55
  • I don't know what your table setup is like, but since you appear to be comparing the contents of similar tables, have you looked at `MINUS`? http://www.techonthenet.com/sql/minus.php – Tebbe Mar 01 '12 at 19:08
  • @S.P. - That sounds like it could be helpful in the future. Thanks for sharing. – Marwan مروان Mar 01 '12 at 19:10
  • @Tebbe - Thanks Tebbe. I started by using `MINUS` to get the 'offending' rows, but then I have to go through the data to figure out what the discrepancy is between the data. What is currently being used (and I am being thrown into this) is an Excel spreadsheet that does the comparison for the two tables using three tabs, one for each table, and the third for comparing the results. The users want to be able to sort the data so that they can analyze the differences in the data between the two tables. – Marwan مروان Mar 01 '12 at 19:15
  • I tried using `MINUS` with `ROWID`, and I got error **ORA-01790**, which states that the expression has different datatypes. `SELECT ROWID, abc.* FROM table_1 abc MINUS SELECT ROWID, bcd.* FROM table_2 bcd` Do I have to convert `ROWID` to a specific data type? I understood that `ROWID` is its own data type. Help. I understand that by performing the above statement/query, I will get back all the rows since the `ROWID` values will be different in both tables. – Marwan مروان Mar 01 '12 at 21:01
  • I replaced `ROWID` with `ROWIDTOCHAR(ROWID)` in each `SELECT` query, and it works now. – Marwan مروان Mar 01 '12 at 21:23

1 Answers1

46

You can do something like

SELECT rowid, a.*
  FROM table_name a

But I'm not sure that is actually going to help you. Sorting the data on ROWID is not going to be particularly useful since that is just a physical location on disk. It's just as arbitrary as presenting the data unsorted.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks. I will try that. I was under the impression that `ROWID` is generated in a sequential order. Thanks for clarifying. – Marwan مروان Mar 01 '12 at 18:19
  • 6
    @Marwan - It's definitely not guaranteed to be sequential. For small tables that are only loaded once and never modified, it may appear to be sequential but that should not be relied upon. – Justin Cave Mar 01 '12 at 18:24
  • That is good to know. Thanks for taking the time to clarify this for me. I will be working with small tables, but if they do get larger I will have to figure out what else I can do if the `ROWID` stops working for me. Thanks again. – Marwan مروان Mar 01 '12 at 19:09
  • 1
    This is a good article and can come in handy. http://www.adp-gmbh.ch/ora/concepts/rowid.html – S.P. Mar 01 '12 at 19:13