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

thatJeffSmith-Oracle

Where's your JSON payload? The db is saying your JSON is bad, but we can't see your JSON.

User_CI7SY

I am using this JSON input and it works fine without ORDS or when I simply replace the bind variable in ORDS.

'
{
"ProductId":"1008",
"FindNearestSearch":"false",
"ResultList":[
{
"Address1":"3121 US Hwy 22 East",
"Address2":"Suite 301",
"Address3":null,
"Address4":null,
"Attributes":null,
"Branch_id":"1231",
"BusinessHours":"Mon-Tue 8:00 AM-5:00 PM|",
"CategoryIds":"",
"City":"Branchburg",
"ContactName":null,
"ContactPosition":null,
"ContactURL":null,
"CountryCode":"US",
"Coupons":null,
"DailyHoursList":null,
"Distance":10,
"EmailAddress":"rxxxxx@xxxxx.electricvine.com",
"Events":null,
"FaceBookPageId":null,
"FaxNumber":null,
"GeoCodeStatusId":2,
"Id":"1000",
"ImageFileUrl":null,
"InternetLocation":false,
"IsLeadManager":false,
"IsStoreLocator":false,
"LMServiceID":false,
"Latitude":40.600771,
"LocationTypeName":"Standard",
"Longitude":-74.686472,
"MobileNumber":null,
"Name":"Atlantic City Dinettes",
"ParticipatingDistributor":true,
"PhoneNumber":"444-444-6464",
"ProductTotal":3142,
"PostCode":"08876",
"State":"NJ",
"ThirdPartyId":null,
"TimeZone":"(GMT-05:00) Eastern Time (US Canada)",
"URL":null
}
],
"SearchReqLogId":10514187,
"TotalResults":2
} '

User_CI7SY
Answer

Issue got resolved when I removed single quote - while passing data to ORDS. Thanks.

Marked as Answer by thatJeffSmith-Oracle · Oct 16 2023
user-u97su

It seems like the error is related to JSON syntax when you're running your procedure through ORDS. Double-check the input you're providing via ORDS, ensuring it adheres to the expected JSON format. Also, consider any differences in how ORDS handles inputs compared to SQL Developer.

If the issue persists, consult the ORDS documentation or community forums for specific Branch advice on handling JSON input in your scenario. They might have insights into potential quirks or adjustments needed when interfacing with ORDS.

Additionally, make sure you're using the correct version of ORDS and Oracle Database, as compatibility issues can sometimes cause unexpected behavior.

Good luck with your Oracle procedure!

Micheal Joni

I'm currently facing an issue while calling a stored procedure using JSON data through Oracle ORDS. The procedure works fine with regular parameters, but when JSON is used as the input payload, it either throws an error or doesn’t process the data as expected. I've already ensured that the JSON structure matches the expected format in the procedure and that the media type is set to application/json.

If anyone has resolved similar issues, especially involving nested JSON objects or handling CLOB input, your suggestions would be greatly appreciated.

Unrelated but worth mentioning — I was testing this setup while working on my Pixellab Mod Apk customization project, where I needed to store user configuration data through ORDS APIs. That’s when I encountered this bug.

Micheal Joni

Issue calling procedure with JSON in ORDS?

Struggling with ORDS not accepting your JSON data for a procedure, especially if you're trying to update something like a Mx Player Menu? Common culprits are invalid JSON, incorrect ORDS template configuration, or the PL/SQL procedure not parsing the JSON correctly. Check your JSON validity, ORDS Content-Type headers, and ensure your stored procedure uses JSON_OBJECT_T or CLOB and parses the incoming data properly.

thatJeffSmith-Oracle

Please don't tag onto 2 year old ‘answered’ posts, start a new thread with your specific details.

1 - 7

Post Details

Added on Aug 24 2021
7 comments
697 views