2 Replies Latest reply on Sep 28, 2015 1:31 PM by Careau-Oracle

    Create a rest call with ords package with pl-sql as source type

    Careau-Oracle

      Hi

      I want to call some pl-sql block within a rest call but I can't get any example on the web for that.

       

      Does anybody have one for me?

      The usecase I try to do is calling a rest call and giving a json object and calling a pl-sql procedure in a pl/sql block.

       

      The code I tried is something like that

       

      begin

         ords_services.delete_module(p_name => 'cr_serv_mod61');

         ords.create_service(

          p_module_name => 'cr_serv_mod61',

          p_base_path =>   '/mod/',

          p_pattern =>     'instest',

          p_method =>      'POST',

          p_source_type => ords.source_type_plsql,

          p_source =>      'begin insert into emp3 (empno, ename, job,   sal)

                   values  (:empno, :ename, :job, :sal); myProc(:empno)  end;');

      end;

       

      But all I got was an 405 error "Method not allowed"

        • 1. Re: Create a rest call with ords package with pl-sql as source type
          Denis Segard-Oracle

          Hello,

           

          declare

            l_moduleId number;

            l_templateId number;

            l_handlerId  number;

            l_parameterId number;

          begin

              ords_services.delete_module(p_name => 'emp_module');

              l_moduleId := ORDS_SERVICES.create_module(p_name => 'emp_module',

                                                         p_uri_prefix => '/emp_module',

                                                         p_items_per_page => 10,

                                                         p_status => 'PUBLISHED',

                                                         p_comments => 'Comments on Module emp_module');

              l_templateId := ORDS_SERVICES.add_template(p_module_id => l_moduleId,

                                                         p_uri_template => '/postemp/',

                                                         p_priority => 0,

                                                         p_etag_type => 'HASH',

                                                         p_etag_query => null,

                                                         p_comments => 'POST emp emp/');

              l_handlerId := ORDS_SERVICES.add_handler(p_template_id => l_templateId,

                                                        p_source_type =>  'plsql/block',

                                                        p_method => 'POST',

                                                        p_items_per_page => null,

                                                        p_mimes_allowed =>null,

                                                        p_comments => 'Comments on handler ',

                                                        p_source => 'declare

            lCount number;

          begin

               insert into trace (data) values  (:data);

          end;');

          end;

           

          Generate a POST HTTP request :

           

          In the POST HTTP request  , the HTTP Header must be set to

           

          Content-Type application/json

           

          in the body :

           

          {"data" : "Hello World"}

           

          Kind regards

          • 2. Re: Create a rest call with ords package with pl-sql as source type
            Careau-Oracle

            Hi Denis

            Thnaks for your response.

            You use the in your response was one I wrote few days ago and it didn't work.

            With the response I got on that post, I think the ords_service package is an internal or system one and we are not supposed to use it. Maybe it's the reason there's no doc on it on the web...

             

            Meanwhile, I was able to make my snipet code works by setting the method to GET.

            But it's not a restful call since get shouldn't commit stuff with GET...