Forum Stats

  • 3,750,404 Users
  • 2,250,174 Discussions
  • 7,866,968 Comments

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 -

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;

Answers

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


     '

    {

      "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":"[email protected]",

         "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
    User_CI7SY Member Posts: 20 Green Ribbon

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

    thatJeffSmith-Oracle