VBCS page links

All VBCS POCs links below:

1. VBCS overview
2. VBCS - typical visual application workflow
   
3. VBCS - Understand variables
   
4. VBCS - Understand actions and action chains
   
5. VBCS - access and secure business objects
   
6. VBCS - important parts or tools under Navigator
   
7. VBCS - constituents of a web application
   
8. VBCS - provisioning visual builder instance
   
9. VBCS - task1 - Create a web application
10. VBCS - task2 - Create business objects and import data.
    
11. VBCS - task3 - create a business objects diagram
    
12. VBCS - task4 - How to check and test Business Object rest endpoints
    
13. VBCS - task5 - Use main-start page to display one business object like departments
    
14. VBCS - task6 - add a page to create object instance like departments using add create page quick start
    
15. VBCS - task7 - add a page to display business object like employees
    
16. VBCS - task8 - add a page to create employee business object
    
17. VBCS - task9 - change the name of the main-start page
    
18. VBCS - task10 - Add navigation between pages in a webapp | Action chain
    
19. VBCS - task11 - add a page to edit business object like department's details
    
20. VBCS - task12 - add a button to delete a department
    
21. VBCS - task13 - change the app's deafult page
    
22. VBCS - task14 - Access data from an external REST service
    
23. VBCS - task15 - access data from an external REST service using service connection - Define by endpoint option
    
24. VBCS - task16 - Enable the web app as a PWA
    
25. VBCS - task17 - stage the application and import data into the application
    
26. VBCS - task18 - publish the application
    
27. VBCS - sample web application | Employee CRUD operation web application
28. VBCS SDP vs ADP
29. VBCS - Parent Child Relationship between Business objects
30. VBCS - Dependent or Cascading Select dropdown
31. VBCS - Export data to Excel component
32. VBCS - Call OIC integration from Visual Builder
33. VBCS - Create Custom Javascript function in Visual Builder

References:

https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/view-workshop?wid=763

OIC - Database package call to insert Varray size of data | DB package insert in oracle integration

Use Case: Here, we will create a package which is accepting Varray type of elements and then call it and feed customers data from Oracle Integration.

Note: use Varray or nested table to create a valid collection type of the fields. Here we will use Varray types.

Create VARRAY type:

Create or replace TYPE NUMBER_ARRAY IS VARRAY(1000) OF INTEGER;

Create or replace TYPE STRING_ARRAY IS VARRAY(1000) OF VARCHAR2(200);

Create or replace TYPE STRING_ARRAY_ID IS VARRAY(1000) OF VARCHAR2(20);

Package specification:

Create or replace package PKG_CUSTOMERS_MULT AS

PROCEDURE addCustomers(P_Id STRING_ARRAY_ID, P_Name STRING_ARRAY, P_Age NUMBER_ARRAY, P_Address STRING_ARRAY);

END PKG_CUSTOMERS_MULT;

/

PACKAGE BODY:

Create or replace package PKG_CUSTOMERS_MULT AS

PROCEDURE addCustomers(P_Id STRING_ARRAY_ID, P_Name STRING_ARRAY, P_Age NUMBER_ARRAY, P_Address STRING_ARRAY)

IS

BEGIN

FOR i IN P_Id.FIRST .. P_Id.LAST

LOOP

INSERT INTO CUSTOMER1(ID,NAME,AGE,ADDRESS) VALUES(P_Id(i),P_Name(i),P_Age(i),P_Address(i));

END LOOP;

END addCustomers;

END PKG_CUSTOMERS_MULT;

/

Steps in Detail:

Integration flow:

Rest trigger adapter configurarion

Invoke stored procedure steps:

Map rest request to db target package elements.

Note: In the map, for each package Varray field, for each cuatomers, we have to map the item elements individually.

For example, - for P_ID

<P_ID>

   For each customer,

      <P_ID_ITEM>

         Id of each customer

      </P_ID_ITEM>

</P_ID>

Testing:

Input:

Output: DB table:

OIC - Call DB package insert operation | one to one database insert using Package

Use Case: we will create a simple package and using OIC integration, insert the customer records one by one to the database.

Highlevel steps:

1. Create a customer table and package to insert customer records
2. Rest trigger to accept customer records.
3. Call package using db adapter
4. For each customer record, insert to database.

Create table:

Create table customer1(

Id varchar2(20),

Name varchar2(200),

Age number,

Address varchar2(200));

Package Specification:

CREATE OR REPLACE PACKAGE PCK_CUSTOMER AS

PROCEDURE addCustomer(c_id customer1.id%type,

c_name customer1.Name%type,

c_age customer1.age%type,

c_addr customer1.address%type);

END PCK_CUSTOMER;

/

Package Body:

CREATE OR REPLACE PACKAGE BODY PCK_CUSTOMER AS

PROCEDURE addCustomer(c_id customer1.id%type,

c_name customer1.Name%type,

c_age customer1.age%type,

c_addr customer1.address%type)

IS

BEGIN

INSERT INTO CUSTOMER1(ID NAME,AGE,ADDRESS) VALUES(c_id, c_name, c_age, c_addr);

END addCustomer;

END PCK_Customer;

/

Rest trigger request json:

{

"Customers":{

"Customer":[

{

"Id":1,

"Name":"aaa",

"Age":12,

"Address":"qqq"

},

{

"Id":2,

"Name":"aaa",

"Age":12,

"Address":"qqq"

}]

}}

Steps in detail:

Integration flow:

Rest trigger configuration:

For each customer

Invoke  a stored procedure.

Map customer to db target fields.

DB Offset - Fetch clause

Offset and Fetch clauses are used in conjunction with Select and order by clause to provide a means to retrieve a range of records.

Offset: the offset argument is used to identify the starting point to retirn rows from a result set.

Fetch: It is used to return a set of number of rows.

Syntax:

Select column_name(s)

From table_name

Order by column_name

Offset rows_to_skip ROWS

Fetch Next number_of_rows ROWS ONLY;

Example:

Suppose you have 9 rows in a db table and you want to fetch 3rd to 7th one

Select orderNumber,Customer

From orders

Order by orderNumber

Offset 2 ROWS

Fetch Next 5 Rows ONLY;

OIC - DB Procedure call | DB adapter 10 MB limit overcoming solution

Use Case: If we run a db select query and it has humungous data (more than 10MB) then it will fail as it has reached the limit. We can avoid such error using  SQL with offset. In this use case, we will use a Stored procedure.

To know about Offset & Fetch:

https://soalicious.blogspot.com/2022/04/db-offset-fetch-clause.html

Highlevel steps:

1. Create a db table orders.
2. Create a offset select db query
3. Wrap the sql query in a stored procedure.
4. In the integration, 
1. 1st we take the total row count using db adaptet call
2. Determine number of iteration - ceiling(totalRows / batch size)
3. For each iteration,
1. We will select the rows by offset
2. For each row, we will log the row details
3. Increment the counter and offset.

Steps in detail:

Create a table orders:

Create table orders(

"orderNumber" VARCHAR2(20 BYTE),

"Customer" VARCHAR2(200 BYTE)

)

Select offset db query:

Select "orderNumber","Customer" from orders order by "orderNumber"

Offset 2 rows 

Fetch Next 3 rows only

Sql query wrapped up in stored procedure:

CREATE OR REPLACE PROCEDURE OrdersByOffset(in_fetchSize NUMBER, in_offset NUMBER, p_Orders out SYS_REFCURSOR)

IS

BEGIN

OPEN p_Orders for

SELECT "orderNumber","Customer" 

FROM orders

ORDER BY "orderNumber"

OFFSET in_offset ROWS

FETCH NEXT in_fetchSize ROWS ONLY;

END;

/

Create a scheduled orchestration and count total rows from the dp adapter using the following sql:

Select count(1) from orders

Total rows fetch db adaptet config:

Create variables for :

totalRows: number of rows fetched from db adapter.

batchSize: number(4.0)

totalIteration: ceiling(orajs29:divide($totalRows,$batchSize))

Next step is to invoke stored procedure by offset to get the batch of rows.

Assign Counter and Offset:

Counter: number(1)

fetchSize: $batchSize

offSet: number(0)

In the while,

$Counter <=$totalIteration

Invoke stored procedure.

Map the fetchSize and Offset.

Now for each fetched row, will log them and increment the counter and offset.

For each row,

Log the row details:

concat("Order Details - OrderNr / Customer :",$currentRow/nsmpr3:Row/nsmpr3:Column[1]," / ",$currentRow/nsmpr3:Row/nsmpr3:Column[2])

Counter: $Counter + 1

offSet : $offSet + $fetchSize

Testing:

In my case, i have totalRows = 9 in the db orders table and batchSize= 4, so total iterations=9 / 4 = 3.

OIC | Merge two CSV files into one CSV file based on a common field or primary key

Use Case: Here,we will merge two CSV files into one final file based on a common primary key or common field values.

In the below example, we will use EmpId as primary key in both the CSV files for merging.

EmpDetail.csv

EmpId,FName,LName

101,sid,das

102,dip,chak

103,sri,das

EmpContact.csv

EmpId,Contact

101,101_123456

102,102_234567

101,102_987654

103,103_456789

Merged file:

EmpId,FName,LName,Contact

101,sid,das,101_123456 102_887654

102,dip,chak,102_234567

103,sri,das,103_456789

Highlevel steps:

1. Read both the CSV files using FTP adapter one by one.
2. For each emp record of 1st CSV file, we will Map both the files and write the Merge file using FTP adapter. We will use a condition to match the records of the both the file using the EmpId of both the files.

Steps in detail:

Integration flow:

Once both files read, we will loop for each emp record of the 1st file.

Map the current emp details and map the contact using following condition:

"$readEmployeeContact/nsmpr2:SyncReadFileResponse/ns24:FileReadResponse/ns22:EmpContactRecSet/ns22:EmpContactRec[ns22:Empid=$currentEmp/nsmpr1:EmpDataRec/nsmpr1:EmpId]/ns22:Contact"

Test:

Note: instead of taking a for each action, we can also do the same in xslt itself:

Xslt code:

<nstrgmpr:WriteFile xml:id="id_12">

<ns30:Employees>

<xsl:for-each select="$readEmployeeData/nsmpr2:SyncReadFileResponse/ns27:FileReadResponse/ns26:/EmpDataRecSet/ns26:/EmpDataRec">

<xsl:variable name="EmpId" select="ns26:/EmpId"/>

<ns30:Employee>

<ns30:C1>

<xsl:value-of select="ns26:EmpId"/>

</ns30:C1>

<ns30:C2>

<xsl:value-of select="ns26:FName"/>

</ns30:C2>

<ns30:C3>

<xsl:value-of select="ns26:LName"/>

</ns30:C3>

<ns30:C4>

<xsl:value-of select="$readEmployeeContact/nsmpr1:SyncReadFileResponse/ns23:FileReadResponse/ns20:/EmpContactRecSet/ns20:/EmpContactRec[ns20:EmpId=$EmpId]/ns20:Contact"/>

</ns30:C4>

</ns30:Employee>

</xsl:for-each>

</ns30:Employees>

</nstrgmpr:WriteFile>