simple requirement here - I have a DB table that has a primary key filled by a sequence, this table, let's call it the CUSTOMERS table, also has a field dateCreated.
Now I need to insert data into this table via OIC.
A simple flow - REST Trigger - DB Insert and that's it.
So here's the table definition -
DDL -
CREATE TABLE customers (cust_id integer generated by default as identity (start with 1) not null primary key, cust_name varchar2(20) not null, createdDate date not null);
Here is the request payload for the REST trigger -
So now to the configuration of the DB adapter -
First attempt - use INSERT
Note, I cannot de-select the custId column.
Now to the mapping -
Note: I need to map custId - but, this is filled at DB level.
So another approach -
Now to the mapping -
Note, no input parameters are shown.
So what was my solution here?
good old PL/SQL -
I create a stored procedure as follows -
CREATE OR REPLACE Procedure p_createCust
( custName_in varchar2, createdDate_in DATE)
IS
v_status varchar2(10);
BEGIN
INSERT INTO customers
( cust_Name,
createdDate )
VALUES
( custName_in,
createdDate_in );
commit;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
I call this from the DB adapter -
Mapping -
Note I am setting the CreatedDate field to fn:current-date()
I activate and test -
I validate in the DB -
Now I need to insert data into this table via OIC.
A simple flow - REST Trigger - DB Insert and that's it.
So here's the table definition -
DDL -
CREATE TABLE customers (cust_id integer generated by default as identity (start with 1) not null primary key, cust_name varchar2(20) not null, createdDate date not null);
Here is the request payload for the REST trigger -
So now to the configuration of the DB adapter -
First attempt - use INSERT
Note, I cannot de-select the custId column.
Now to the mapping -
Note: I need to map custId - but, this is filled at DB level.
So another approach -
Now to the mapping -
Note, no input parameters are shown.
So what was my solution here?
good old PL/SQL -
I create a stored procedure as follows -
CREATE OR REPLACE Procedure p_createCust
( custName_in varchar2, createdDate_in DATE)
IS
v_status varchar2(10);
BEGIN
INSERT INTO customers
( cust_Name,
createdDate )
VALUES
( custName_in,
createdDate_in );
commit;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
I call this from the DB adapter -
Mapping -
Note I am setting the CreatedDate field to fn:current-date()
I activate and test -
I validate in the DB -
