Thursday, August 27, 2020

#798 OIC DB Adapter - some more adventures...

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 -


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)
  v_status varchar2(10);


     INSERT INTO customers
     ( cust_Name,
       createdDate )
     ( custName_in,
       createdDate_in );

     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

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 -

No comments: