Skip to Main Content



For appeals, questions and feedback about Oracle Forums, please email Please ask technical questions in the appropriate category. Thank you!

DBMS_CLOUD.COPY_COLLECTION - Only take select records

rgiljohannMay 20 2024

I am using Autonomous Database 19c.

I have some external JSON files I am trying to load the data into the database using DBMS_CLOUD.COPY_COLLECTION or COPY_DATA. These files have a ton of records in them, but I only need about 300 of the records. The files can be as big as 200 GB each, so I really don't want to load the entire thing in for storage purposes, and it also takes a long time when I try to do that when I only need the 300 records to begin with.

I tried to create an external table, and create a new table with only the 300 records I need, but it is taking too long since it is still iterating over the entire 200 GB file with the external table.

For reference, I can filter the raw data using a json query like the one below:

JSON_VALUE(NPI,'$.billing_code') = ‘99214’

Is there a way to put that filter in a DBMS_CLOUD.COPY_COLLECTION or COPY_DATA script to only take those select records?

This post has been answered by brandon hong on May 29 2024
Jump to Answer
Post Details
Added on May 20 2024