SQL 如何从表 (Oracle) 中获取具有给定 rowid 列表 IN STRING 的记录？

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

提问by btpka3

Any one can help me to resolve the FIXME ?

任何人都可以帮助我解决 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 emphasizethe 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?

我想我需要强调一点：
如果rowid与给定的rowid匹配，我只想从表（例如TABLE_A）中选择记录。
当所有给定的 rowid 都来自 TABLE_A（要查询的）时，就可以了。
但是，只要给定的 rowid 来自其他表（TABLE_B 或 DUAL，例如），就会发生“ORA-01410 invalid ROWID”。我想修复这个问题。
我希望有人可以运行第四条 SQL（或具有相同模式的另一个 SQL），然后给我你的解决方案。还有，第三条和第四条SQL语句除了一个是SQLID类型，另一个是STRING类型之外，有什么区别呢？如何解决第四条SQL的问题？

回答by APC

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

ROWID 是一种特殊的数据类型，而不是字符串。那就是我们需要使用的ROWIDTOCHAR()功能。

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 标识特定表中的特定行，为什么您希望 DUAL 中的 ROWID 匹配任何其他表中的任何内容？

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:

ROWID 是访问行的更快方式。但是需要将 ROWID 作为字符串来处理是非常不寻常的。更常规的方法是：

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.

但更正常的是使用SELECT ... FOR UPDATE 语法，它隐式使用 ROWID 而我们不必费心。

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.

因此，鉴于您尝试做的事情有点不寻常，我认为您应该多解释一下您的目标。这样我们就可以帮助您找到实现这些目标的最佳方式。

回答by Mark Baker

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

假设您有“Oracle 呈现”格式的 ROWID，它看起来像这样：

AAACiZAAFAAAAJEAAA

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

Oracle 格式是 Base64 字符串编码。从 Oracle 中选择 ROWID 将导致该值的 Base64 显示。

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)

1. 对象的数据对象编号
2. 行所在的数据文件（第一个文件是 1）。
3. 行所在的数据文件中的数据块
4. 该行在数据块中的位置（第一行为0）

The format is: OOOOOO.FFF.BBBBBB.RRR

格式为：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.

数据文件编号在数据库中是唯一的。您可以从 DBA_DATA_FILES 视图中检索它。每个数据文件被分成块，dba_extents 表将为您提供记录的segment_name 和segment_type。

回答by Gary Myers

You can use a JOIN

您可以使用 JOIN

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

回答by Marcin Wroblewski

Just a hint:

只是一个提示：

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

您写道“我不知道给定的 rowid 来自哪里。”。

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

好吧，DBMS_ROWID.ROWID_OBJECT 会给你对象的 id（然后你可以在 ALL_OBJECTS 视图中找到对象）。

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:

无论如何，似乎虽然没有记录在案，但每次尝试使用一个表中的 rowid 来查询另一个表时，您都会收到 ORA-01410 错误。因此，与其试图强制 oracle 改变其行为，您可以简单地用一些程序代码包装您的查询，例如：

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;
/

回答by Jeffrey Kemp

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?

听起来您可能正在尝试使用 ROWID 来存储表之间的引用。也许您已经将一张表中的 ROWID 存储在另一张表中？

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.

如果是这种情况，这种方法是不正确的。ROWID 是物理指针，可以在不通知的情况下更改。我不知道在任何情况下将 ROWID 作为数据存储在任何表中是有用的。

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

表之间的参照完整性应通过存储唯一标识符（即目标表中UNIQUE定义了约束的列）来实现。