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