RowID contains detailed information on records, but this string number is generally unattainable, and RowId in Informix is different. However, this information can be obtained through the DBMS_ROWID package in Oracle. This article describes the use of the package through a defined custom function.
Function body follows: create or replace function get_rowid (l_rowid in varchar2) return varchar2isls_my_rowid varchar2 (200); rowid_type number; object_number number; relative_fno number; block_number number; row_number number; begin dbms_rowid.rowid_info (l_rowid, rowid_type, object_number, relative_fno, block_number , row_number); ls_my_rowid: = 'Object # IS:' || to_CHAR (Object_Number) || CHR (10) || 'relative_fno is:' || to_char (relative_fno) || CHR (10) || 'Block Number IS : '|| To_Char (Block_Number) || CHR (10) ||' Row Number IS: '|| to_CHAR (Row_Number); Return Ls_my_RowID; END; /
Let's take a look at your usage:
SQL> create or replace function get_rowid 2 (l_rowid in varchar2) 3 return varchar2 4 is 5 ls_my_rowid varchar2 (200); 6 rowid_type number; 7 object_number number; 8 relative_fno number; 9 block_number number; 10 row_number number; 11 begin 12 dbms_rowid. rowid_info (l_rowid, rowid_type, object_number, relative_fno, block_number, row_number); 13 ls_my_rowid: = 'Object # is:' || to_char (object_number) || chr (10) || 14 'relative_fno is:' || to_char (relative_fno ) || CHR (10) || 15 'Block Number IS:' || to_CHAR (Block_Number) || CHR (10) || 16 'Row Number IS:' || to_CHAR (Row_Number); 17 RETURN LS_MY_ROWID; 18 END 19 /
Function created.
SQL> SELECT ROWID, Service_ID from service_bean where service_id = 155;
ROWID SERVICE_ID ------------------ ---------- AAAhZRAAaAAAAaKAC7 155SQL> select get_rowid (AAAhZRAAaAAAAaMADi) rowid from dual; select get_rowid (AAAhZRAAaAAAAaMADi) rowid from dual * Error is located in Chapter 1: ORA-00923: From Keyword Not Found Where Expected
SQL> select get_rowid (AAAhZRAAaAAAAaMADi) row_id from dual; select get_rowid (AAAhZRAAaAAAAaMADi) row_id from dual * ERROR at line 1: ORA-00904: "AAAHZRAAAAAAAAMADI": invalid identifier
SQL> Select get_rowid (aaahzraaaaaaamadi) from dual; select get_rowid (aaahzraaaaaaaamadi) from Dual * Error is located in Chapter 1: ORA-00904: "Aaahzraaaaaaamadi": Invalid Identifier
SQL> select get_rowid ( 'AAAhZRAAaAAAAaMADi') rowid from dual; select get_rowid ( 'AAAhZRAAaAAAAaMADi') rowid from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected
SQL> SELECT GET_ROWID ('AaahzraaaaaaAamadi') row_id from dual;
Row_id ------------------------------------------------- ---------------
Object # is: 136785RELATIVE_FNO IS: 26BLOCK NUMBER IS: 1676ROW NUMBER IS: 226
Where Object # is
Select Object_id from all_Objects where object_name = 'service_bean';
Relative_fno is the number of the data file where the table is located
The original text of this knowledge point is here http://blog.9cbs.net/eygle/archive/2004/11/09/174061.aspx
