Unscoped REF Columns That Allow Primary Keys

An unscoped REF column that allows primary keys can reference both system-generated and primary key REFs. The syntax for loading into such a REF column is the same as if you were loading into a system-generated OID REF column or into a primary-key-based REF column. See Example 11-13 and Example 11-14.

The following restrictions apply when loading into an unscoped REF column that allows primary keys:

• Only one type of REF can be referenced by this column during a single-table load, either system-generated or primary key, but not both. If you try to reference both types, then the data row will be rejected with an error message indicating that the referenced table name is invalid.

• If you are loading unscoped primary key REFs to this column, then only one object table can be referenced during a single-table load. That is, to load unscoped primary key REFs, some pointing to object table X and some pointing to object table Y, you would have to do one of the following:

  • Perform two single-table loads.

  • Perform a single load using multiple INTO TABLE clauses for which the WHEN clause keys off some aspect of the data, such as the object table name for the unscoped primary key REF. For example:

    LOAD DATA 
    INFILE 'data.dat' 
    
    INTO TABLE orders_apk 
    APPEND 
    when CUST_TBL = "CUSTOMERS_PK" 
    fields terminated by "," 
    ( 
      order_no   position(1)  char, 
      cust_tbl FILLER     char, 
      cust_no  FILLER     char, 
      cust   REF (cust_tbl, cust_no) NULLIF order_no='0' 
    ) 
    
    INTO TABLE orders_apk 
    APPEND 
    when CUST_TBL = "CUSTOMERS_PK2" 
    fields terminated by "," 
    ( 
      order_no  position(1)  char, 
      cust_tbl FILLER     char, 
      cust_no  FILLER     char, 
      cust   REF (cust_tbl, cust_no) NULLIF order_no='0' 
    ) 
    

  If you do not use either of these methods, then the data row will be rejected with an error message indicating that the referenced table name is invalid.

• Unscoped primary key REFs in collections are not supported by SQL*Loader.

• If you are loading system-generated REFs into this REF column, then any limitations described in "System-Generated OID REF Columns" also apply here.

• If you are loading primary key REFs into this REF column, then any limitations described in "Primary Key REF Columns" also apply here.

  Note:

  For an unscoped REF column that allows primary keys, SQL*Loader takes the first valid object table parsed (either from the REF directive or from the data rows) and uses that object table's OID type to determine the REF type that can be referenced in that single-table load.