soalicious

Tuesday, April 26, 2022

VBCS - Sample WEB application | Employee CRUD operation web application

Use case:

Create a web application
Create an Employee business object
Page to list all the employees.
Page to on board new employee
Page to view all employee details
Page to update an employee
Page to delete an employee

Steps in detail with screenshots:

Home >> Visual Builder

New Application

Provide application name >> Finish

Navigation hamburger >> Business Objects

Business Object

Provide Business Object Name

By default, we have 5 fields:

Id, CreatedBy, CreationDate, LastUpdatedBy,LastUpdatedDate

Created new field: FirstName, LastName, Age, EmailAddress

Create web application

See the structure of the web apps.

Take Table component

Go to Quick Start

Add Data to Business object

Select Employee BO.

Add fields to show in the employee list.

Add create Page

Select same BO.

Select fields to create for onboard new employee.

Add edit page

Select Employee BO.

Add fields that need to be updated.

Add Detail Page.

Select Employee BO.

Select fields to show details

Add delete action

Select Employee BO.

Finish

The final page looks like below:

All the pages created under main folder.

Lets go to Preview and test .

Click On board

Add an employee

Employee created.

Same way added another employee.

The page looks little messed up so modified the page formats.

Select one employee row and click update employee.

Update the employee fields.

You can see the employee fields have been modified.

Clcik on Employee detail.

See the employee details.

Select employee and delete employee.

The employee gets deleted successfully.

Go to the Business Object >> Data and see all the current employee data.

VBCS page links

All VBCS POCs links below:

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)

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:

Monday, April 25, 2022

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:

Create a customer table and package to insert customer records
Rest trigger to accept customer records.
Call package using db adapter
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)

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.

Thursday, April 21, 2022

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: