5 Replies Latest reply on Apr 19, 2017 11:54 AM by thatJeffSmith-Oracle

    500 error when using pl/sql hadler

    3433634

      hi

       

      i have a procedure as follows, i have created a rest data service module using sqldeveloper with the following pl/sql:

      in a get handler

      begin

      get_emp_json;

      end;

      this runs fine as a piece of pl/sql and validates fine within sqldeveloper

       

      i then upload and try to test :

      http://localhost:9090/ords/hr/demo/employees/

      but receive a 500 error

       

      see source code here, any direction?

       

       

      create or replace PROCEDURE get_emp_json (p_empid IN hr.employees.employee_id %TYPE DEFAULT NULL) AS

        l_cursor SYS_REFCURSOR;

      BEGIN

       

        OPEN l_cursor FOR

          SELECT e.employee_id AS "empid",

                 e.first_name AS "employee_name

          FROM   hr.employees e

          WHERE  e.employee_id = DECODE(p_empid, NULL, e.employee_id, p_empid);

         

        APEX_JSON.INITIALIZE_CLOB_OUTPUT;

        APEX_JSON.open_object;

        APEX_JSON.write('emp', l_cursor);

        APEX_JSON.close_object;

        dbms_output.enable();

      dbms_output.put_line('output here');

        dBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_CLOB_OUTPUT);

      APEX_JSON.FREE_OUTPUT;

      END;

        • 1. Re: 500 error when using pl/sql hadler
          thatJeffSmith-Oracle

          Your source code has a problem

           

          e.first_name AS "employee_name  -- missing the "

           

          Here's what I did:

          -- replaced dbms_output with htp.p so you can actually see what's coming back

          -- fixed the typo on the cursor

           

          create or replace PROCEDURE get_emp_json (p_empid IN hr.employees.employee_id %TYPE DEFAULT NULL) AS

            l_cursor SYS_REFCURSOR;

          BEGIN

            OPEN l_cursor FOR

              SELECT e.employee_id AS "empid",

                     e.first_name AS "employee_name"

              FROM   hr.employees e

              WHERE  e.employee_id = DECODE(p_empid, NULL, e.employee_id, p_empid);

            APEX_JSON.INITIALIZE_CLOB_OUTPUT;

            APEX_JSON.open_object;

            APEX_JSON.write('emp', l_cursor);

            APEX_JSON.close_object;

            htp.prn(APEX_JSON.GET_CLOB_OUTPUT);

            --  dBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_CLOB_OUTPUT);

          APEX_JSON.FREE_OUTPUT;

          END;

           

          And my module:

          -- Generated by Oracle SQL Developer REST Data Services 4.2.0.17.089.1709

          -- Exported REST Definitions from ORDS Schema Version 3.0.9.348.07.16

          -- Schema: HR   Date: Tue Apr 18 10:23:29 EDT 2017

          --

          BEGIN

            ORDS.ENABLE_SCHEMA(

                p_enabled             => TRUE,

                p_schema              => 'HR',

                p_url_mapping_type    => 'BASE_PATH',

                p_url_mapping_pattern => 'peeps',

                p_auto_rest_auth      => FALSE);  

           

           

            ORDS.DEFINE_MODULE(

                p_module_name    => 'otn',

                p_base_path      => '/otn/',

                p_items_per_page =>  25,

                p_status         => 'PUBLISHED',

                p_comments       => NULL);    

            ORDS.DEFINE_TEMPLATE(

                p_module_name    => 'otn',

                p_pattern        => 'emps',

                p_priority       => 0,

                p_etag_type      => 'HASH',

                p_etag_query     => NULL,

                p_comments       => NULL);

            ORDS.DEFINE_HANDLER(

                p_module_name    => 'otn',

                p_pattern        => 'emps',

                p_method         => 'GET',

                p_source_type    => 'plsql/block',

                p_items_per_page =>  25,

                p_mimes_allowed  => '',

                p_comments       => NULL,

                p_source         =>

          'begin

          get_emp_json(

             p_empid => :p_emp_id

          );

          end;'

                );

           

            COMMIT;

          END;

           

          Then running it:

           

          apex-json.png

          • 2. Re: 500 error when using pl/sql hadler
            3433634

            thanks, ive corrected errors.  still receiving a 500 error when testing service.

             

            within rest development in sqldeveloper , the plsql attached to the get is running fine and outputs as expected , it uploads successfully but hit

            the 500 when testing.

            • 3. Re: 500 error when using pl/sql hadler
              thatJeffSmith-Oracle

              What does your ords log show?

               

              If i artificially break my SP, I get the 500 too and ORDS basically reports this

               

              Apr 18, 2017 4:15:33 PM 

              INFO: Error occurred during execution of: [CALL, begin

              get_emp_json(

                 p_empid => /*in:p_emp_id*/?

              );

              end;, [p_emp_id, in, class oracle.dbtools.common.stmt.UnknownParameterType]]with values: {1=102}

              Apr 18, 2017 4:15:33 PM 

              INFO: ORA-06550: line 2, column 2:

              PLS-00905: object HR.GET_EMP_JSON is invalid

              ORA-06550: line 2, column 2:

              PL/SQL: Statement ignored

               

               

              java.sql.SQLException: ORA-06550: line 2, column 2:

              PLS-00905: object HR.GET_EMP_JSON is invalid

              ORA-06550: line 2, column 2:

              PL/SQL: Statement ignored

               

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)

                at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)

                at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)

                at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)

                at oracle.jdbc.driver.T4C8Oall.doOALL(T...

              • 4. Re: 500 error when using pl/sql hadler
                3433634

                thanks,

                 

                yes its not valid. checked the logs

                 

                Im using the developer days vm.

                 

                Im using the ords schema but unable to use the apex_json package (no privs)

                 

                Any ideas on how to resolve?

                 

                Thanks

                • 5. Re: 500 error when using pl/sql hadler
                  thatJeffSmith-Oracle

                  Grant execute privs on the package to the HR user.