1

I have a query running against an Oracle non-spatial database that returns thousands of records, but when I run CopyRows against that query only 24 rows are carried over! What the heck is going on? I've tried with both ArcMap Python 2.7 and Pro Python 3.6.

query = '''SELECT * FROM ... WHERE ...'''
view = arcpy.management.MakeQueryLayer(
    input_database=input_database, 
    out_layer_name=bph_layer, 
    query=query, 
    oid_fields="", 
    shape_type="", 
    srid="", 
    spatial_reference="")
arcpy.management.CopyRows(view, bph_table)

print('Records from DB ({}): {}'.format(bph_layer, arcpy.management.GetCount(bph_layer)))
print('Records in View result ({}): {}'.format(view, arcpy.management.GetCount(view)))
print('Records out to {}: {}'.format(bph_table, arcpy.management.GetCount(bph_table)))

Emits:

Records from DB (BPH_plots_for_distribution_layer): 10139
Records in View result (BPH_plots_for_distribution_layer): 10139
Records out to BPH_plots_for_distribution: 24
1

This took several hours to get to the bottom of this one. Like so many issues the cure is dead simple, but only if you know what the problem is. In this case: the problem is not specifying an Object ID field (which contains unique numeric values).

Q: So why were 24 records returned and not 3600 or ...?

A: Without an explicit ObjectID CopyRows is automatically selecting some field to use, and in this instance that column only has 24 unique values.

One attempted fix is too explicitly declare the real ID field:

view = arcpy.management.MakeQueryLayer(
    ...
    oid_fields="BPH_PLOT_ID", 
    ...)

This workded, but has the highly undesirable side effect of converting the ID field's name into OBJECTID and overwriting the original values. This is a known but not highly publicized byproduct of CopyRows tool (and probably others).

Another often suggested remedy is to CAST ROWNUM as OBJECTID. This is bad juju! When you use ROWNUM, it returns a value which may not describe a row. Imagine a selection set of the first three rows returned by a spatial query, {1,2,3}. If the query extent is changed, the selected rows will change, and so will your object id's.

To workaround that, include the unique ID field in the query twice, once as OBJECTID and again as RealID.

In this example BPH_PLOT_ID is my RealID:

query = '''SELECT
    CAST (BPH_PLOT_ID as number(38)) as OBJECTID,
    BPH_PLOT_ID, ...    
    FROM ... WHERE ...'''

view = arcpy.management.MakeQueryLayer(
    ...
    oid_fields="OBJECTID", 
    ...)

Note: Leave "objectid" in the first position, since sometimes the first NOT NULL integer gets promoted.

And finally remember in any future re-use of this derived data to only use realid and ignore objectid.

  • 1
    Casting ROWNUM is bad juju if you use it in mapping. Be careful that this solution doesn't cause a worse problem. – Vince Jan 30 at 22:19
  • @vince can you elaborate or point to where that's covered? – matt wilkie Jan 30 at 22:31
  • ...or better yet, what's the best practice method to use instead – matt wilkie Jan 30 at 22:40
  • 2
    The requirement is that the value be unique, but that the value must identify a specific row. When you use ROWNUM, it returns a value which may not describe a row. Imagine a selection set of the first three rows returned by a spatial query, {1,2,3}. If the query extent is changed, the selected rows will change. The only way to avoid this is to use a value which exists in the row. – Vince Jan 31 at 1:36
  • 1
    Yes, that's what CopyRows does. Better to include SELECT objectid,objectid as orig_id ... so the overridden value is sacrificed, but you still have a copy. Better to leave the column "objectid" in the first position, since sometimes the first NOT NULL integer gets promoted. – Vince Feb 1 at 0:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.