5 Replies Latest reply on Mar 28, 2018 10:22 AM by Pierre Yotti

    Validations in ORDS

    Caprihan-Oracle

      Hi..Was wondering how we can incorporate mandatory parameter check/validations into PL/SQL handlers?

      I tried something along the lines of:

      -- first the validations

          IF (NVL(:id,'NOT_DEFINED') = 'NOT_DEFINED')

              OR (NVL(MEMBER_PIC,'NOT_DEFINED') = 'NOT_DEFINED')

              OR (NVL(:content_type ,'NOT_DEFINED') = 'NOT_DEFINED') THEN

              raise VALIDATION_EXCEPTION;

         END IF;

       

      With my exception handler looking as:

      WHEN VALIDATION_EXCEPTION THEN
      :status := 400;
      v_code := SQLCODE;
      v_errm := SUBSTR(SQLERRM, 1 , 64);
      v_error_string := 'Code: ' || v_code || ', Message: ' || v_errm;

      :response_message := v_error_string;

      HTP.P(v_error_string);

       

      However despite this, I'm still seeing the standard error screen

       

       

      500 Internal Server Error

      Any idea how I can handle the exception better and show my own message instead of this?

       

      Regds,

      Gaurav

        • 1. Re: Validations in ORDS
          Pierre Yotti

          Hi,

           

          you should create a Output Parameter to display the Error Output as json. Have you create output parameter?

           

          Bildschirmfoto 2018-03-27 um 18.02.34.png

          and then

          WHEN VALIDATION_EXCEPTION THEN

          :status := 412;

          :error_message := v_error_string;

           

           

          412 Error code is better appropriate when a validation failed

           

           

          You can enable the   Debug Tracing in ORDS Server to display the Error Output and you will see what exactly cause that error

          https://docs.oracle.com/cd/E37099_01/doc.20/e25066/trouble.htm#AELIG7206

           

          Which Version of ORDS did you use?

           

           

          Regards

           

          Pierre

          • 2. Re: Validations in ORDS
            Caprihan-Oracle

            Hi Pierre...Thank you for responding.

            Yes, I have included similar parameters...and am attaching a screenshot for your reference.

             

            Screen Shot 2018-03-28 at 11.05.08 AM.png

             

            And as for the version of ORDS, I'm running on 3.0.11.180.12.34

             

            Regds,

            Gaurav

            • 3. Re: Validations in ORDS
              Pierre Yotti

              Hi,

              Why is reponse_message IN Parameter? Which Parameter do you want to use to output the Error?

               

              The Parameter for the Output the error Message muss OUT Parameter.

               

              if it  doest not works with 3.0.11.180.12.34, you can upgrade. That Version has 2 Many Bugs. I can suggest you to upgrade to 17.4

               

              or you can make it works like so: Remove all Output Parameters and try that

               

              https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29822

               

                     apex_json.open_object;

                      apex_json.write('v_error_string',v_error_string);

                      apex_json.close_object;

               

              to output the errors

               

              Regards

              • 4. Re: Validations in ORDS
                Caprihan-Oracle

                Thank you for your response Pierre...

                I tried out the following:

                1) Changed the response_message from IN to OUT...No effect

                2) Removed all the parameters and included the json approach as below:

                 

                EXCEPTION

                    WHEN VALIDATION_EXCEPTION THEN

                        :status := 412;

                        v_code := SQLCODE;

                        v_errm := SUBSTR(SQLERRM, 1 , 64);

                        v_error_string := 'Code: ' || v_code || ', Message: ' || v_errm;

                 

                        apex_json.open_object;

                        apex_json.write('v_error_string',v_error_string);

                        apex_json.close_object;

                 

                    WHEN OTHERS THEN

                        :status := 400;

                        v_code := SQLCODE;

                        v_errm := SUBSTR(SQLERRM, 1 , 64);

                        v_error_string := 'Code: ' || v_code || ', Message: ' || v_errm;

                 

                        apex_json.open_object;

                        apex_json.write('v_error_string',v_error_string);

                        apex_json.close_object;

                 

                 

                Sadly, that too did'nt change the way I was receiving the response in Postman...same 500 Internal Error message as before.

                I guess I will have to try to upgrade to the newer version of ORDS.

                 

                Wondering if my validation check is a problem? This is what I'm doing:

                 

                    IF (NVL(:id,'NOT_DEFINED') = 'NOT_DEFINED')

                        OR (NVL(MEMBER_PIC,'NOT_DEFINED') = 'NOT_DEFINED')

                        OR (NVL(:content_type ,'NOT_DEFINED') = 'NOT_DEFINED') THEN

                        raise VALIDATION_EXCEPTION;

                   END IF;

                 

                 

                id is passed as an parameter in the URL itself...MEMBER_PIC is defined as a BLOB and defaulted to :body in the DECLARE block...content_type is set as a parameter.

                 

                Regds,

                Gaurav

                • 5. Re: Validations in ORDS
                  Pierre Yotti

                  Hi,

                   

                  to testing try something like that. And remove all Parameters. If if works you can add anothers Pamaters and show where is your errors.

                   

                  Begin

                      v_error_string := 'Code: ' || v_code || ', Message: ' || v_errm;

                          apex_json.open_object;

                          apex_json.write('v_error_string',v_error_string);

                          apex_json.close_object;

                   

                  END;

                   

                   

                  The source Type shoud be PL/SQL.

                   

                   

                  PS:

                  You can enable the   Debug Tracing in ORDS Server to display the Error Output in POSTMAN and you will see what exactly cause that error

                  https://docs.oracle.com/cd/E37099_01/doc.20/e25066/trouble.htm#AELIG7206