5 Replies Latest reply on Mar 11, 2020 12:49 PM by EJ-Egyed

    POST call with Array of Records in JSON


      Does ORDS 18.3 support inbound POST JSON calls with Array of Records passed in?


      I need to make a POST call and pass in a number of records (for example details about a number of employees to be updated).. If this is meant to work, how would I pass it in the JSON, and how would I do things in ORDS to map JSON values in PL/SQL?

        • 2. Re: POST call with Array of Records in JSON

          thatJeffSmith-Oracle's response would be how to do it if using an Auto-REST endpoint.  If you are not using Auto-REST, I have answered this question in a previous thread here: How to receive array data in non-Auto Rest enabled handler

          • 3. Re: POST call with Array of Records in JSON

            Thank you both for your input.. Both examples are showing simple array of varchar2, not a more complex array of records.


            For example:

            "accounting" : [  
            { "firstName" : "John", 
            "lastName"  : "Doe",
            "age"   : 23 },
            { "firstName" : "Mary", 
            "lastName"  : "Smith",
            "age"   : 32 }



            How would you recieve the above through ORDS?

            • 4. Re: POST call with Array of Records in JSON

              You'll write some plsql to unwind the json


              If you're on 12c or higher there's a pl/sql api for that, if you're on 11g, you'll need to write your own from scratch or try the json package included with APEX

              • 5. Re: POST call with Array of Records in JSON

                I would use an endpoint like this.  Again, this is using the PL/SQL JSON APIs available in newer versions of Oracle:


                    ORDS.enable_schema (p_enabled               => TRUE,
                                        p_url_mapping_type      => 'BASE_PATH',
                                        p_url_mapping_pattern   => 'testapi',
                                        p_auto_rest_auth        => FALSE);
                    ORDS.define_module (p_module_name => 'v1', p_base_path => 'v1/');
                    ORDS.define_template (p_module_name => 'v1', p_pattern => 'emp');
                    ORDS.define_handler (p_module_name      => 'v1',
                                         p_pattern          => 'emp',
                                         p_method           => 'POST',
                                         p_source_type      => ORDS.source_type_plsql,
                                         p_source           => 'DECLARE
                    l_json         json_object_t;
                    l_accounting   json_array_t;
                    l_employee     json_object_t;
                    TYPE employees_t IS TABLE OF employees%ROWTYPE;
                    l_employees    employees_t := employees_t ();
                    l_json := json_object_t.parse (:body_text);
                    l_accounting := l_json.get_array (''accounting'');
                    FOR i IN 0 .. l_accounting.get_size - 1
                        l_employee := TREAT (l_accounting.get (i) AS JSON_OBJECT_T);
                        l_employees (i + 1).first_name := l_employee.get_string (''firstName'');
                        l_employees (i + 1).last_name := l_employee.get_string (''lastName'');
                        l_employees (i + 1).age := l_employee.get_string (''age'');
                    END LOOP;
                    FORALL i IN 1 .. l_employees.COUNT
                        INSERT INTO employees (first_name, last_name, age)
                             VALUES (l_employees (i).first_name, l_employees (i).last_name, l_employees (i).age);
                END;'                     ,
                                         p_items_per_page   => 0);