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.

Answer 2

You can use a JOIN

select * 
from TABLE a 
     join (select chartorowid('AAAEqwAAEAAAAD/AAA') rid from dual) b 
         on b.rid=a.rowid;

Answer 3

ROWIDs are a special data type not a string. That's we need to use the ROWIDTOCHAR() function.

As the ROWID identifies a specific row in a specific table why would you expect the ROWID from DUAL to match anything in any other table?

ROWID is the faster way of accessing a row. But it is highly unusual to need to wrangle ROWIDs as strings. The more regular way of doing this would be something like:

declare    
    lv_row_id rowid;
    l_blah t23.whatever%type;
begin
    ....
    select rowid into lv_row_id 
    from t23
    where pk_col = 42;

    do_some_stuff;

    update t23
    set whatever = l_blah
    where rowid = lv_row_id;
    ....

But even more normal would be to use the SELECT ... FOR UPDATE syntax, which implicitly uses ROWID without us having to bother.

So, given that what you are trying to do is a bit unusual I think you should explain a bit more about your goals. That way we can help you find the best way of achieving them.

Answer 4

Just a hint:

You wrote "I do not known where the given rowid comes from.".

Well, DBMS_ROWID.ROWID_OBJECT will give you id of the object (and then you can find the object in ALL_OBJECTS view).

Anyway, it seems that although it is not documented you will get the ORA-01410 error each time when you try to use rowid from one table in query against another table. So instead of trying to force oracle to change its behaviour, you can simply wrap your query with some procedural code like:

BEGIN
   SELECT INFO_ID INTO yourvariable
     FROM TM_INFO_CATALOG  
    WHERE ROWID IN (yourrowid);

   do_something_with_yourvariable;

EXCEPTION
  WHEN invalidrowid THEN
     NULL;
END;
/

or

BEGIN
   IF DBMS_ROWID.ROWID_OBJECT(:yourrowid) = id_of_TM_INFO_CATALOG THEN
      SELECT INFO_ID INTO yourvariable
        FROM TM_INFO_CATALOG  
       WHERE ROWID IN (yourrowid);

      do_something_with_yourvariable;
   END IF;
END;
/

Answer 5

It sounds like you may be trying to use ROWIDs to store references between tables. Perhaps you've stored the ROWIDs from one table in another table?

If this is the case, this approach is not correct. ROWIDs are physical pointers and can change without notification. I'm not aware of any situation where it is useful to store ROWIDs as data in any table.

Referential integrity between tables should be implemented by storing a unique identifier (i.e. the column(s) from the target table that have a UNIQUE constraint defined).