Friday, December 9, 2022

ERP - BI Roles and Configurations

BI Roles & Configuration:

The user should have BI abstract role, attached with "Manage reports & analytics function security policy" priviledge and BI administration role. 

To configure follow below steps:

Navigator >> Tools >> Security console

Create a Role like XXDEMO_BI_ROLE where role category as BI - Abstract Roles.

Add function security policy as Manage Reports & Anlytics.

Add role membership to BI admin.

Add user to the created role.

Verify whether the user has assigned all the roles.
Users >> search user

To synchronizes users, roles and role grants with definitions in LDAP.

Navigator >> Tools >> Scheduled Processes

Schedule New process

Search Retrive Latest LDAP Changes.


We can see the job is running and will succeed and some time.

Now we are ready to create  or manage BI reports.

To create report;
Navigation to BI reports:

Navigator >> Tools >> Reports & Analytics >> Browse Catalog

Here we can see 2 folders:

My Folders: Reports for a particular user, is kept and not visible to any other user.

Shared folders: reports saved in this folder, will be visible to all users having accesa to Reports and Analytics.

Following roles need to show data model under Reports & Analytics:

Tuesday, December 6, 2022

OIC - Twilio adapter - send SMS / WhatsApp from Oracle Integration

UseCase: Using Twilio adapter, we can send SMS / whatsapp from OIC. Here, we will send a sms and whatsapp messages as well using Twilio adapter.

Implementation detailed steps:

First step: Get a Twilio Trial account from . Then a Trial number - this will be used to send messages

Messaging >> get Setup >> create messageing service.

Second Step: Create a Twilio connection

My first Twilio account >> gather account sid, Auth token , required for OIC twilio connection.

Also take my twilio phone number which will be used as sender number.

Third step: Create an Integration and invoke Twilio adapter

Exposing a rest adapter to receive sender , receiver and sms details and send a status .

Select created twilio adapter and configure.

Select operation as Send SMS or MMS.

Complete the mapping 

Add tracking, save and activate and test.

Sms on phone:.

Fifth step: Add verified called Ids from Twilio trial account

For send Whatsapp messages, follow the below steps:

Twilio account >> Messaging >> Try it out >> Send a WhatsApp Message

Activate your sandbox

Save the number provided in the screenshot in your mobile device and write below code to connect:
Join <Sandboxname>

My case: Join page-giant

Sandbox is connected.

Test the same sms integration with "whatsapp:" prefix before the sender and receiver numbers as below:

Bang! We have received the whatsapp message.

To disconnect the sandbox, just write "stop".


Friday, December 2, 2022

OIC - How to call DB Procedure Asynchronously

Usecase: Here, we will create a db package. In the package, we will create 2 procedures. 1st one, wrapper proc which will call the 2nd main proc asynchronously using dbms_scheduler.create_job() function.

**call the procedure via dbms_scheduler.create_job for a single immediate run. Parameter values for create_job to support single immediate run:

Job_name: a unique name say, 'async_main_proc' concatenated with attribute1 to prevent concurrency conflict
Job_action: plsql block invoking main procedure async way.
Enabled: TRUE ( DEFAULT is false)
Auto_drop: TRUE ( default is TRUE)

Why we are creating/invoking async procedure:
Suppose we are invoking a db service procedure and if it takes more than 5 minutes to complete, then the process will be timed out. Even if the proc does not have OUT parameter, proc will be executed in synchronous mode and it waits for the completion of procedure execution. To overcome this limitation, first we need to create a wrapper procedure which calls the main procedure using dbms scheduler option inside the wrapper procedure.

Detailed steps:

Create a table:

Create table async_main_proc(
Attribute1 varchar2(200),
Attribute2 varchar2(200));

DB package specification:

Create or replace package XX_ASYNC_DEMO_PKG
     Procedure wrapper_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2);
     Procedure async_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2);
End xx_async_demo_pkg;

DB package Body:

Create or replace package body xx_async_demo_pkg
     Procedure wrapper_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2)
         Dbms_output.put_line('--- start wrapper proc ---' || systimestamp);
         --A separate procedure called asynchronously
         Dbms_output.put_line('before async_main_proc' || systimestamp);
           Job_name => 'async_main_proc'||Attribute1,
           Job_type => 'PLSQL_BLOCK',
           Job_action =>
           Enabled => TRUE,
           Auto_drop => TRUE,
           Comments => 'call main proc steps');
         Dbms_output.put_line('after async_main_proc' || systimestamp);
         Dbms_output.put_line('--- end wrapper_proc' || systimestamp);

     Procedure async_main_proc(
       Attribute1 in varchar2,
       Attribute2 in varchar2)
         Sql_stmt varchar2(200);
           Dbms_output.put_line('--- start async_main_proc ---' || systimestamp);
           Sql_stmt := 'insert into      async_main_proc(attribute1,attribute2) values(:1,:2)';
           Execute immediate sql_stmt using attribute1,attribute2;
           Dbms_output.put_line('--- end async_main_proc ---' || systimestamp);
End xx_async_demo_pkg;

Test the procedure from db:
Exec xx_async_demo_pkg.wrapper_main_proc('test','abc')

OIC integration flow: Create the Database connection and call the package and wrapper procedure using db adapter.

Test from DB:


Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...