9.1.3.1.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_BLOCK_NUMBER:

PRAGMA RESTRICT_REFERENCES (ROWID_BLOCK_NUMBER, WNDS, RNDS, WNPS, RNPS);

9.1.3.2.1 Restrictions

The DBMS_ROWID package supplies the following pragma for CREATE_ROWID:

PRAGMA RESTRICT_REFERENCES (CREATE_ROWID, WNDS, RNDS, WNPS, RNPS);

9.1.3.2.2 Example

Here is an example of a call to the ROWID_CREATE procedure:

   my_rowid ROWID;
BEGIN 
   my_rowid := DBMS_ROWID.ROWID_CREATE 
     (DBMS_ROWID.ROWID_TYPE_EXTENDED, 100, 15, 103, 345);
END;
/

9.1.3.3.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_INFO:

PRAGMA RESTRICT_REFERENCES (ROWID_INFO, WNDS, RNDS, WNPS, RNPS);

9.1.3.4.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_OBJECT:

PRAGMA RESTRICT_REFERENCES (ROWID_OBJECT, WNDS, RNDS, WNPS, RNPS);

9.1.3.4.2 Example

You will want to obtain a ROWID's object number only if the ROWID type is extended. You would write code like this to perform that check:

IF DBMS_ROWID.ROWID_TYPE (v_rowid) = DBMS_ROWID.ROWID_TYPE_EXTENDED 
THEN
   v_objnum := DBMS_ROWID.ROWID_OBJECT (v_rowid);
END IF;

9.1.3.5.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_RELATIVE_FNO:

PRAGMA RESTRICT_REFERENCES (ROWID_RELATIVE_FNO, WNDS, RNDS, WNPS, RNPS);

9.1.3.6.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_ROW_NUMBER:

PRAGMA RESTRICT_REFERENCES (ROWID_ROW_NUMBER, WNDS, RNDS, WNPS, RNPS);

9.1.3.7.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_RELATIVE_FNO:

PRAGMA RESTRICT_REFERENCES (ROWID_RELATIVE_FNO, WNDS, WNPS, RNPS);

9.1.3.8.1 Restrictions

Note the following restrictions on calling ROWID_TO_EXTENDED:

• The ROWID_TO_EXTENDED function returns a ROWID in the extended character format. If you provide a NULL ROWID, the function will return NULL. If a zero-valued ROWID is supplied (00000000.0000.0000), a zero-valued restricted ROWID is returned.

• The DBMS_ROWID package supplies the following pragma for ROWID_TO_EXTENDED:

  PRAGMA RESTRICT_REFERENCES (ROWID_TO_EXTENDED, WNDS, WNPS, RNPS);

• If the schema and object names are provided as IN parameters, this function first verifies that you have SELECT privilege on the table named. It then converts the restricted ROWID provided to an extended ROWID, using the data object number of the specified table. Even if ROWID_TO_EXTENDED returns a value, however, that does not guarantee that the converted ROWID actually references a valid row in the table, either at the time that the function is called, or when the extended ROWID is actually used. It is only performing a conversion.

• If the schema and object name are not provided (i.e., are passed as NULL), then this function attempts to fetch the page specified by the restricted ROWID provided. It treats the file number stored in this ROWID as the absolute file number. This may cause problems if the file has been dropped, and its number has been reused prior to the data migration. If the fetched page belongs to a valid table, the data object number of this table is used in converting to an extended ROWID value.

  This approach is very inefficient. Oracle recommends doing this only as a last resort, when the target table is not known. Note that the user must still be aware of the correct table name when using the converted ROWID.

• If an extended ROWID value is supplied, that ROWID's data object is verified against the data object number calculated from the table name argument. If the two numbers do not match, DBMS_ROWID raises the INVALID_ROWID exception. If there is a match, then the input ROWID is returned.

9.1.3.8.2 Example

Suppose that I have a table in my APP schema called ROWID_conversion. This table contains two columns: ROWID_value and table_name. The ROWID_value column contains the restricted-format ROWIDs for rows in the table specifed by the table_name column. I can then convert all of my restricted ROWID values to extended ones with the following UPDATE statement:

UPDATE app.rowid_conversion
   SET rowid_value = 
       DBMS_ROWID.ROWID_TO_EXTENDED 
          (rowid_value, 
           'APP', 
           table_name,
           DBMS_ROWID.ROWID_CONVERT_INTERNAL);.

9.1.3.9.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_TO_RESTRICTED:

PRAGMA RESTRICT_REFERENCES 
   (ROWID_TO_RESTRICTED, WNDS, RNDS, WNPS, RNPS);

9.1.3.10.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_TYPE:

PRAGMA RESTRICT_REFERENCES (ROWID_TYPE, WNDS, RNDS, WNPS, RNPS);

9.1.3.10.2 Example

In the following query, I determine the ROWID types in the emp table:

SELECT DISTINCT (DBMS_ROWID.ROWID_TYPE(ROWID))
  FROM emp;

This returns the value of 1, that is: DBMS_ROWID.ROWID_TYPE_EXTENDED. The emp table was created under Oracle8 and therefore uses the Oracle8 type of ROWID.

9.1.3.11.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_VERIFY:

PRAGMA RESTRICT_REFERENCES (ROWID_VERIFY, WNDS, WNPS, RNPS);

9.1.3.11.2 Example

Interestingly, you can call this numeric function as a kind of Boolean function within SQL. Suppose that I want to find all the invalid ROWIDs prior to converting them from restricted to extended. I could write the following query (using the same tables used in the example for the ROWID_TO_EXTENDED function):

SELECT ROWID, rowid_value 
  FROM app.rowid_conversion
  WHERE DBMS_ROWID.ROWID_VERIFY (rowid_value, NULL, NULL, 0) = 1;