Forum Stats

  • 3,852,814 Users
  • 2,264,140 Discussions
  • 7,905,150 Comments

Discussions

Parameterized Cursor on JSON_TABLE

Hi Team, I tried to get data into JSON_TABLE and created a parameterized cursor but it is not retrieving the data even data exists in the table. Below is code Am using. Can some one please help if anything else to be done to filter the second line cur for header cur header num.

Second for loop is not filtering the data based on first for loop header number. I tried by hard coding also, looks like the where clause is not filtering on JSON_TABLE.

CREATE OR replace PROCEDURE xx_test2_up (

  p_status  OUT  VARCHAR2,

  p_data   IN  BLOB

) IS

  v_firstname VARCHAR2(100);

  v_lastname VARCHAR2(100);

  v_email_address VARCHAR2(100);

  v_age VARCHAR2(100);

  v_qualification VARCHAR2(100);

  header_id NUMBER;

  CURSOR hdr_cur IS

  SELECT

    hdr.firstname,

    hdr.lastname,

    hdr.emailaddress,

    hdr.age,

    hdr.qualification,

    hdr.header_num

  FROM

      JSON_TABLE ( p_data, '$'

        COLUMNS (

          NESTED PATH '$.Header[*]'

            COLUMNS (

              NESTED PATH '$.parts[*]'

                COLUMNS (

                  firstname VARCHAR2 ( 240 ) PATH '$.firstName',

                  lastname VARCHAR2 ( 80 ) PATH '$.lastName',

                  emailaddress VARCHAR2 ( 100 ) PATH '$.emailAddress',

                  age VARCHAR2 ( 100 ) PATH '$.age',

            --invoice_date VARCHAR2(240) PATH '$.INVOICE_DATE',

                  qualification VARCHAR2 ( 240 ) PATH '$.qualification',

                  header_num VARCHAR2 ( 100 ) PATH '$.HeaderNum'

                )

            )

        )

      )

    hdr;


CURSOR line_cur (

  p_hdr_num varchar2)

is SELECT

     line_rec.firstname,

     line_rec.lastname,

     line_rec.emailaddress,

     line_rec.age,

     line_rec.qualification,

     line_rec.header_num

   FROM

       JSON_TABLE ( p_data, '$'

         COLUMNS (

           NESTED PATH '$.Header[*]'

             COLUMNS (

               NESTED PATH '$.parts[*]'

                 COLUMNS (

                   NESTED PATH '$.Lines[*]'

                     COLUMNS (

                       NESTED PATH '$.parts[*]'

                         COLUMNS (

                           firstname VARCHAR2 ( 240 ) PATH '$.firstName',

                           lastname VARCHAR2 ( 80 ) PATH '$.lastName',

                           emailaddress VARCHAR2 ( 100 ) PATH '$.emailAddress',

                           age VARCHAR2 ( 100 ) PATH '$.age',

            --invoice_date VARCHAR2(240) PATH '$.INVOICE_DATE',

                           qualification VARCHAR2 ( 240 ) PATH '$.qualification',

                           header_num VARCHAR2 ( 100 ) PATH '$.HeaderNum'

                         )

                     )

                 )

             )

         )

       )line_rec

WHERE

  line_rec.header_num = p_hdr_num; 

                   


--type hdr_type is table of hdr_cur%rowtype;

--hdr_rec hdr_type;

--type line_type is table of line_cur%rowtype;

---line_rec line_type;

BEGIN


--open hdr_cur;

--open line_cur;

--fetch hdr_cur bulk collect into hdr_rec;

  FOR hdr_rec IN hdr_cur LOOP

    header_id := ap_invoice_id_seq.nextval;

    INSERT INTO xx_test1 (

      firstname,

      lastname,

      emailaddress,

      age,

      header_id,

      header_num

    ) VALUES (

      hdr_rec.firstname,

      hdr_rec.lastname,

      hdr_rec.emailaddress,

      hdr_rec.age,

      header_id,

      hdr_rec.header_num

    );

---fetch line_cur bulk collect into line_rec;


    FOR line_rec IN line_cur(hdr_rec.header_num) LOOP INSERT INTO xx_test2 (

      firstname,

      lastname,

      header_id,

      line_id,

      header_num

    ) VALUES (

      line_rec.firstname,

      line_rec.lastname,

      header_id,

      escc_ap_invoice_line_id_seq.NEXTVAL,

      hdr_rec.header_num

    );


    END LOOP;


  END LOOP;

--close hdr_cur;

--close line_cur;


  /* SELECT

    rec.firstName,

    rec.lastName,

    rec.emailAddress,

    rec.age,

    rec.qualification

  INTO

    v_firstname,

    v_lastname,

    v_email_address,

    v_age,

    v_qualification

  FROM

      JSON_TABLE ( p_data, '$'

        COLUMNS (

          firstName VARCHAR2 ( 240 ) PATH '$.firstName',

          lastName VARCHAR2 ( 80 ) PATH '$.lastName',

          emailAddress VARCHAR2(100) PATH '$.emailAddress',

          age varchar2(100) PATH '$.age',

            --invoice_date VARCHAR2(240) PATH '$.INVOICE_DATE',

          qualification VARCHAR2 ( 240 ) PATH '$.qualification'

        )

      )

    rec;*/

---insert into xx_test1(firstname,lastname,emailAddress,age,qualification) (


  COMMIT;

  p_status := 'S';

EXCEPTION

  WHEN OTHERS THEN

    p_status := sqlerrm;

END;

Tagged:

Answers