Displaying RowID in Select * (all) Statement

Question

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

Swen

ROWIDTOCHAR can be used to display a rowid. select ROWIDTOCHAR(rowid) from <table name>; – Kassandrakassaraba

@Kassandrakassaraba - Is there an advantage to using ROWIDTOCHAR when displaying ROWID? – Cheddar

It helps because it converts it to a VARCHAR2 type. If you want to do something like this SELECT ROWID FROM <tablename> WHERE ROWIDTOCHAR(ROWID) LIKE '%<string>%'; – Kassandrakassaraba

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? techonthenet.com/sql/minus.php – Suctorial

@Kassandrakassaraba - That sounds like it could be helpful in the future. Thanks for sharing. – Cheddar

@Suctorial - 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. – Cheddar

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. – Cheddar

I replaced ROWID with ROWIDTOCHAR(ROWID) in each SELECT query, and it works now. – Cheddar

Answer 1

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.

Oxidase

Thanks. I will try that. I was under the impression that ROWID is generated in a sequential order. Thanks for clarifying. – Cheddar

@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. – Oxidase

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. – Cheddar

This is a good article and can come in handy. adp-gmbh.ch/ora/concepts/rowid.html – Kassandrakassaraba