4 Replies Latest reply on Jun 10, 2020 3:23 PM by martv

    Copy SODA collection

    martv

      Hi,

      I want to copy, export/import, data from one soda-collection into another soda-collection, how can I do so?

        • 1. Re: Copy SODA collection
          MaxOrgiyan-Oracle

          Thanks for your question. I am from the SODA team, we can get you an answer, but two questions for you first: do you want to do this from SQL/PLSQL or some other SODA driver (Java, etc)? Is the target collection empty or has data it in already?

          • 2. Re: Copy SODA collection
            martv

            Some background: suppose a sub-contractor has created some data as JSON in SODA and wants to send this data to the ordering customer.

            1) Would be nice to have functionality so that sub-contrator can export the data and ordering cutomer can import the data.

            OR

            2) sub-contrator copy data in target soda-collection and ordering customer gets access to target-collection.

             

             

            - do you want to do this from SQL/PLSQL or some other SODA driver (Java, etc)?

            Solution by REST or SQL/PLSQL.

             

             

            - Is the target collection empty or has data it in already?

               Mightbe empty or not, but for now let's suppose it's empty.

            • 3. Re: Copy SODA collection
              MaxOrgiyan-Oracle

              For both (1) and (2), based on the use-case you describe, I am assuming the original and the target collections are in different DB instances (not necessarily even on the same machine), correct?

               

              If that's the case, the standard way to do that is to use Oracle datapump (expdp to export and impdp to import).

               

              While datapump is not yet aware of SODA collections (that's on the roadmap), collections are backed by regular Oracle tables, so you can move the data between collections' tables. In particular, one easy way to do this is to make sure the target collection exists before moving the data, and use "DATA_ONLY" mode of datapump to move the data.

               

              Does this answer your question? Please let me know.

               

              Example:

               

              (1) Note that to perform datapump export/import you'll need to know the name of that table backing the collection. You can get the name of the table backing the collection as well as the schema in which it resides by running the following when connected as the schema that owns the collection:

               

              select object_name, object_schema from user_soda_collections;

               

              (2) On the source DB (assume we're exporting a collection backed by a table named 'MYCOL' in schema scott):

               

              CREATE OR REPLACE DIRECTORY test_dir AS '/directoryPathHere';

              grant read, write on directory test_dir to scott;

               

              expdp scott/tiger@host:port/serviceNameHere tables="MYCOL" directory=TEST_DIR dumpfile=mycol.dmp logfile=mycol.exp.log

               

              You should have mycol.dmp (the datapump dump file) and mycol.exp.log (the log file) in the specified directory.

               

              (3) On the target DB (exporting into scott, with target collection backed by table name 'MYCOL'):

               

              CREATE OR REPLACE DIRECTORY test_dir AS '/directoryPathHere';

              grant read, write on directory test_dir to scott;

               

              Move the mycol.dmp file created by the export and place it into the input directory. Then do the import from that dump file:

               

              impdp scott/tiger@host:port/serviceNameHere  tables="MYCOL" content=DATA_ONLY directory=TEST_DIR dumpfile=mycol.dmp logfile=mycol.imp.log

              • 4. Re: Copy SODA collection
                martv

                Thanks, this works fine for me.