4 Replies Latest reply on Feb 20, 2020 2:34 PM by EJ-Egyed

    ORDS handling User Defined Data Types (UDT)

    onbORDS

      Hello Everyone

      I have a situation where we need to use User defined data Type and we don't use Auto REST feature. We are not sure how the return type of UDT in fuction or OUT from Procedure can be handle by ORDS. Return from cursor works fine with RESULTSET parameter. I am assuming AUTO pl/sql have some features that I am not aware of.

      Here is an example.

      We have following Types created.

      CREATE OR REPLACE TYPE emp_det_obj AS OBJECT

      (

         empno NUMBER (4),

         ename VARCHAR2 (10 BYTE),

         job VARCHAR2 (9 BYTE),

         sal NUMBER (7, 2)

      );

      /

       

      create or replace type emp_det_tab is table of emp_det_obj;
      /

      create or replace function emp_details return emp_det_tab is
      l_emp_det_tab emp_det_tab;

      And Procedure as

       

       

      CREATE OR REPLACE PROCEDURE proc_emp_details (p_emp_det OUT emp_det_tab)

      IS

      --l_emp_det_tab   emp_det_tab;

      BEGIN

         SELECT emp_det_obj (empno   => e.empno,

                             ename   => e.ename,

                             job     => e.job,

                             sal     => e.sal)

           BULK COLLECT INTO p_emp_det

           FROM emp e;

      --return l_emp_det_tab; — when used as function

      end proc_emp_details;

      /

       

      BEGIN

         ords_admin.enable_schema (p_enabled               => TRUE,

                                   p_schema                => 'my_schema',

                                   p_url_mapping_type      => 'BASE_PATH',

                                   p_url_mapping_pattern   => 'abc3',

                                   p_auto_rest_auth        => FALSE);

         ORDS_admin.define_module (p_schema           => 'my_schema',

                                   p_module_name      => 'testprcudt',

                                   p_base_path        => 'testprcudt/',

                                   p_items_per_page   => 0);

       

       

         ORDS_admin.define_template (p_schema        => 'my_schema',

                                     p_module_name   => 'testprcudt',

                                     p_pattern       => 'proc_emp_details');

       

       

         ORDS_admin.define_handler (p_schema           => 'my_schema',

                                    p_module_name      => 'testprcudt',

                                    p_pattern          => 'proc_emp_details',

                                    p_method           => 'POST',

                                    p_source_type      => ORDS.source_type_plsql,

                                    p_source           => '

      BEGIN

      proc_emp_details(:l_emp_det_tab);

      END;'                          ,

                                    p_items_per_page   => 0);

       

       

         ORDS_admin.define_parameter (p_schema               => 'my_schema',

                                      p_module_name          => 'testprcudt',

                                      p_pattern              => 'proc_emp_details',

                                      p_method               => 'POST',

                                      p_name                 => 'l_emp_det_tab',

                                      p_bind_variable_name   => 'l_emp_det_tab',

                                      p_source_type          => 'RESPONSE',

                                      p_param_type           => 'RESULTSET', --- What param type should be used here? Document shows only STRING, INT, DOUBLE, BOOLEAN, LONG, TIMESTAMP (not even RESULTSET, perhaps comes with new document)

                                      p_access_method        => 'OUT');

       

       

         COMMIT;

      END;

      /

       

      This does not work. How can we have function/procedure that output UDT to be shown using ORDS? Also if there are nested UDT will it be different way to handle?

      Thanks,
      Rajan

        • 1. Re: ORDS handling User Defined Data Types (UDT)
          thatJeffSmith-Oracle

          We built auto feature to save you from having to write a lot of custom code .. is there a reason you can use the auto feature here?

          • 2. Re: ORDS handling User Defined Data Types (UDT)
            onbORDS

            Hi Jeff,

             

            I like the auto feature but for our case we have lots of existing custom code.

             

            Also, We are not allowed to use ORDS user/schema and we use some other schema instead(right now ords_admin and we are already facing some road block with permission).

             

            so beside the auto feature, how can we work on UDT ? Is there solution for it.

             

            Thanks,

            Rajan

            • 3. Re: ORDS handling User Defined Data Types (UDT)
              onbORDS

              Hi Jeff,

               

              Just curious if accessing oracle packages/procedure object is limited to Auto Rest?

              • 4. Re: ORDS handling User Defined Data Types (UDT)
                EJ-Egyed

                Why is it that you need to use the user defined type?  The simplest way would to be to just create GET endpoints using a select statement in the source of the handler.  It is possible to use a function/procedure returning a custom type, but the type will need to have the individual values printed out.

                 

                I used the code below to recreate your scenario creating 3 endpoints.

                 

                1. testprcudt/proc_emp_details
                  1. This returns all of the information in the EMP table. This is probably the simplest solution to return the information you are looking for. If this is not formatted in a structure that you like, you can try changing the p_source_type parameter to ORDS.source_type_query, ORDS.source_type_feed, or ORDS.source_type_collection_feed to see how the data would be returned with each of those formats.
                2. testprcudt/proc_emp_details/:empno
                  1. This returns information about a single employee by appending the empno at the end of the URL
                3. testprcudt/proc_emp_details/function
                  1. This returns all of the employees in a JSON Array. This uses a function returning a custom type which is a table of the emp_det_obj.  This seems to be what you were asking for, but I would not recommend using this method as it is more code to maintain.  Option 1 is able to return the same information (although structured slightly differently) but without the need of any custom types/procedures/functions.

                 

                CREATE TABLE emp
                (
                    empno   NUMBER (4),
                    ename   VARCHAR2 (10),
                    job     VARCHAR2 (9),
                    sal     NUMBER (7, 2)
                );
                
                
                INSERT INTO emp (empno,
                                 ename,
                                 job,
                                 sal)
                     VALUES (1234,
                             'John Smith',
                             'Reception',
                             50000);
                
                
                INSERT INTO emp (empno,
                                 ename,
                                 job,
                                 sal)
                     VALUES (5678,
                             'Jane Allen',
                             'Developer',
                             75000);
                
                
                CREATE OR REPLACE TYPE emp_det_obj AS OBJECT
                (
                    empno NUMBER (4),
                    ename VARCHAR2 (10 BYTE),
                    job VARCHAR2 (9 BYTE),
                    sal NUMBER (7, 2)
                );
                /
                
                
                CREATE OR REPLACE TYPE emp_det_tab IS TABLE OF emp_det_obj;
                /
                
                
                CREATE OR REPLACE FUNCTION get_emp_details
                    RETURN emp_det_tab
                IS
                    l_employees   emp_det_tab;
                BEGIN
                    SELECT emp_det_obj (empno   => e.empno,
                                        ename   => e.ename,
                                        job     => e.job,
                                        sal     => e.sal)
                      BULK COLLECT INTO l_employees
                      FROM emp e;
                
                
                    RETURN l_employees;
                END get_emp_details;
                /
                
                
                BEGIN
                    ORDS.enable_schema (p_enabled               => TRUE,
                                        p_url_mapping_type      => 'BASE_PATH',
                                        p_url_mapping_pattern   => 'abc3',
                                        p_auto_rest_auth        => FALSE);
                
                
                    ORDS.define_module (p_module_name => 'testprcudt', p_base_path => 'testprcudt/', p_items_per_page => 0);
                
                
                    --Return entire table
                    ORDS.define_template (p_module_name => 'testprcudt', p_pattern => 'proc_emp_details');
                
                
                    --Return entire table handler
                    ORDS.define_handler (p_module_name   => 'testprcudt',
                                         p_pattern       => 'proc_emp_details',
                                         p_method        => 'GET',
                                         p_source_type   => ORDS.source_type_collection_feed,
                                         p_source        => 'SELECT empno, ename, job, sal FROM emp');
                
                
                    --Return one employee
                    ORDS.define_template (p_module_name => 'testprcudt', p_pattern => 'proc_emp_details/:empno');
                
                
                    --Return one employee handler
                    ORDS.define_handler (p_module_name   => 'testprcudt',
                                         p_pattern       => 'proc_emp_details/:empno',
                                         p_method        => 'GET',
                                         p_source_type   => ORDS.source_type_query_one_row,
                                         p_source        => 'SELECT empno, ename, job, sal FROM emp where empno = :empno');
                
                
                    --Return all employees using a function
                    ORDS.define_template (p_module_name => 'testprcudt', p_pattern => 'proc_emp_details/function');
                
                
                    --Return all employees using a function handler
                    ORDS.define_handler (p_module_name   => 'testprcudt',
                                         p_pattern       => 'proc_emp_details/function',
                                         p_method        => 'GET',
                                         p_source_type   => ORDS.source_type_plsql,
                                         p_source        => 'DECLARE
                    l_employees    emp_det_tab;
                    l_all_emps     json_array_t := json_array_t ();
                    l_single_emp   json_object_t;
                BEGIN
                    l_employees := get_emp_details;
                
                
                    FOR i IN l_employees.FIRST .. l_employees.LAST
                    LOOP
                        l_single_emp := json_object_t ();
                
                
                        l_single_emp.put (key => ''empno'', val => l_employees (i).empno);
                        l_single_emp.put (key => ''ename'', val => l_employees (i).ename);
                        l_single_emp.put (key => ''job'', val => l_employees (i).job);
                        l_single_emp.put (key => ''sal'', val => l_employees (i).sal);
                
                
                        l_all_emps.append (l_single_emp);
                    END LOOP;
                
                
                    owa_util.mime_header(''application/json'');
                    htp.p (l_all_emps.stringify);
                END;'                     );
                
                
                
                
                    COMMIT;
                END;
                /
                

                 

                 

                This is the result of hitting the endpoints via Postman

                 

                Endpoint 1: return all employees using select statement

                 

                Endpoint 2: Return a single employee using a select statement

                 

                Endpoint 3: Return all employees using a procedure/function that returns a table of a custom record type