8 Replies Latest reply on Oct 4, 2019 11:10 AM by Jose Aróstegui

    PLSQL Handler returns empty output

    Jose Aróstegui

      Hi experts,


      I'm using ORDS 3.0.4.60.12.48 and I'm testing a PLSQL procedure that builds a json with a custom format. I'm adding some trace to the procedure and I've checked that it's called and the parameters are OK, but I get an empty page in browser:

       

       

      These are the scripts used:

       

      BEGIN
        ords.enable_schema(p_enabled             => TRUE,
                           p_schema              => 'AHORDS',
                           p_url_mapping_type    => 'BASE_PATH',
                           p_url_mapping_pattern => 'ahapi',
                           p_auto_rest_auth      => false);
        COMMIT;
      END;
      /
      
      BEGIN          
        ords.define_module(p_module_name    => 'wms.v1',
                           p_base_path      => 'wms/v1',
                           p_items_per_page => 1000,
                           p_status         => 'PUBLISHED',
                           p_comments       => 'WMS Module');             
        COMMIT;
      END;
      /
          
      BEGIN
        ords.define_template(p_module_name => 'wms.v1',
                             p_pattern     => 'warehouseClosingDates',
                             p_comments    => 'Warehouses Closing Dates');
        COMMIT;
      END;
      /
      
      
      -- Nivel 3: HANDLER
      BEGIN
        ords.define_handler(p_module_name    => 'wms.v1',
                            p_pattern        => 'warehouseClosingDates',
                            p_method         => 'GET',
                            p_mimes_allowed => 'application/json',
                            p_source_type    => ords.source_type_plsql,                      
                            p_source         => '
      begin
        closing_dates(p_year => :p_year,
                    p_warehouse_code => :p_warehouse_code);
      end;',
                            p_items_per_page => 1000,
                            p_comments       => 'Returns warehouses closing dates');
        COMMIT;
      END;
      /
      

       

      BEGIN
        ords.define_parameter(p_module_name        => 'wms.v1',
                              p_pattern            => 'warehouseClosingDates',
                              p_method             => 'GET',
                              p_name               => 'warehouse_code',
                              p_bind_variable_name => 'p_warehouse_code',
                              p_source_type        => 'URI',
                              p_param_type         => 'STRING',
                              p_access_method      => 'IN',
                              p_comments           => 'Filters by warehouse code');  
      
      
        ords.define_parameter(p_module_name        => 'wms.v1',
                              p_pattern            => 'warehouseClosingDates',
                              p_method             => 'GET',
                              p_name               => 'year',
                              p_bind_variable_name => 'p_year',
                              p_source_type        => 'URI',
                              p_param_type         => 'STRING',
                              p_access_method      => 'IN',
                              p_comments           => 'Filters by natural year');  
        COMMIT;
      END;
      /
      
      CREATE OR REPLACE PROCEDURE closing_dates(p_year           IN VARCHAR2,
                                                p_warehouse_code IN VARCHAR2) IS
        l_cursor   SYS_REFCURSOR;
        l_festivos json_array_t;
      
      
        CURSOR c_festivos(pc_year           IN VARCHAR,
                          pc_warehouse_code IN VARCHAR2) IS
          SELECT lpad(b.war_no, 2, '0') almacen
                ,to_char(a.activity_date, 'YYYYMMDD') festivo
            FROM (SELECT DISTINCT war_id
                                 ,start_date activity_date
                                 ,event_name
                                 ,css_class
                                 ,is_opened
                                 ,rank() over(PARTITION BY start_date ORDER BY decode(event_description, 'Opening', '1', 'Closed', '2', 'Sunday', '3', 'Labor', '4')) rank
                                 ,decode(event_description, 'Opening', '1', 'Closed', '2', 'Sunday', '3', 'Labor', '4')
                    FROM wep_open_excep_cal_nosec_v) a
                ,mst_warehouses b
           WHERE a.war_id = b.war_id
             AND rank = 1
             AND to_char(a.activity_date, 'YYYY') = pc_year
             AND lpad(b.war_no, 2, '0') = nvl(pc_warehouse_code, lpad(b.war_no, 2, '0'))
             AND is_opened = 'N'
           ORDER BY 2;
      
      
      BEGIN
        INSERT INTO traza
        VALUES
          ('p_warehouse_code=' || p_warehouse_code);
        INSERT INTO traza
        
        VALUES
          ('p_year=' || p_year);
        COMMIT;
        apex_json.initialize_clob_output;
      
      
        apex_json.open_object; -- {
        apex_json.open_array('almacenes'); -- "almacenes": [
      
      
        FOR reg_warehouse IN (SELECT lpad(w.war_no, 2, '0') almacen
                                    ,w.war_id
                                FROM wms.mst_warehouses w
                               WHERE w.war_no = to_number(p_warehouse_code)) LOOP
        
          apex_json.open_object; -- {
          apex_json.write('almacen', reg_warehouse.almacen);
        
          apex_json.open_array('festivos'); -- "festivos": [  
        
          FOR reg_festivos IN c_festivos(p_year, p_warehouse_code) LOOP
            apex_json.write(reg_festivos.festivo); --190101
          
          END LOOP;
        
          apex_json.close_array; -- ]
          apex_json.close_object; -- }
        END LOOP;
      
      
        apex_json.close_array; -- ]
        apex_json.close_object; -- }
      
      
        dbms_output.put_line(apex_json.get_clob_output);
        INSERT INTO traza
        VALUES
          (apex_json.get_clob_output);
        COMMIT;
      
      
      END closing_dates;
      

       

      What am I missing?

      Thanks!

      Jose.

        • 1. Re: PLSQL Handler returns empty output
          thatJeffSmith-Oracle

          w/o looking at your code, version 3.0.4 is very old, and no longer supported...you should at least be on version 3.0.10 if you want to stay on the version 3 code base...which you shouldn't do to begin with

           

          also - how are you having the stored procedure return the output to ORDS? it's not this is it?

          1.   dbms_output.put_line(apex_json.get_clob_output); 
          • 2. Re: PLSQL Handler returns empty output
            Jose Aróstegui

            Thanks a lot, Jeff.

             

            I've scalated the upgrade issue.

             

            Meanwhile, how do I return the output to Ords?

             

            Also, how can I check the higher supported version compatible with Apex 5.0?

             

            Best regards from Madrid!

            • 3. Re: PLSQL Handler returns empty output
              Jose Aróstegui

              Hi Jeff,


              Found something interesting in a blog over there :-) (https://www.thatjeffsmith.com/archive/2018/08/executing-pl-sql-package-procedure-via-rest/ )

               

              So I've created an OUT parameter

              ords.define_parameter(
                      p_module_name          => 'wms.v1',
                      p_pattern              => 'warehouseClosingDates',
                      p_method              => 'GET',
                      p_name                => 'result',
                      p_bind_variable_name  => 'x_result',
                      p_source_type          => 'RESPONSE',
                      p_param_type          => 'STRING',
                      p_access_method        => 'OUT',
                      p_comments            => NULL
                  );
              

               

              An assigned the json output it in the procedure:

               

              closing_dates(p_year => :p_year,
                              p_warehouse_code => :p_warehouse_code,
                              x_result => :x_result);
              

               

              But now, I get the correct json but with a lot of \n:

               

               

              Thanks again,

              Jose.

              • 4. Re: PLSQL Handler returns empty output
                Jose Aróstegui

                Hi Jeff

                 

                I've read another of your post (https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/ ), and I've change the approach.

                 

                The handler now is like this:

                BEGIN
                  ords.define_handler(p_module_name    => 'wms.v1',
                                      p_pattern        => 'warehouseClosingDates',
                                      p_method        => 'GET',
                                      p_mimes_allowed => 'application/json',
                                      p_source_type    => ords.source_type_media,  --> SOURCE TYPE IS MEDIA  
                                      p_source        => q'[
                SELECT mime_type, json_result
                    FROM TABLE(closing_dates2(:p_year, :p_warehouse_code))         ---> Function now returns a pipelined object
                  ]',                     
                                      p_items_per_page => 0,
                                      p_comments      => 'Returns warehouses closing dates');
                  COMMIT;
                END;
                /
                

                 

                CREATE OR REPLACE FUNCTION closing_dates2(p_year           IN VARCHAR2,
                                                          p_warehouse_code IN VARCHAR2) RETURN typ_json_result_tbl
                  PIPELINED IS
                ....
                BEGIN
                ...
                  PIPE ROW(typ_json_result('application/json', apex_json.get_clob_output));
                END closing_dates2;
                

                 

                CREATE OR REPLACE TYPE typ_json_result AS OBJECT (
                mime_type   VARCHAR2(300),
                json_result CLOB)
                
                CREATE OR REPLACE TYPE typ_json_result_tbl AS TABLE OF typ_json_result
                

                 

                And now it's working fine:

                 

                Thanks a lot for your time and interest!

                 

                Regards,

                Jose.

                • 6. Re: PLSQL Handler returns empty output
                  Jose Aróstegui

                  Hi Jeff,


                  I've been asked to control http status and return 400 in case parameters aren't ok and stuff like that.

                   

                  How can I control this in my current implementation?

                   

                  Thanks,

                  Jose.

                  • 7. Re: PLSQL Handler returns empty output
                    Jose Aróstegui

                    Hi,

                     

                    Finally I've found were my initial error was. In procedure closing_dates I was calling to:

                     

                    apex_json.initialize_clob_output; 

                     

                    And this, was avoiding package apex_json to write using UTL_HTP and that's the reason the output was empty.

                     

                    Removing it, now the json generated is displayed correctly and I can control the http status using a PLSQL handler instead of a Media handler and defining the out parameter:

                     

                      ords.define_parameter(p_module_name        => 'wms.v1',
                                            p_pattern            => 'warehouseClosingDates',
                                            p_method             => 'GET',
                                            p_name               => 'X-APEX-STATUS-CODE',
                                            p_bind_variable_name => 'status',
                                            p_source_type        => 'HEADER',
                                            p_param_type         => 'INT',
                                            p_access_method      => 'OUT',
                                            p_comments           => NULL);
                    

                     

                    Hope it helps to someone as lost as I was....

                     

                    Regards,

                    Jose.

                    • 8. Re: PLSQL Handler returns empty output
                      Jose Aróstegui

                      ... and last comment.... this is wrong in 

                       

                      Oracle® REST Data Services

                      Installation, Configuration, and Development

                      Guide

                      Release 3.0.11

                      E87809-03

                      July 2017

                       

                      You can use source_type_plsql with GET operations without problems:

                       

                      Regards,

                      Jose.