Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. 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
Comments
Post Details
Added on May 20 2024
2 comments
149 views