HOW TO get records with given rowid list IN STRING from a table (Oracle)?

Question

Any one can help me to resolve the FIXME ?

-- Task: Get records with given rowid IN STRING from a table.
-- NOTICE: I do not known where the given rowid comes from.

-- OUTPUT 'AAAAB0AABAAAAOhAAA'
SELECT ROWID FROM DUAL; 
-- OK, one record
SELECT * FROM DUAL WHERE ROWID IN ('AAAAB0AABAAAAOhAAA');
-- run with no errors, and no records
SELECT INFO_ID FROM TM_INFO_CATALOG  WHERE ROWID IN (SELECT ROWID FROM DUAL);
-- ERROR: ORA-01410 invalid ROWID, WHY ?????????? (This is my sql statement)
SELECT INFO_ID FROM TM_INFO_CATALOG  WHERE ROWID IN ('AAAAB0AABAAAAOhAAA');  -- FIXME

-- Question: How to check an rowid is exists in a table?

-- The following is my way:
-- FIRST, I need check whether the given rowid is from the table to query.
-- OK, but, low performance, as using function 'ROWIDTOCHAR()' (I think so.)
SELECT 1 FROM TM_INFO_CATALOG WHERE 'AAAAB0AABAAAAOhAAA' IN (SELECT ROWIDTOCHAR(ROWID) FROM TM_INFO_CATALOG);
-- ERROR: ORA-01410 
SELECT 1 FROM TM_INFO_CATALOG WHERE 'AAAAB0AABAAAAOhAAA' IN (SELECT ROWID FROM TM_INFO_CATALOG);

-- THEN, select the record using the exist rowid
-- SELECT * from TM_INFO_CATALOG WHERE ROWID = %theGivenRowIdWhichExistInThisTable%

---

I think I need to emphasize the point:
I just want select the records from a table(such as TABLE_A), if the rowid matches the given rowid.
When all given rowid comes from TABLE_A (which to query), then it is all right.
But, as long as one given rowid comes from other tables (TABLE_B or DUAL， such as), then "ORA-01410 invalid ROWID" occured. I want to FIX this problem.
I wish someone could run the fouth SQL (or annother SQL with the same pattern), then give me your solution. And, What is the difference between the third and the fourth SQL statement except that one is in SQLID type while the other is in STRING type? HOW TO fix the the fourth SQL's problem?

Answer 1

Assuming you have the ROWID in its "Oracle presented" format, it looks like this:

AAACiZAAFAAAAJEAAA

The Oracle format is a Base64 string encoding. Selecting a ROWID from Oracle will result in a Base64 display of the value.

Four pieces of data are encoded in this structure:

1. The data object number of the object
2. The datafile in which the row resides (first file is 1).
3. The data block in the datafile in which the row resides
4. The position of the row in the data block (first row is 0)

The format is: OOOOOO.FFF.BBBBBB.RRR

  OOOOOO is the object ID
  FFF is the file number
  BBBBBB is the block number
  RRR is the row number

The datafile number is unique in the database. You can retrieve it from the DBA_DATA_FILES view. Each datafile is broken into blocks, and the dba_extents table will give you a segment_name and segment_type for the record.