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 with calling ORDS REST API with JSON input, from an external REST client

User_KJA7VJun 9 2021

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

This post has been answered by cormaco on Jun 10 2021
Jump to Answer

Comments

cormaco
Answer

The implicit parameter :body is of type BLOB, to pass a CLOB use :body_text
Installation, Configuration, and Development Guide (0 Bytes)

Marked as Answer by User_KJA7V · Jun 10 2021
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

1 - 4

Post Details

Added on Jun 9 2021
4 comments
6,265 views