8 Replies Latest reply on Apr 7, 2018 5:20 PM by Pierre Yotti

    generate nested json with PAGINATION in ORDS

    Pierre Yotti

      Hi,

      any ideas why generate nested json doest not support Pagination?

       

      It was already so in old version. Now I use ords 17.4. Still the same issues.

       

      I think nested json with Pagination work im Spring boot. Why not in ords?

       

      I use ORDS 17.4

       

      Oracle database 12c

       

      How can i paginate that nested json https://apexea.oracle.com/pls/apex/paul/hr/empinfo/

       

      That is the statement behind the Service

       

      SELECT

          c.cust_first_name,

          c.cust_last_name,

          c.cust_city,

          CURSOR (

              SELECT

                  o.order_total,

                  CURSOR (

                      SELECT

                          p.product_name,

                          i.quantity,

                          i.unit_price

                      FROM

                          demo_order_items i,

                          demo_product_info p

                      WHERE

                              o.order_id = i.order_id

                          AND

                              i.product_id = p.product_id

                  ) product

              FROM

                  demo_orders o

              WHERE

                  c.customer_id = o.customer_id

          ) orders

      FROM

          demo_customers c

        • 1. Re: generate nested json with PAGINATION in ORDS
          Pierre Yotti

          When i try to make it manually with

           

          fetch first n rows only;

           

          That is the error that i receive:

           

          Bildschirmfoto 2018-04-05 um 13.52.05.png

          • 2. Re: generate nested json with PAGINATION in ORDS
            thatJeffSmith-Oracle

            https://twitter.com/thatjeffsmith/status/981878615729164288

             

            The way we page the results, analytic functions and using a cursor - means you can't use a cursor in your query or you'd get a ORA-22902.

             

            This:

             

            SELECT ID, NAME CUST_NAME, ADDRESS,

            CURSOR(

              SELECT ACCOUNT_ID,

                     NAME ACCOUNT_NAME,

                     BALANCE

              FROM ACCOUNTS

              WHERE CUST.ID = CUSTOMER_ID) AS "accounts",

              '../customers/' || ID || '/pic' AS "$signature"

            FROM customers cust

             

            Gets turned into this when attempting to paginate the results

             

            select q_.* , row_number() over (order by 1) RN___ from (

            SELECT ID, NAME CUST_NAME, ADDRESS,

            CURSOR(

              SELECT ACCOUNT_ID,

              NAME ACCOUNT_NAME,

              BALANCE

              FROM ACCOUNTS

              WHERE CUST.ID = CUSTOMER_ID) AS "accounts",

              '../customers/' || ID || '/pic' AS "$signature"

            FROM customers cust

            ) q_

            )

            where RN___ between :1 and :2 , OriginalSql = select * from (

            select q_.* , row_number() over (order by 1) RN___ from (

            SELECT ID, NAME CUST_NAME, ADDRESS,

            CURSOR(

              SELECT ACCOUNT_ID,

              NAME ACCOUNT_NAME,

              BALANCE

              FROM ACCOUNTS

              WHERE CUST.ID = CUSTOMER_ID) AS "accounts",

              '../customers/' || ID || '/pic' AS "$signature"

            FROM customers cust

            ) q_

            )

            where RN___ between ? and ?

             

            Which results in a

            Error Msg = ORA-22902: CURSOR expression not allowed

             

             

            So, it's not supported due to the way we paginate SQL based result sets.

            1 person found this helpful
            • 3. Re: generate nested json with PAGINATION in ORDS
              Pierre Yotti

              Hi Jeff,

              Thank for the input. Any workaround to make it works? To write custom pagination in my select statement? 

              ThanksPierre

               

              Gesendet von Yahoo Mail auf Android

               

                Am Do., Apr. 5, 2018 at 16:54 schrieb community-admin<community-prod-admin_ww@oracle.com>:   

              |

              generate nested json with PAGINATION in ORDS

               

              reply from thatJeffSmith-Oracle in ORDS, SODA & JSON in the Database - View the full discussion

               

              https://urldefense.proofpoint.com/v2/url?u=https-3A__twitter.com_thatjeffsmith_status_981878615729164288&d=DwIFaQ&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=J0c9pJSHG4nYqXAMPCi8W4OODRgLT8Gma9DHAp5n8DHBwoVbFCCQ4OEmzUQt6qul&m=7B7GR-A7kRpfaBUZwPQT7eov_oqxCcoZ51cd4__7CJo&s=iVDFo3mfaUsiATTChzhuVXYDNf2PvaBEnG3G1W5EgU0&e=

               

               

               

              The way we page the results, analytic functions and using a cursor - means you can't use a cursor in your query or you'd get a ORA-22902.

               

               

               

              This:

               

               

               

              SELECT ID, NAME CUST_NAME, ADDRESS,

               

              CURSOR(

               

                SELECT ACCOUNT_ID,

               

                       NAME ACCOUNT_NAME,

               

                       BALANCE

               

                FROM ACCOUNTS

               

                WHERE CUST.ID = CUSTOMER_ID) AS "accounts",

               

                '../customers/' || ID || '/pic' AS "$signature"

               

              FROM customers cust

               

               

               

              Gets turned into this when attempting to paginate the results

               

               

               

              select q_.* , row_number() over (order by 1) RN___ from (

               

              SELECT ID, NAME CUST_NAME, ADDRESS,

               

              CURSOR(

               

                SELECT ACCOUNT_ID,

               

                NAME ACCOUNT_NAME,

               

                BALANCE

               

                FROM ACCOUNTS

               

                WHERE CUST.ID = CUSTOMER_ID) AS "accounts",

               

                '../customers/' || ID || '/pic' AS "$signature"

               

              FROM customers cust

               

              ) q_

               

              )

               

              where RN___ between :1 and :2 , OriginalSql = select * from (

               

              select q_.* , row_number() over (order by 1) RN___ from (

               

              SELECT ID, NAME CUST_NAME, ADDRESS,

               

              CURSOR(

               

                SELECT ACCOUNT_ID,

               

                NAME ACCOUNT_NAME,

               

                BALANCE

               

                FROM ACCOUNTS

               

                WHERE CUST.ID = CUSTOMER_ID) AS "accounts",

               

                '../customers/' || ID || '/pic' AS "$signature"

               

              FROM customers cust

               

              ) q_

               

              )

               

              where RN___ between ? and ?

               

               

               

              Which results in a

               

              Error Msg = ORA-22902: CURSOR expression not allowed

               

               

               

               

               

              So, it's not supported due to the way we paginate SQL based result sets.

               

              Reply to this message by replying to this email, or go to the message on Oracle Community

              Start a new discussion in ORDS, SODA & JSON in the Database at Oracle Community

              Following ORDS, SODA & JSON in the Database in these streams: Connections Stream

              Following generate nested json with PAGINATION in ORDS in these streams: Inbox

               

                 

               

               

               

              • 4. Re: generate nested json with PAGINATION in ORDS
                thatJeffSmith-Oracle

                You'd have to use PL/SQL and page the results yourself.

                1 person found this helpful
                • 5. Re: generate nested json with PAGINATION in ORDS
                  Pierre Yotti

                  Yes. Can you please give some example how to make it in pl/sql or can you blog it?

                   

                  We want to have prev and next links too.

                   

                  Thanks

                  Pierre

                  • 6. Re: generate nested json with PAGINATION in ORDS
                    thatJeffSmith-Oracle

                    It's complicated. You'd have to query X rows, your pagesize, plus 1 or 2 extra to see if there were more. Then print those, Then print links to next page. And then have a dynamic query that only gets rows for certain ROWNUMs. I've seen it done before from others...

                    1 person found this helpful
                    • 7. Re: generate nested json with PAGINATION in ORDS
                      Pierre Yotti

                      Hi Jeff, Can you share links? Can you make a pagination for a sample select with Pl/sql? For example how to make pagination for Select * from emp with pl/sql source type. When you give me for that select, I will make for the nested json. Thank to have you here

                      • 8. Re: generate nested json with PAGINATION in ORDS
                        Pierre Yotti

                        Hi Jeff,

                         

                        got it. here is how to make custom pagination for sample sql query with PL/SQL as Source Typ.

                         

                        DECLARE

                            v_count      NUMBER;

                            v_previous   VARCHAR2(255);

                            v_next       VARCHAR2(255);

                            v_limit      NUMBER := 5;

                         

                         

                            FUNCTION is_number ( p_string IN VARCHAR2 ) RETURN INT IS

                                v_new_num   NUMBER;

                            BEGIN

                                v_new_num := to_number(p_string);

                                RETURN 1;

                            EXCEPTION

                                WHEN value_error THEN

                                    RETURN 0;

                            END is_number;

                         

                         

                        BEGIN

                            IF

                                is_number(:m) = 0

                            THEN

                                :status := 400;

                                OPEN :error FOR

                                    SELECT

                                        'there is some errors' message,

                                        1 code,

                                        400 status

                                    FROM

                                        dual;

                         

                         

                            ELSE

                                SELECT

                                    COUNT(*)

                                INTO

                                    v_count

                                FROM

                                    eba_demo_chart_emp;

                         

                         

                                OPEN :items FOR

                                    SELECT

                                        *

                                    FROM

                                        (

                                            SELECT

                                                q.*,

                                                ROW_NUMBER() OVER(

                                                    ORDER BY 1

                                                ) rn

                                            FROM

                                                (

                                                    SELECT

                                                        *

                                                    FROM

                                                        eba_demo_chart_emp

                                                ) q

                                        )

                                    WHERE

                                        rn BETWEEN DECODE(

                                            :m,

                                            '',

                                            0,

                                            :m + 1

                                        ) AND nvl(:m + v_limit,v_limit);

                         

                         

                                IF

                                    to_number(nvl(:m,0) ) < to_number(v_count)

                                THEN

                                    :hasmore := 'true';

                                ELSE

                                    :hasmore := 'false';

                                END IF;

                         

                         

                                :limit := v_limit;

                                :offsete := nvl(:m,0);

                                SELECT

                                    'https://apexea.oracle.com/pls/apex/paul/hr/custompagination?m='

                                     || nvl(

                                        (:m - v_limit),

                                        v_limit

                                    ) "$previous"

                                INTO

                                    v_previous

                                FROM

                                    dual;

                         

                         

                                SELECT

                                    'https://apexea.oracle.com/pls/apex/paul/hr/custompagination?m='

                                     || nvl(

                                        (v_limit +:m),

                                        v_limit

                                    ) "$next"

                                INTO

                                    v_next

                                FROM

                                    dual;

                         

                         

                                IF

                                    to_number(:m) > v_count

                                THEN

                                    :next := '';

                                ELSE

                                    :next := v_next;

                                END IF;

                         

                         

                                IF

                                    to_number(:m) < 0 OR

                                        :m IS NULL

                                    OR :m = ''

                                THEN

                                    :previous := '';

                                ELSE

                                    IF

                                        :m = v_limit

                                    THEN

                                        :previous := 'https://apexea.oracle.com/pls/apex/paul/hr/custompagination';

                                    ELSE

                                        :previous := v_previous;

                                    END IF;

                                END IF;

                         

                         

                            END IF;

                        EXCEPTION

                            WHEN OTHERS THEN

                                :status := 500;

                                OPEN :items FOR

                                    SELECT

                                        'there is some errors' message,

                                        1 code,

                                        500 status

                                    FROM

                                        dual;

                         

                         

                        END;

                         

                        The json look like so

                         

                        Bildschirmfoto 2018-04-06 um 22.54.18.png

                         

                         

                        and it works fine

                         

                        Demo

                        https://apexea.oracle.com/pls/apex/paul/hr/custompagination/

                        1 person found this helpful