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.