2 Replies Latest reply on Mar 10, 2017 4:25 PM by thatJeffSmith-Oracle

    ORDS response from POST service after insert

    3cb3985d-5578-4d8f-b1f6-87a6be5bf248

      I am currently facing a hard choice:

       

      • On one hand, if I use AutoREST, I can easily get POST response. However, I cannot customise the API behaviour as I wanted, such as case-insentsitive query.

                I enabled the AutoREST with Table object.   I am aware that I can use ?q={} to do the query.  

                However I cannot query the data with case insensitive. 

       

                For example, ?q={"name":"Chang"} but I would like to find all date with name chang, without considering letter case. 

       

                I know I can define a customized handler to do this.   But if currently I did ords.enable_object, is there anyway to customize this on top of it?

       

      • On the other hand, if I define every services I need, I am having trouble with getting proper POST response.

                I am trying to implement the POST service and return certain values after insert (i.e., output some values to POST’s response). I have tried several ways but no luck.

       

                I have tried declaring local variables but they are not returned. I have also tried select after insert. Not working as well. I have a procedure called ‘insert_customer’, where there are several IN parameters and a OUT parameter as customer_id. But the customer_id just cannot be outputed.

       

       

       

      Here is am example I have:

       

      CREATE OR REPLACE PROCEDURE insert_customer (

         p_corporate_key  IN  customers.corporate_key%TYPE,

         p_fullname       IN  customers.fullname%TYPE,

         p_email          IN  customers.email%TYPE,

         p_phone          IN  customers.phone%TYPE,

         p_job_title      IN  customers.job_title%TYPE,

         p_department     IN  customers.department%TYPE,

         p_organization   IN  customers.organization%TYPE,

         p_customer_id   OUT customers.customer_id%TYPE

      )

      AS

      BEGIN

         INSERT INTO customers (corporate_key, fullname, email, phone, job_title, department, organization)

         VALUES (p_corporate_key, p_fullname, p_email, p_phone, p_job_title, p_department, p_organization)

         RETURN customer_id INTO p_customer_id;

      EXCEPTION

         WHEN OTHERS

         THEN HTP.print(SQLERRM);

      END;

      /

       

      BEGIN

         ORDS.DEFINE_SERVICE(

            p_module_name => 'insert.customers' ,

            p_base_path   => '/customers/',

            p_method      => 'POST',

            p_source      => ‘DECLARE

                                          l_customer_id number;

                                      BEGIN

                                           insert_customer(p_corporate_key => :corporate_key,

                                                 p_fullname      => :fullname,

                                                 p_email         => :email,

                                                 p_phone         => :phone,

                                                 p_job_title     => :job_title,

                                                 p_department    => :department,

                                                 p_organization  => :organization

                                                 p_customer_id  => :l_customer_id);

                                      END;'

          );

       

         COMMIT;

      END;

       

       

      customer_id is a incremental sequence generated by trigger.

       

      I would like to insert a customer (with relevant information) and then return the customer_id.