9 Replies Latest reply on Mar 11, 2019 6:43 PM by MaxOrgiyan-Oracle

    SODA collection on existing table

    Praveen_!

      Trying to create a collection on an existing table with no success, Is this even possible?

        • 1. Re: SODA collection on existing table
          thatJeffSmith-Oracle

          What would that mean?

           

          SODA allows you to work with documents, but under the covers, we create an oracle table for you.

           

          You're not supposed to care or even know about the table, so what would your scenario really mean?

          • 2. Re: SODA collection on existing table
            Praveen_!

            My scenario requires collection on an existing table that currently has data populated (ID, JSON_DOC, VERSION etc), It is now my understanding that creating a collection on existing table is not supported in SODA for REST but it is supported in SODA for PLSQL, however, DBMS_SODA package needed for it only exists in Oracle 18 not in Oracle 12... so  I guess my question is how does one go about creating a collection on pre-existing table which otherwise has all the required columns in Oracle 12.

            • 3. Re: SODA collection on existing table
              MaxOrgiyan-Oracle

              Hi Praveen,

               

              This is Max from the Oracle SODA team. Just saw this question. Yes, that's correct - it's not allowed thru SODA REST, and DBMS_SODA is 18 and above. One option is to use SODA Java to create the collection.

               

              If you want to create it from PLSQL, there's an internal PLSQL procedure I can point you to as a workaround on 12. I've just added you as a friend here - please accept request, and then I should be able to mail you the workaround directly.

               

              Thanks,

              Max.

              • 4. Re: SODA collection on existing table
                MaxOrgiyan-Oracle

                Jeff, just FYI - although SODA users don't have to know anything about the table if they don't want to, more advanced users can make use of it. For example, we advertise that you can run SQL queries on the underlying table (say for reporting). Or you can use custom collection metadata to change the underlying table shape and column types. And, yes, we support creating a collection on top of an existing table, as long as it has the right shape, column types, and values (and you can use custom collection metadata to tailor a collection to the SQL table you want to map it to).

                 

                This is covered, at least at a high-level, in Overview of SODA Document Collections in the Intro to SODA book.

                 

                The ability to create a collection on top of existing table is disabled in SODA REST for security reasons, but works in all other SODA implementations (Java, PLSQL, node.js, etc).

                • 5. Re: SODA collection on existing table
                  Praveen_!

                  Max I will reach out to you in a bit. I'm evaluating SODA for a large global implementation and so far it seems quite promising... Just wondering, do you have any reservations/concerns or any case studies about deploying SODA for 10 million plus JSON resources per table with high availability SLA?

                  • 6. Re: SODA collection on existing table
                    MaxOrgiyan-Oracle

                    Hi Praveen, I don't have any concerns right off the bat. Under the covers, SODA is using Oracle SQL and regular tables, so if Oracle Database can handle your workload, so should SODA. But we'd have to discuss your use-case in detail to say for sure. Let me know when you're ready to discuss, and we can schedule a meeting. It's best to be on as recent of a DB release as you can - we are constantly improving SODA and the underlying SQL/JSON support, so there are significant improvements in every release.

                    • 7. Re: SODA collection on existing table
                      MaxOrgiyan-Oracle

                      Sent you a private message with the 12c workaround details. Let me know if you got it - had some trouble using private messaging on this site.

                      • 8. Re: SODA collection on existing table
                        Praveen_!

                        Thanks Max I too sent you a message explaining our project and how we plan to use SODA.

                        • 9. Re: SODA collection on existing table
                          MaxOrgiyan-Oracle

                          Hi Praveen, responded to your message, and left my direct contact info as well. Thanks!