Create a REST API in OIC to insert multiple records in On-Prem Database using For Loop Concept

By -

In this article, we will learn the concepts of FOR LOOP and utilize it to read the JSON payload to insert multiple records into the On-Prem database table. 



We will create a new integration with the name ForEachDBInsert  as shown in the snippet


 Click on Create




 Click the App Driven Orchestration 




 Provide the name  ForEachDBInsert 



Drop the rest connection trigger to the pallette


 



Provide the end point name 




Fill the response URI and Operation 








Click on JSON sample 



Copy the below sample code 

{
"UserData":[
        {
         "id":"",
         "firstname":"",
         "lastname":"",
"email":"",
         "qualification":""
      },
      {
         "id":"",
         "firstname":"",
         "lastname":"",
"email":"",
         "qualification":""
      }
  ]
}





Provide the JSON sample for the response as well 



JSON sample payload response 
{
"STATUS":""
}




Click on Next and then Done. 



Once rest trigger has been configured , next step is to drop For Each Loop 





Provide the For Loop Name and drag the array list of UserData as a repeating element in the ForLoop Configuration 




Now ForLoop has been configured, the next step is to drop the On-Prem database connector which will map with the request connector to iterate the records  and insert data into a database table 




Provide  the endpoint and select the operation as Run a SQL Statement 





enter the DML statement as shown in the snippet 



Click on Done 


Now next step is to start mapping between Forloop and On-Prem DB Connector 




Drag a mapper to capture the response 




create a filed to store the response 





Enable the tracking identifier 









Activate the integration 




Test the integration by providing the payload to insert data into the database 










Copy the payload pass it in the URL 

{
  "UserData": [
    {
      "id": "1",
      "firstname": "Keith",
      "lastname": "Walton",
      "email": "test123@oracle.com",
      "qualification": "Senior Integration Consultant"
    },
    {
      "id": "2",
      "firstname": "Tom",
      "lastname": "Cooper",
      "email": "test1245@oracle.com",
      "qualification": "OCI Consultant"
    },
    {
      "id": "3",
      "firstname": "Ram",
      "lastname": "Tiwari",
      "email": "xtest123@oracle.com",
      "qualification": "Principal Integration Consultant"
    },
    {
      "id": "4",
      "firstname": "Pranay",
      "lastname": "Tripathi",
      "email": "ytest1245@oracle.com",
      "qualification": "PAAS Consultant"
    },
    {
      "id": "5",
      "firstname": "Brent",
      "lastname": "Walton",
      "email": "test123@oracle.com",
      "qualification": "JAVA Cloud Consultant"
    },
    {
      "id": "6",
      "firstname": "Sheldon",
      "lastname": "Westman",
      "email": "test1245@oracle.com",
      "qualification": "IT ADMIN Consultant"
    }
  ]
}



Provide your OIC Credentials 







Query the table at an on-premise database instance to view the data






Pranay is a versatile Oracle Technical consultant. Adept in Oracle ERP development, SDLC management, design, and architecture. He has 11 years of extensive experience in Oracle Apps implementation engaged in technical development areas of Oracle Applications 11i/R12 E-Business Suite. He possesses sound knowledge in technologies like OAF, ADF, J2EE, MSCA, Oracle PaaS Cloud, Java Cloud services, ADF & XML Publisher. Currently, he is working for BirlaSoft Inc. USA. Being a Consistent solution provider in Oracle Developer Community forums earned him the title of "Technology Master”, Currently he holds 3rd position globally for providing a solution in Oracle Developer Community for the OAF Workspace. He has also authored several articles on ADF Technology, Fusion Financials, OAF & PaaS Cloud. He successfully presented the case study in Oracle Collaborate event forum on "How optimizing the Oracle Mobile App Solution Helped in Boosting the Productivity of a Warehouse."

Comments

  1. Eduauraa9 May 2022 at 02:02

    Nice blog..

    Eduauraa ICSE Class 8 maths 2021 Syllabus for free and plan your study according to the syllabus. icse class 8 maths syllabus

    ReplyDelete

Post a Comment

Popular posts from this blog

Sample HZ API's script to Create Contacts and Contact Points in Oracle

By -
1. Below Code will create Contact Point in Oracle . DECLARE    P_CONTACT_POINT_REC   HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;    P_PHONE_REC           HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;    P_EDI_REC_TYPE        HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;    P_EMAIL_REC_TYPE      HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;    P_TELEX_REC_TYPE      HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;    P_WEB_REC_TYPE        HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;    X_RETURN_STATUS       VARCHAR2 (1000);    X_CONTACT_POINT_ID    NUMBER;    X_MSG_COUNT           NUMBER;    X_MSG_DATA            VARCHAR2 (1000); BEGIN    P_CONTACT_POINT_REC.CONTACT_POINT_TYPE := 'EMAIL';    P_CONTACT_POINT_REC.OWNER_TABLE_NAME := 'HZ_PARTIES';    P_CONTACT_POINT_REC.OWNER_TABLE_ID := 150336; ---L_ORG_PARTY_ID; commented by  pranay tiwari on 05-Jun-2014    P_CONTACT_POINT_REC.CREATED_BY_MODULE := 'TCA_V2_API';    ----email    P_EMAIL_REC_TYPE.EMAIL_FORMAT := 'MAILHTML';  
Read more

REST integration built-in OIC to read Large files with size more than 10MB

By -
Image
Processing large files in any integration requires a lot of effort and complex coding. Every integration platform has a limit size to read a file, similarly, the OIC service provides a limit of 10MB to read the file, if the size exceeds, then the below error will be encountered  Error Reason: Translation Error. Translation Error. Error while translating message to native format. Please make sure that the payload for the outbound interaction conforms to the schema and payload size does not exceed threshold. Error occured as File size exceeds maximum threshold size of 10 MB : Application Error Error Summary: ICS runtime execution error  In this article, we will learn how to read the big-size file in OIC.  Note only XML and CSV formats are supported for larger file read operations.  Here we will build an integration with a source FTP connector to read files containing sizes more than 10 MB  &  read them through the "Read File in Chunks" features and create multiple files  at
Read more