I have a procedure on Oracle which takes CLOB input and processes it. When I run it manually using SQL developer, it works fine but when I run it via ORDS it shows below error. Please help.
Error starting at line : 4 in command -
BEGIN
DATA_FRESHNESS_RNK1(p_datedoc => :body_text );
END;
Error report -
ORA-40441: JSON syntax error
ORA-06512: at "SYS.JDOM_T", line 9
ORA-06512: at "SYS.JSON_OBJECT_T", line 115
ORA-06512: at "DISTINV.DATA_FRESHNESS_RNK1", line 43
ORA-06512: at line 2
40441. 00000 - "JSON syntax error"
*Cause: The provided JavaScript Object Notation (JSON) data had invalid
syntax and could not be parsed.
*Action: Provide JSON data with the correct syntax.
Elapsed: 00:00:00.009
-----------------------------------------------My ORDS handler is:------------------------
BEGIN
DATA_FRESHNESS_RNK1(p_datedoc => :body_text );
END;
------------------------------------------and procedure definition is-----------------------
create or replace PROCEDURE DATA_FRESHNESS_RNK1(p_datedoc IN CLOB )
--,p_datedoc_rnk OUT CLOB )
IS
--p_datedoc CLOB ;
--p_datedoc_rnk CLOB ;
datedoc JSON_OBJECT_T;
datearray JSON_ARRAY_T;
datedata JSON_OBJECT_T;
distdata_tmp JSON_OBJECT_T;
distdata_prev JSON_OBJECT_T;
distdata_curr JSON_OBJECT_T;
datescore_prev number;
datescore_curr number;
product_id varchar2(100 char);
part_id_calc varchar2(100 char);
date_calc varchar2(100 char);
date_calc1 date;
date_calc3 date;
date_calc2 number;
distqty number;
distance number;
conv_fact number;
p_conv_fact number;
p_onhand_qty_uom VARCHAR2(30 CHAR) ;
branch_id_calc varchar2(150 char);
datescore number;
distscore number;
prtcption number;
indx number;
swapped boolean;
p_dist_branch_type VARCHAR2(30 CHAR);
p_dist_branch_type_calc VARCHAR2(30 CHAR);
p_edap_part_cat_name VARCHAR2(150 CHAR) ;
p_erp_id varchar2(30 char);
p_erp_name varchar2(30 char);
p_partner_tier_level varchar2(30 char);
p_rank number;
begin
datedoc := JSON_OBJECT_T.parse(p_datedoc);
product_id := nvl(datedoc.get_string('ProductId'),'-123');
datearray := datedoc.get_array('ResultList');
for indx in 0 .. datearray.get_size - 1
loop
CASE
WHEN datearray.get(indx).is_object
THEN
datedata := TREAT (datearray.get(indx) AS json_object_t);
--This TREAT makes each record in ResultList of type JSON
part_id_calc := datedata.get_string('Id');
branch_id_calc := datedata.get_number('Branch_id');
distance := nvl(datedata.get_number('Distance'),-1);
--Fetching few columns data from table DIST\_INV\_TRANS based on branch\_id and part\_id
begin
prtcption := 10;
select source\_dt ,onhand\_qty,onhand\_qty\_uom into date\_calc ,distqty ,p\_onhand\_qty\_uom
from(select branch\_id, part\_id, source\_dt,onhand\_qty,onhand\_qty\_uom,
row\_number() over (partition by branch\_id, part\_id order by source\_dt desc) as rnk
from dist\_inv\_trans)
where rnk = 1 and branch\_id = branch\_id\_calc and part\_id = part\_id\_calc;
EXCEPTION
WHEN NO\_DATA\_FOUND
THEN prtcption := 0;
end;
datedata.put('Participation',prtcption);
--Calculating difference between SYSDATE and the SOURCE\_DT
date\_calc1 := nvl(to\_date(date\_calc,'DD-MON-YYYY'),'31-DEC-1873');
date\_calc3 := to\_date(sysdate,'DD-MON-YYYY');
date\_calc2 := (date\_calc3 - date\_calc1);
--Calculating Date freshness value
datescore := (case when date\_calc2 \< 3 then 20
when date\_calc2 >=4 and date\_calc2 \<= 7 then 16
when date\_calc2 >=8 and date\_calc2 \<= 14 then 12
else 0 end ) ;
--Inserting value of data freshness(F) score into the JSON object datedata
datedata.put('DateScore',datescore);
--Inserting value of Customer proximity(X) into the JSON object datedata
distscore := (case when distance between 0 and 5 then 20
when distance between 6 and 10 then 18
when distance between 11 and 15 then 16
when distance between 16 and 20 then 14
when distance between 21 and 25 then 12
else 0 end);
datedata.put('DistScore',distscore);
BEGIN
--Calculating and Inserting value of Quantity On Hand(Q) into the JSON object datedata
select distinct DIST\_PART\_UOM\_REF.CONV\_FACTOR into p\_conv\_fact from dist\_inv\_trans,DIST\_PART\_UOM\_REF,DIST\_BRANCH\_REF
where DIST\_INV\_TRANS.ONHAND\_QTY\_UOM = DIST\_PART\_UOM\_REF.DIST\_UOM
and DIST\_PART\_UOM\_REF.DIST\_NAME = DIST\_BRANCH\_REF.DIST\_NAME
and DIST\_INV\_TRANS.ONHAND\_QTY\_UOM=p\_onhand\_qty\_uom;
--end;
EXCEPTION
WHEN NO\_DATA\_FOUND
THEN
p\_conv\_fact := 1;
end;
conv\_fact := (case when UPPER(p\_onhand\_qty\_uom) = 'EA' then 1
else p\_conv\_fact end);
distqty := distqty\*conv\_fact;
datedata.put('Qty',distqty);
--Calculating Location Type(L):
select distinct branch\_type,erp\_id,erp\_name into p\_dist\_branch\_type\_calc,p\_erp\_id,p\_erp\_name
from DIST\_BRANCH\_REF,dist\_inv\_trans
where dist\_inv\_trans.branch\_id=DIST\_BRANCH\_REF.dist\_branch\_id
and dist\_inv\_trans.branch\_id = branch\_id\_calc;
p\_dist\_branch\_type :=
(case when p\_dist\_branch\_type\_calc = 'Pickup only' then 10
when p\_dist\_branch\_type\_calc = 'Ship only' then 10
when p\_dist\_branch\_type\_calc = 'Both' then 12
else 0
end
);
datedata.put('Location',p\_dist\_branch\_type);
--Calculating EDAP loyalty status(E)
select distinct nvl(EDAP\_PARTCAT\_REF.EDAP\_PART\_CAT\_NAME,'XXXX') into p\_edap\_part\_cat\_name
from EDAP\_PARTCAT\_REF,DIST\_INV\_TRANS
where EDAP\_PARTCAT\_REF.PDH\_PART\_CAT\_NAME = DIST\_INV\_TRANS.PROD\_DIVISION\_NAME
and DIST\_INV\_TRANS.branch\_id=branch\_id\_calc and part\_id = part\_id\_calc ;
select distinct DIST\_EDAP\_REF.PARTNER\_TIER\_LEVEL into p\_partner\_tier\_level
from DIST\_EDAP\_REF
where DIST\_EDAP\_REF.PART\_CATEGORY\_NAME = p\_edap\_part\_cat\_name
and DIST\_EDAP\_REF.PARTNER\_NUM = p\_erp\_id
and DIST\_EDAP\_REF.SOURCE\_NAME = p\_erp\_name ;
p\_partner\_tier\_level := (case when p\_partner\_tier\_level = 'Tier1' then 10
else 0 end);
datedata.put('E\_Loyalty',p\_partner\_tier\_level);
--Calculating overall Rank
p\_rank := (prtcption + datescore + distscore + p\_dist\_branch\_type + p\_partner\_tier\_level)+(distqty/1000000);
datedata.put('RankScore',p\_rank);
--p\_datedoc\_rnk := datedata.to\_clob;
else
dbms\_output.put\_line('- No input provided ..exiting');
END CASE;
end loop;
/* LOOP
swapped := false;
for indx in 1 .. datearray.get_size - 1
loop
distdata_prev := TREAT (datearray.get(indx-1) AS json_object_t);
distdata_curr := TREAT (datearray.get(indx) AS json_object_t);
DateScore_prev := distdata_prev.get_number('DateScore');
DateScore_curr := distdata_curr.get_number('DateScore');
if DateScore_prev < DateScore_curr
then
distdata_tmp := distdata_prev;
distdata_prev := distdata_curr;
distdata_curr := distdata_tmp ;
swapped := true;
end if;
dbms_output.put_line(distdata_prev.to_string);
dbms_output.put_line(distdata_curr.to_string);
end loop;
EXIT WHEN swapped;
END LOOP;
p_datedoc_rnk := datedoc.to_clob;
dbms_output.put_line('datearray is : ' || datearray.to_string);
dbms_output.put_line(' p_datedoc_rnk is: ' || p_datedoc_rnk);
*/
--p_datedoc_rnk := datedoc.to_clob;
dbms_output.put_line('This procedure calculates Data freshness and stores in the field DateScore in J_OBJECT_T ' || datedoc.to_string);
end;