Forum Stats

  • 3,751,804 Users
  • 2,250,415 Discussions
  • 7,867,596 Comments

Discussions

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

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

Tagged:

Best Answer

Answers

  • cormaco
    cormaco Member Posts: 1,648 Bronze Crown
    Accepted Answer
  • User_KJA7V
    User_KJA7V Member Posts: 2 Red Ribbon

    Thank you! It worked.

  • User_CI7SY
    User_CI7SY Member Posts: 22 Green Ribbon

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,929 Employee

    please don't hijack threads, start a new one