Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue while calling procedure using JSON data on ORDS

User_CI7SYAug 24 2021

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;

This post has been answered by User_CI7SY on Aug 25 2021
Jump to Answer

Comments

Post Details

Added on Aug 24 2021
7 comments
702 views