Issue with calling ORDS REST API with JSON input, from an external REST client

User_KJA7V

I built a simple procedure to take JSON input as CLOB and insert it into Oracle 19c database. I exposed this procedure as a POST ORDS REST API endpoint. When I call this API with JSON input through Oracle's web REST client, it works fine. The JSON structure gets inserted into the CLOB field successfully. But when I make the same POST API call through an external REST client ( POSTMAN or SoapUI), it throws a PLSQL error about incompatible data types or incorrect number of arguments. The REST call is able to access the procedure but doesnt accept the JSON input.

Procedure behind the POST API:

BEGIN

LOAD_DISTINV_DOC (p_invdata   => :body);

END;

LOAD_DISTINV_DOC procedure :

create or replace PROCEDURE LOAD_DISTINV_DOC (

 p_invdata  IN CLOB

)

AS

BEGIN

 INSERT INTO TABLE_TEST (ROW_ID, INV_DOC, CREATION_DT, UPDATE_DT, CREATED_BY, UPDATED_BY)

 VALUES (LND_ROWID_SEQ.NEXTVAL, p_invdata, SYSDATE, SYSDATE,'POC_TEST','POC_TEST');

EXCEPTION

 WHEN OTHERS THEN

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

COMMIT;

END;

TABLE_TEST DDL:

CREATE TABLE TABLE_TEST 

  ( "ROW_ID" NUMBER(*,0) NOT NULL ENABLE, 

"INV_DOC" CLOB, 

"PROC_FLG" VARCHAR2(10 CHAR), 

"CREATION_DT" DATE, 

"UPDATE_DT" DATE, 

"CREATED_BY" VARCHAR2(100 CHAR), 

"UPDATED_BY" VARCHAR2(100 CHAR), 

CONSTRAINT "LND_DISTINVAPITRANS_JSONCHK" CHECK ( inv_doc IS JSON ) ENABLE, 

CONSTRAINT "LND_DIST_INV_API_TRANS_PK" PRIMARY KEY ("ROW_ID")

Error :

555 User Defined Resource Error

2021-06-09T18:58:29.106Z | bL6owdwUXUT-d74asKRciA | ORDS-25001

• The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 6550, Error Message: ORA-06550: line 2, column 1: PLS-00306: wrong number or types of arguments in call to 'LOAD_DISTINV_DOC' ORA-06550: line 2, column 1: PL/SQL: Statement ignored

Can anyone please help here ?

Thanks,

Raj Kavuda

cormaco

The implicit parameter :body is of type BLOB, to pass a CLOB use :body_text

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.1/aelig/implicit-parameters.html

cormaco

The implicit parameter :body is of type BLOB, to pass a CLOB use :body_text

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.1/aelig/implicit-parameters.html

User_KJA7V

Thank you! It worked.

User_CI7SY

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;

thatJeffSmith-Oracle

please don't hijack threads, start a new one