Skip to Main Content

Parameterized Cursor on JSON_TABLE

User_4ZOU6Sep 13 2022

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;

Comments
Post Details
Added on Sep 13 2022
1 comment
94 views