1

I am trying to export a dataset from Toad to Excel (or text delimited file). I need the ROWID to display in the resulting file.

I have "Show ROWID in editable grids" selected in View >> Toad Options >> Data Grids >> Data.

ROWID is being displayed in the Data tab of Schema Browser in Toad.

When I run Export Data from Schema Browser; under the Dataset tab the ROWID is being displayed in the SQL statement.

But when I export the dataset, I do not get the ROWID showing in the resulting (output) file.

I am running Toad 10.5.0.41.

Any advice on what I am missing?

Thanks in advance,

Marwan

Marwan مروان
  • 2,163
  • 8
  • 30
  • 40
  • I just figured this out, so I thought I would share the answer with everyone. Click on View/Edit Query, then modify the SQL query so that the `ROWID` has an alias, example: `SELECT ROWID as ROWID_COL, FIRST_COL FROM TABLE_NAME` When the data is exported through the Export Dataset button, the ROWID will be included in the resulting file. I hope this helps. – Marwan مروان Mar 05 '12 at 21:14
  • Just curious, why would you want to export the ROWID's? – Jeffrey Kemp Mar 06 '12 at 03:50
  • @Jeffrey - I have been asked to compare two tables (with no primary keys) for differences. I thought that I might be able to sort the tables by their `ROWID` so that I can match the columns from the different tables. But this is not working out as the tables do not have the same number of rows. If anyone has any hints or ideas on how to tackle this then I would really appreciate it. Thanks in advance. – Marwan مروان Mar 06 '12 at 17:07
  • `ROWID` is an internal pointer to a particular row in a table. If you're querying two tables, by definition every `ROWID` will be distinct, you cannot compare them. As for methods to compare: probably the easiest would be to calculate a hash for each row and do a FULL OUTER JOIN between the two tables to find matching and non-matching rows. – Jeffrey Kemp Mar 08 '12 at 12:51
  • @JeffreyKemp - I will try the FULL OUTER JOIN, but I am not sure how a hash will help me at this point since I can't seem to find any values that help distinguish rows from each other. I was using the `ROWID` to identify the a row after it has been returned in a dataset. Thanks for the information and recommendations, I will try to follow up on this. – Marwan مروان Mar 09 '12 at 17:36
  • At worst, you could use *all* the columns to generate the hash - then if the hash matches the equivalent from another row, then you can be confident that at least those two rows are identical (assuming you use a good hash algorithm). – Jeffrey Kemp Mar 10 '12 at 11:34

1 Answers1

1

Why don't you explicitly add ROWID to your SQL statement?

select rowid, t.*
from my_table t;
WoMo
  • 7,136
  • 2
  • 29
  • 36
  • That is perfect. I just was not sure where to make that change. The Export Dataset button does not allow the SQL statement to be modified. I just figured out that I can modify the SQL statement for the Export Dataset directly from the View/Edit Query button. Thanks for the hint. – Marwan مروان Mar 05 '12 at 21:20
  • 1
    The `ROWID` needs an alias so that it can show up in the exported data/output file, example: `select ROWID as ROWID_COL, t.* from MY_TABLE t;` This is specifically in relation to Toad's Export Dataset button. Thanks again for the suggestion. – Marwan مروان Mar 05 '12 at 22:39