11 Replies Latest reply on Aug 23, 2018 7:29 PM by dmcmahon-Oracle

    Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).

    Rick J

      This is a general test case for a problem I am having.

       

      I am using Oracle 12.2 and ORDS 18.2 in standalone mode.

       

      The following query succeeds when executed via sqlplus...

       

      SQL>     select x, y

        2      from JSON_TABLE(

        3      q'[{ "coordinates": [

        4  [

        5  [1.1, 2],

        6  [3, 4]

        7  ]

        8  ]

        9  }]'

      10      , '$.coordinates[0][*]' ERROR ON ERROR

      11      columns (x number path '$[0]', y number path '$[1]')) a;

       

       

               X          Y

      ---------- ----------

             1.1          2

               3          4

       

      However using the same query in GET handler results in a 500 error and the following error message ...

       

              at oracle.dbtools.rest.resource.generator.json.JSONResourceGeneratorBase.service(JSONResourceGeneratorBase.java:66)

              ... 91 more

      Caused by: Error : 1722, Position : 0, Sql = select x, y

          from JSON_TABLE(

          q'[{ "coordinates": [

                                      [

                                              [1.1, 2],

                                              [3, 4]

                                      ]

                              ]

                      }]'

          , '$.coordinates[0][*]' ERROR ON ERROR

          columns (x number path '$[0]', y number path '$[1]')) a, OriginalSql = select x, y

          from JSON_TABLE(

          q'[{ "coordinates": [

                                      [

                                              [1.1, 2],

                                              [3, 4]

                                      ]

                              ]

                      }]'

          , '$.coordinates[0][*]' ERROR ON ERROR

          columns (x number path '$[0]', y number path '$[1]')) a, Error Msg = ORA-01722: invalid number

       

       

              at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

              ... 112 more

       

      Why would the query not succeed when executed by via ORDS?

        • 1. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
          Pierre Yotti

          try that like so

          select

              x,

              y

            from

                  json_table ( '[{ "coordinates": [[[16.2, 2],[3, 4]]]}]','$.coordinates[0][*]' ERROR ON ERROR

                      columns (

                          x number path '$[0]',

                          y number path '$[1]'

                      )

                  )

              a;

           

          Bildschirmfoto 2018-08-22 um 09.46.29.png

           

          Bildschirmfoto 2018-08-22 um 09.45.18.png

          • 2. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
            odie_63

            Rick J wrote:

             

            Why would the query not succeed when executed by via ORDS?

            Probably an issue with NLS settings (NLS_NUMERIC_CHARACTERS) in the ORDS context.

             

            For example :

            SQL> select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';
            
            VALUE
            --------------------------------------------------------------------------------
            .,
            
            SQL> select x, y
              2  from json_table(
              3          '{"coordinates": [[[1.1, 2],[3, 4]]]}'
              4        , '$.coordinates[0][*]' error on error
              5          columns x number path '$[0]'
              6                , y number path '$[1]'
              7      ) a
              8  ;
            
                    X          Y
            ---------- ----------
                  1.1          2
                    3          4
            

             

            But :

            SQL> alter session set nls_numeric_characters=",.";
            
            Session altered.
            
            SQL> select x, y
              2  from json_table(
              3          '{"coordinates": [[[1.1, 2],[3, 4]]]}'
              4        , '$.coordinates[0][*]' error on error
              5          columns x number path '$[0]'
              6                , y number path '$[1]'
              7      ) a
              8  ;
            select x, y
            *
            ERROR at line 1:
            ORA-01722: invalid number
            
            • 3. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
              Pierre Yotti

              That is correct. But running the SQL in ORDS Handler, that does not matter

               

              if you  set

              alter session set nls_numeric_characters=",.";

              or

              alter session set nls_numeric_characters=".,";

              • 4. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                Rick J

                Thanks for your reply.  I also get a problem if the json is contained on one line.    I should have mentioned that the error does not occur if the coordinates are integers.   

                • 5. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                  Pierre Yotti

                  "I also get a problem if the json is contained on one line."

                   

                  What do you means?

                   

                   

                  It works for me when the json is of one line or in multiple line

                   

                  Bildschirmfoto 2018-08-22 um 13.55.03.png

                   

                  Bildschirmfoto 2018-08-22 um 13.55.11.png

                   

                  Bildschirmfoto 2018-08-22 um 13.55.17.png

                  • 6. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                    odie_63

                    Alli Pierre Yotti wrote:

                     

                    That is correct. But running the SQL in ORDS Handler, that does not matter

                     

                    if you set

                    alter session set nls_numeric_characters=",.";

                    or

                    alter session set nls_numeric_characters=".,";

                    Are you sure?

                    How do you know (or control) which NLS settings apply when ORDS run the query?

                    • 7. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                      Rick J

                      I am unsure how your example is different from mine.   In any case your example also fails for me.    This does seem to be a NLS_NUMERIC_CHARACTERS issue.   

                       

                      I am looking for how that environment value can be changed for ORDS.

                      • 8. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                        Pierre Yotti

                        Hi,

                         

                        i think you are correct. Sorry. That is how i test it

                         

                        1- That works fine. I created i GET handle using PL/SQL and set the  nls_numeric_characters dynamically when run the Service

                         

                        begin

                        EXECUTE IMMEDIATE 'ALTER SESSION SET nls_numeric_characters=''.,''';

                        open :items for select

                            x,

                            y

                          from

                                json_table ( '[

                           {

                              "coordinates":[

                                 [

                                    [

                                       16.2,

                                       2.9

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ]

                                 ]

                              ]

                           }

                        ]','$.coordinates[0][*]' error on error

                                    columns (

                                        x number path '$[0]',

                                        y number path '$[1]'

                                    )

                                )

                            ;

                            end;

                        Bildschirmfoto 2018-08-22 um 14.45.39.png

                         

                        Bildschirmfoto 2018-08-22 um 14.45.44.png

                         

                         

                        Result

                        Bildschirmfoto 2018-08-22 um 14.42.37.png

                         

                         

                        2- That doest not works fine

                         

                        begin

                        EXECUTE IMMEDIATE 'ALTER SESSION SET nls_numeric_characters='',.''';

                        open :items for select

                            x,

                            y

                          from

                                json_table ( '[

                           {

                              "coordinates":[

                                 [

                                    [

                                       16.2,

                                       2.9

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ],

                                    [

                                       3.988,

                                       4

                                    ]

                                 ]

                              ]

                           }

                        ]','$.coordinates[0][*]' error on error

                                    columns (

                                        x number path '$[0]',

                                        y number path '$[1]'

                                    )

                                )

                            ;

                            end;

                         

                        Result

                        Bildschirmfoto 2018-08-22 um 14.43.23.png

                         

                         

                         

                        The issue is about nls_numeric_characters. But you need to set it directly in ORDS Level or change it at the Database Level

                        • 9. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                          cormaco

                          I am looking for how that environment value can be changed for ORDS.

                          It might be easier to return your values as strings and then convert them with to_number:

                          alter session set nls_numeric_characters=",.";  
                          select to_number(x,'9999.9999') x, to_number(y,'9999.9999') y  
                          from json_table(  
                                   '{"coordinates": [[[1.1, 2],[3, 4]]]}'  
                                 , '$.coordinates[0][*]' error on error  
                                   columns x varchar2(10) path '$[0]'  
                                         , y varchar2(10) path '$[1]'  
                               );
                          
                          
                          
                                   X          Y
                          ---------- ----------
                          1,1                 2
                                   3          4
                          
                          

                           

                          The left alignment of "1,1" seems to be a client issue, the type is definitely number

                          • 10. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                            odie_63

                            Alli Pierre Yotti wrote:

                             

                            But you need to set it directly in ORDS Level or change it at the Database Level

                            Fixing it at ORDS level would be better indeed.

                            Doing it at the database level doesn't guarantee a session won't override the settings.

                             

                            Anyway, I think this behaviour is a bug.

                            In standard JSON, the decimal separator is a dot, so the parser should work regardless of the NLS settings.

                            1 person found this helpful
                            • 11. Re: Invalid number when parsing json array using JSON_TABLE (but only when executed via ORDS).
                              dmcmahon-Oracle

                              This is a known bug and will be fixed in an upcoming 12.2 rollup release.