Database - schedule using DBMS JOB

DBMS JOB is a job scheduler package which schedules and manages jobs in the job queue.
Use case:
Creating a job(execute hourly) which calls a package and procedure to update the polling_status field of the table from 1 to 2, then these table records become candidates for the SOA db polling and further processing. also, update the job execution from hourly to daily once.

To check the existing jobs:
Select * from user_jobs;

Create the required table, package and procedure.
Query to create table:
 CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50),
  polling_status number(2),
  eligble_for_credits varchar2(10),
  processing_status varchar2(50)
);

Package Specification:
create or replace PACKAGE "CUSTOMEREVENTLOG"
AS
PROCEDURE UPDATE_POLLING_STATUS;       
END CUSTOMEREVENTLOG;
Package body:
create or replace PACKAGE BODY "CUSTOMEREVENTLOG"
AS
PROCEDURE UPDATE_POLLING_STATUS
AS
BEGIN
  UPDATE customers
  SET POLLING_STATUS = '2'
  WHERE POLLING_STATUS = '1';
END  UPDATE_POLLING_STATUS;
END CUSTOMEREVENTLOG;

Query to create JOB:
To submit a job to the job queue, use the following syntax:
DBMS_JOB.SUBMIT(
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2,
   next_date IN     DATE DEFAULT SYSDATE,
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,
   force     IN     BOOLEAN DEFAULT FALSE);

*JOB: This is the identifier assigned to the job you created. You must use this job number whenever you want to alter or remove the job.
*WHAT: This is the PL/SQL code you want to have executed. The WHAT parameter must end with a semi-colon.
*NEXT_DATE: The next date when the job will be run. The default value is SYSDATE.
*INTERVAL: The date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL. This parameter is a VARCHAR2 and must be enclosed in single quotes.
*NO_PARSE: This is a flag. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.

*Instance and Force: Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value. Oracle displays error ORA-23319 if the instance value is a negative number or NULL.The force parameter defaults to false. If force is TRUE, any positive integer is acceptable as the job instance. If force is FALSE, the specified instance must be running, or Oracle displays error number ORA-23428.

Created Job:
DECLARE
  v_numjob NUMBER := 111;
BEGIN
 DBMS_JOB.SUBMIT(v_numjob,'CUSTOMEREVENTLOG.UPDATE_POLLING_STATUS();',sysdate,'sysdate+1/24');
END;
/

Job execute Hourly

Wait 1 hour and check the result:
DBMS job runs and it updated the polling status from 1 to 2.

Enable/disable oracle job using job id:
Disable:
begin
  dbms_job.broken(101, true);
  commit;
end;
/

Enable:
begin
  dbms_job.broken(101, false);
  commit;
end;
/

**Broken =N means start and Broken =Y means stop job

Job Intervals examples
Execute daily          'SYSDATE + 1'
Execute once per week  'SYSDATE + 7'
Execute hourly         'SYSDATE + 1/24'
Execute every 10 min.  'SYSDATE + 10/1440'
Execute every 30 sec.  'SYSDATE + 30/86400'

DBMS_JOB.CHANGE
To alter user-definable parameters associated with a job, use the following syntax:

DBMS_JOB.CHANGE(  JOB IN BINARY_INTEGER,
   what                  IN VARCHAR2 DEFAULT NULL,
   next_date             IN DATE DEFAULT NULL,
   interval              IN VARCHAR2 DEFAULT NULL,
   instance              IN BINARY_INTEGER DEFAULT NULL,
   force                 IN BOOLEAN DEFAULT FALSE );

Example:  To execute daily
DECLARE
BEGIN
DBMS_JOB.CHANGE(101,'CUSTOMEREVENTLOG.UPDATE_POLLING_STATUS();',sysdate,'sysdate+1');
COMMIT;
END;
/

For more details click here Oracle-Help-Center.
You can also schedule using DBMS_SCHEDULER. Fore more details click here DBMS_SCHEDULER.

SOA 12c - Create Integrated weblogic server default domain

To create the default domain, go to Window⇾Application Servers

 Right click on IntegratedWebLogicServer and click on Create Default Domain.

 Enter the domain password and accept defaults.

Domain is building now.

Start the server

Open the http://localhost:7101/console.

12c SOA - How to test Xquery from Jdeveloper

Step1: Open the Xquery in source mode and right click and click Run Xquery option.

Step2: For source variables which called the List of variables to bind, have to provide the xml inputs to test.

Step3: Here Choose Generate XML by Schema option and select Set XML Occurrence to 1 then select Save Generated XML to file and choose the input file.

 Step4: Click Add to sequence and OK.

 Step5: Give a target file name where u can see the output and click the Run button.

 Bang! You can see the output.

12c OSB - Java Callout

Sometimes Oracle Service Bus is not sufficient to meet our requirements. To do so OSB provides Java Callout activity that is used to call java code from OSB.

Restrictions to use Java Callout activity:
• Java code must be packaged as jar
• The java method that will be called from OSB, must be static.

Implementation: 
Create an Custom application

 Create a simple java class:Application⇾Project⇾Java class

Create jar that will contain the .class file.

 Jar file is created and Copy the jar file in the OSB project

Create a OSB project to use the created Jar file

Create folder structures like, proxyServices, businessServices, resources etc

 Create a XSD

 Create a Proxy WSDL using the created XSD.

 Move the jar file in JAR folder under resources folder

 Create a Proxy service using that created WSDL

 Proxy and pipeline created

 Drag the Java Callout activity in the pipeline and configure the properties

 Select the class from jar

 Give a variable name for storing the response will be received from the java callout.

 Go the expression and select the inputs

Method: Browser the jar and select the method that you want to call and click OK

Arguments: Set the value of arguments

Return: Assign the variable in which you want to get the output of the method

Drag the log activity and print the variable.

Export the OSB project and deploy to the service bus server,

 Testing: from pipeline

 You can see the response in concatenation of the hello and names under invocation trace

You can observe following issue during the development.

Issue details:

<May 30, 2017 7:17:49 AM MDT> <Error> <oracle.osb.stages.transform.transformloggables> <OSB-382516> <Failed to evaluate expression for callout to java method "public static java.lang.String helloproject.HelloWorld.hello(java.lang.String,java.lang.String)". Argument index: 1, exception: Value of type 'org.apache.xmlbeans.impl.values.XmlAnyTypeImpl' cannot be type-cast to 'java.lang.String'>

Solution:

The error is occurring because it can't pass XmlAnyType TO your static java method. You aren't specifying what you pass to the Java method, so I can only assume you aren't casting it properly to the right type. It gets an XmlAnyTypeImpl which needs to be converted to something else (String, int, boolean, etc.). You can probably just tweak what you're passing to the Java callout using some form of the XS Constructors: