my function which I modified to run directly with JSON payload in Apex SQL Commands environment, it is running but showing errors.
note: on same logic there are more functions I created and are working fine, when received error I modified to run directly with all the same logic. the Oracle Apex version is 24.2
declare
TYPE t_hr_emp_advance_tab IS TABLE OF hr_emp_advance%ROWTYPE;
l_advance_tab t_hr_emp_advance_tab := t_hr_emp_advance_tab();
l_advance_arr JSON_ARRAY_T;
l_advance_obj JSON_OBJECT_T;
current_employee_id number:=0;
errm varchar2(1000);
--
emp varchar2(10);
p_clobs clob :=
'[
{
"id": "483"
"empno": 10007,
"salary_month": "Apr-2025",
"advance_amt": 1000,
"balance_amt": 1000,
"advance_desc": "TESTING",
"advance_mode": "A",
"cheque_no": "12345"
"advance_status": "A"
}
]';
--
p_data blob;
BEGIN
-- ===============================
p_data := apex_util.clob_to_blob(
p_clob => p_clobs,
p_charset => 'AL32UTF8');
-- ================================
l_advance_arr := JSON_ARRAY_T(p_data);
INSERT INTO TEST_ERRORS (ERROR_MSG) VALUES ('first loop start here... 1 ');
commit;
FOR i IN 0 .. l_advance_arr.get_size - 1 LOOP
--
l_advance_tab.extend;
l_advance_obj := TREAT(l_advance_arr.get(i) AS JSON_OBJECT_T);
--
l_advance_tab(l_advance_tab.last).id := l_advance_obj.get_number('id');
l_advance_tab(l_advance_tab.last).empno := l_advance_obj.get_string('empno');
l_advance_tab(l_advance_tab.last).hr_emp_id := hr_get_emp_id(l_advance_obj.get_number('empno'));
l_advance_tab(l_advance_tab.last).salary_month := l_advance_obj.get_string('salary_month');
l_advance_tab(l_advance_tab.last).advance_date := to_date(l_advance_obj.get_string('advance_date'),'YYYY-MM-DD');
l_advance_tab(l_advance_tab.last).advance_amt := l_advance_obj.get_number('advance_amt');
l_advance_tab(l_advance_tab.last).balance_amt := l_advance_obj.get_number('balance_amt');
l_advance_tab(l_advance_tab.last).advance_desc := l_advance_obj.get_string('advance_desc');
l_advance_tab(l_advance_tab.last).advance_mode := l_advance_obj.get_string('advance_mode');
l_advance_tab(l_advance_tab.last).cheque_no := l_advance_obj.get_string('cheque_no');
l_advance_tab(l_advance_tab.last).advance_status := l_advance_obj.get_string('advance_status');
--
END LOOP;
FOR i IN l_advance_tab.first .. l_advance_tab.last
LOOP
current_employee_id := l_advance_tab(i).id;
emp := l_advance_tab(i).empno;
dbms_output.put_line ('in second loop '||emp);
UPDATE hr_emp_advance
SET hr_emp_id = l_advance_tab(i).hr_emp_id,
empno = l_advance_tab(i).empno,
salary_month = l_advance_tab(i).salary_month,
advance_date = l_advance_tab(i).advance_date,
advance_amt = l_advance_tab(i).advance_amt,
balance_amt = l_advance_tab(i).balance_amt,
advance_desc = l_advance_tab(i).advance_desc,
advance_mode = l_advance_tab(i).advance_mode,
cheque_no = l_advance_tab(i).cheque_no,
advance_status = l_advance_tab(i).advance_status
WHERE id = l_advance_tab(i).id;
END LOOP;
COMMIT;
dbms_output.put_line ('advance Successfully updatedd...');
EXCEPTION
WHEN OTHERS THEN
errm := dbms_utility.format_error_backtrace||' -:- '||dbms_utility.format_error_stack;
dbms_output.put_line ('Error: hr_emp_advance_update for Employee: '||emp||' - '||errm);
END;
when running it is showing errors as below ( this time I run it in SQL Plus environment ):
EDUX@orclpdb> set serveroutput on size 50000
EDUX@orclpdb> /
hr_emp_advance_update for Employee: 0 - ORA-06512: at "SYS.JDOM_T", line 22
ORA-06512: at "SYS.JSON_ARRAY_T", line 59
ORA-06512: at line 31
-:- ORA-40441: JSON syntax error
ORA-06512: at
"SYS.JDOM_T", line 22
ORA-06512: at "SYS.JSON_ARRAY_T", line 59
PL/SQL procedure successfully completed.
please help what I am missing or doing wrong? also please guide for a better approach to achieve this update if any…
with kind regards
note: don't know why it is showing some lines of text in Bold and bigger Font?
.