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

riedelme
This forum is about SQL*Developer issues. You should get a better answer faster if you post in the General Oracle forum
1 - 1

Post Details

Added on Jun 9 2021
4 comments
5,715 views