11 Replies Latest reply on Apr 24, 2019 2:44 PM by thatJeffSmith-Oracle

    BASIC ORDS JSON POST SETUP ISSUES

    960754

      Code at end of post.

       

      [fakj@hiplord001 ~]$ curl -i -k -X POST --data-binary j.json https://hiplord001:80/ords/sysman/NEWCUSTOMER/NEW | egrep -i 'bad|err'

       

        % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                       Dload  Upload   Total   Spent    Left  Speed

      100 16249  100 16243  100     6   135k     51 --:--:-- --:--:-- --:--:--  136k

      HTTP/1.1 500 Server Error

      Error-Reason: error="resource.generator.evaluation"; error_description*=UTF-8''The%20request%20could%20not%20be%20processed%20because%20an%20error%20occurred%20whilst%20attempting%20to%20evaluate%20the%20SQL%20statement%20associated%20with%20this%20resource.%20Please%20check%20the%20SQL%20statement%20is%20correctly%20formed%20and%20executes%20without%20error.%20SQL%20Error%20Code%3a%20ORA-06550%3a%20line%206%2c%20column%201%3a%0aPLS-00103%3a%20Encountered%20the%20symbol%20%22%2f%22%20The%20symbol%20%22%2f%22%20was%20ignored.%0a%2c%20Error%20Message%3a%20%7b2%7d.

              <title>Internal Server Error</title>

                                              <span style="padding-left: 0.4em;">Internal Server Error</span>

                      The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: ORA-06550: line 6, column 1:

       

       

      How to debug this ??? PROC call works fine manually.

      Trying to demo this for my developers

       

       

       

       

      -- PAYLOAD (file j.son)

      {

         "custid":9999,

         "cust_fname":"JOHN",

         "cust_lname":"SMITH",

         "cust_phone":"727-111-1111",

         "cust_full_addr":"100-BEACH DRIVE ST PETERSBURG FL 33708"

      }

       

       

       

       

       

      -------CODE

       

      BEGIN

        ORDS.ENABLE_SCHEMA(

            p_enabled             => TRUE,

            p_schema              => 'SYSMAN',

            p_url_mapping_type    => 'BASE_PATH',

            p_url_mapping_pattern => 'sysman',

            p_auto_rest_auth      => FALSE);   

       

       

        ORDS.DEFINE_MODULE(

            p_module_name    => 'NEWCUSTOMER',

            p_base_path      => '/NEWCUSTOMER/',

            p_items_per_page =>  0,

            p_status         => 'PUBLISHED',

            p_comments       => NULL);     

        ORDS.DEFINE_TEMPLATE(

            p_module_name    => 'NEWCUSTOMER',

            p_pattern        => 'NEW',

            p_priority       => 9,

            p_etag_type      => 'NONE',

            p_etag_query     => NULL,

            p_comments       => NULL);

        ORDS.DEFINE_HANDLER(

            p_module_name    => 'NEWCUSTOMER',

            p_pattern        => 'NEW',

            p_method         => 'POST',

            p_source_type    => 'json/query',

            p_items_per_page =>  0,

            p_mimes_allowed  => 'application/json,*',

            p_comments       => NULL,

            p_source         =>

      'BEGIN

          sysman.create_customer(p_custid =>:custid,p_cust_fname =>:cust_fname,p_cust_lname =>:cust_lname,p_cust_phone =>:cust_phone,p_cust_full_addr =>:cust_full_addr);

          COMMIT;

          :status := 302;

      END;

      /'

            );

        ORDS.DEFINE_PARAMETER(

            p_module_name        => 'NEWCUSTOMER',

            p_pattern            => 'NEW',

            p_method             => 'POST',

            p_name               => 'CUSTID',

            p_bind_variable_name => 'custid',

            p_source_type        => 'URI',

            p_param_type         => 'STRING',

            p_access_method      => 'IN',

            p_comments           => NULL);     

        ORDS.DEFINE_PARAMETER(

            p_module_name        => 'NEWCUSTOMER',

            p_pattern            => 'NEW',

            p_method             => 'POST',

            p_name               => 'CUST_FNAME',

            p_bind_variable_name => 'cust_fname',

            p_source_type        => 'URI',

            p_param_type         => 'STRING',

            p_access_method      => 'IN',

            p_comments           => NULL);     

        ORDS.DEFINE_PARAMETER(

            p_module_name        => 'NEWCUSTOMER',

            p_pattern            => 'NEW',

            p_method             => 'POST',

            p_name               => 'CUST_FULL_ADDR',

            p_bind_variable_name => 'cust_full_addr',

            p_source_type        => 'URI',

            p_param_type         => 'STRING',

            p_access_method      => 'IN',

            p_comments           => NULL);     

        ORDS.DEFINE_PARAMETER(

            p_module_name        => 'NEWCUSTOMER',

            p_pattern            => 'NEW',

            p_method             => 'POST',

            p_name               => 'CUST_LNAME',

            p_bind_variable_name => 'cust_lname',

            p_source_type        => 'URI',

            p_param_type         => 'STRING',

            p_access_method      => 'IN',

            p_comments           => NULL);     

        ORDS.DEFINE_PARAMETER(

            p_module_name        => 'NEWCUSTOMER',

            p_pattern            => 'NEW',

            p_method             => 'POST',

            p_name               => 'STATUS',

            p_bind_variable_name => 'status',

            p_source_type        => 'HEADER',

            p_param_type         => 'STRING',

            p_access_method      => 'OUT',

            p_comments           => NULL);     

       

       

       

       

        COMMIT;

      END;

        • 1. Re: BASIC ORDS JSON POST SETUP ISSUES
          thatJeffSmith-Oracle

          check the ords log, should be more of an error stack there

          • 2. Re: BASIC ORDS JSON POST SETUP ISSUES
            960754

            Thanks Jeff.

             

            Was a '/' in the procedure to insert it didnt like - removed.

             

            Funny part is now it works -- but enters empty rows......... all null.

             

            Assume its a mapping issue with parameters....... just not quite sure what yet.

             

             

            --- update

             

            Still no luck - made all the mappings case sensitive - meaning the same ...... still just null values

            • 4. Re: BASIC ORDS JSON POST SETUP ISSUES
              960754

              Yup

              I removed that - but no difference.

               

              curl -i -k -X POST --data-binary @/export/home/fakj/j.json https://hiplord001:80/ords/sysman/NEWCUSTOMER/NEW

              HTTP/1.1 200 OK

              Date: Tue, 23 Apr 2019 18:41:05 GMT

              Content-Type: application/json

              Transfer-Encoding: chunked

               

               

              -- Generated by Oracle SQL Developer REST Data Services 18.2.0.183.1748

              -- Exported REST Definitions from ORDS Schema Version 18.4.0.r3541002

              -- Schema: SYSMAN   Date: Tue Apr 23 14:43:17 EDT 2019

              --

              BEGIN

                ORDS.ENABLE_SCHEMA(

                    p_enabled             => TRUE,

                    p_schema              => 'SYSMAN',

                    p_url_mapping_type    => 'BASE_PATH',

                    p_url_mapping_pattern => 'sysman',

                    p_auto_rest_auth      => FALSE);   

               

               

                ORDS.DEFINE_MODULE(

                    p_module_name    => 'NEWCUSTOMER',

                    p_base_path      => '/NEWCUSTOMER/',

                    p_items_per_page =>  0,

                    p_status         => 'PUBLISHED',

                    p_comments       => NULL);     

                ORDS.DEFINE_TEMPLATE(

                    p_module_name    => 'NEWCUSTOMER',

                    p_pattern        => 'NEW',

                    p_priority       => 9,

                    p_etag_type      => 'NONE',

                    p_etag_query     => NULL,

                    p_comments       => NULL);

                ORDS.DEFINE_HANDLER(

                    p_module_name    => 'NEWCUSTOMER',

                    p_pattern        => 'NEW',

                    p_method         => 'POST',

                    p_source_type    => 'plsql/block',

                    p_items_per_page =>  0,

                    p_mimes_allowed  => '',

                    p_comments       => NULL,

                    p_source         =>

              'BEGIN

                  sysman.create_customer(p_custid =>:custid,p_cust_fname =>:cust_fname,p_cust_lname =>:cust_lname,p_cust_phone =>:cust_phone,p_cust_full_addr =>:cust_full_addr);

                  COMMIT;

                  :status := 302;

              END;'

                    );

                ORDS.DEFINE_PARAMETER(

                    p_module_name        => 'NEWCUSTOMER',

                    p_pattern            => 'NEW',

                    p_method             => 'POST',

                    p_name               => 'STATUS',

                    p_bind_variable_name => 'status',

                    p_source_type        => 'RESPONSE',

                    p_param_type         => 'STRING',

                    p_access_method      => 'OUT',

                    p_comments           => NULL);     

               

               

                COMMIT;

              END;

              • 5. Re: BASIC ORDS JSON POST SETUP ISSUES
                960754

                If I manually call sysman.create_customer(p_custid =>:custid,p_cust_fname =>:cust_fname,p_cust_lname =>:cust_lname,p_cust_phone =>:cust_phone,p_cust_full_addr =>:cust_full_addr);

                 

                Works fine also -- data.

                 

                 

                • 6. Re: BASIC ORDS JSON POST SETUP ISSUES
                  960754

                  Also

                   

                  Called the REST service via another system and it worked ............ it must be something with the way the curl command/system call is being made ???

                  • 7. Re: BASIC ORDS JSON POST SETUP ISSUES
                    thatJeffSmith-Oracle

                    try the curl with --data followed by the json doc text in quotes vs the binary file?

                    • 8. Re: BASIC ORDS JSON POST SETUP ISSUES
                      960754

                      Thanks Jeff.

                       

                      Not 100% sure ....... but its working now.

                       

                      Trying to demo replication of couchbase (NOSQL) to backend Oracle. So this is now working.  1st time. DBA. Want to have it with your presentation to provide to larger DEV team.

                       

                      Appreciated

                      • 9. Re: BASIC ORDS JSON POST SETUP ISSUES
                        thatJeffSmith-Oracle

                        i'm always happy to do ords presentations for customers, just drop me a line if you're interested.

                        jeff.d.smith@oracle.com

                        • 10. Re: BASIC ORDS JSON POST SETUP ISSUES
                          960754

                          Hey Jeff ...... you did an informal one about 3 weeks ago for us.

                          Idea was to get some traction nad do some more discovery before pulling you back in on larger deal. That's  not happened but will look to get it going soon - I have to say was one of the better presentations we have had.

                           

                          Side (last) question - what determines the positional assignment of variables based on payload.

                           

                          Meaning if my payload is

                           

                          -- PAYLOAD (file j.son)

                          {

                             "custid":9999,

                             "cust_fname":"JOHN",

                             "cust_lname":"SMITH",

                             "cust_phone":"727-111-1111",

                             "cust_full_addr":"100-BEACH DRIVE ST PETERSBURG FL 33708"

                          }

                           

                          Do my variable names need to match in my pl/sql side unit. Or is it just positional.

                           

                          Thanks again.

                          Im going to do a load test of couchbase=>Oracle using REST ..... see how it goes and will upload high level details here.

                          • 11. Re: BASIC ORDS JSON POST SETUP ISSUES
                            thatJeffSmith-Oracle

                            i don't think the position of things in a json payload matter, you're referencing them by name, right?

                             

                            and yes, they need to match by name