Forum Stats

  • 3,750,404 Users
  • 2,250,174 Discussions


Issue while calling procedure using JSON data on ORDS

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 -


DATA_FRESHNESS_RNK1(p_datedoc => :body_text );


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:------------------------


                           DATA_FRESHNESS_RNK1(p_datedoc            => :body_text );


------------------------------------------and procedure definition is-----------------------

create or replace PROCEDURE DATA_FRESHNESS_RNK1(p_datedoc  IN CLOB )

--,p_datedoc_rnk OUT CLOB )


--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;




 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



    WHEN datearray.get(indx).is_object 


      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 


      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;



      THEN prtcption := 0;




      --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



      --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);




      --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



      and DIST_INV_TRANS.ONHAND_QTY_UOM=p_onhand_qty_uom;





      p_conv_fact := 1;


      conv_fact := (case when UPPER(p_onhand_qty_uom) = 'EA' then 1 

             else p_conv_fact end);

      distqty := distqty*conv_fact;



      --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





       --Calculating EDAP loyalty status(E)

      select distinct nvl(EDAP_PARTCAT_REF.EDAP_PART_CAT_NAME,'XXXX') into p_edap_part_cat_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);



      --Calculating overall Rank

      p_rank := (prtcption + datescore + distscore + p_dist_branch_type + p_partner_tier_level)+(distqty/1000000);


      --p_datedoc_rnk := datedata.to_clob;


     dbms_output.put_line('- No input provided ..exiting');

   END CASE;    

  end loop;

/* LOOP  

 swapped := false;

  for indx in 1 .. datearray.get_size - 1


      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


        distdata_tmp := distdata_prev;

        distdata_prev := distdata_curr;

        distdata_curr := distdata_tmp ;

        swapped := true;

      end if;   



  end loop;  

  EXIT WHEN swapped;


  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); 



  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,906 Employee

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

  • User_CI7SY
    User_CI7SY Member Posts: 20 Green Ribbon

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







         "Address1":"3121 US Hwy 22 East",

         "Address2":"Suite 301",





         "BusinessHours":"Mon-Tue 8:00 AM-5:00 PM|",










         "EmailAddress":"[email protected]",















         "Name":"Atlantic City Dinettes",







         "TimeZone":"(GMT-05:00) Eastern Time (US Canada)",






    }  '

  • User_CI7SY
    User_CI7SY Member Posts: 20 Green Ribbon

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