Facing issues with OSB pluggins for OEPE

I was working with OSB 11.1.1.6 and faced this issue.

The version of oepe supported with osb 11.1.1.6 is 11.1.1.8

I did a setup of weblogic server(10.3.6) with oepe

then i installed osb 11.1.1.6 on top of it to update my oepe with osb pluggins.

I tried to deploy my process from the new eclipse but with no success.

It just got stuck while publishing the data in to server.

I tried all the possible options but was not able to fix it.

I went ahead and checked all the installation files that gets installed with oepe

I came across this file oracle.osb.ide.link in the ..\OEPE\oepe_11.1.1.8.0\dropins

When i opened this i got following entry.

path=C:/Oracle/OEPE/Oracle_OSB1/eclipse140

I checked in few blogs and found that this should point to the osb home

http://mischakoelliker.wordpress.com/2010/08/31/oracle-service-bus-upgrade-to-oepe-11-1-1-6/

So i just removed the reference to eclipse and now my entry says

path=C:/Oracle/OEPE/Oracle_OSB1

After that i restarted my eclipse with a new workspace and i was able to deploy the process to the server.

Retrieve error code and error message in pl/sql

In soa we can use the runtimeFault.wsdl to get the code,summary and details of error messages

or we have the option to get it using prebuild function in advanced column

ora:getfaultAsString() and ora:getFault()

In a similar way we have something in pl/sql which can be used to retrieve the exact error code and error message.

This is required as we often need to capture these details and pass it to the error table so that it can be passed to the BAM for monitoring.

We will see and example on how to capture the error code and error message in pl/sql

Create an error table

CREATE TABLE error_table (
ErrorCode NUMBER,
ErrorMessage VARCHAR2(64),
ErrorTime TIMESTAMP);


Now make sure you have a employee table

IN my case it has an element fullname and employeeid

SET SERVEROUTPUT ON
DECLARE
name employee.fullname%type;
error_code NUMBER;
error_msg VARCHAR2(64);
BEGIN
SELECT fullname INTO name
FROM EMPLOYEE
WHERE EMPLOYEEID = -420;
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_msg := SUBSTR(SQLERRM, 1, 64);
DBMS_OUTPUT.PUT_LINE
('Error code ' || error_code || ': ' || error_msg);


INSERT INTO error_table
VALUES (error_code, error_msg, SYSTIMESTAMP);
END;
/


So once you will execute this code you can go and check the error table it will be populated with the error details

Since you have already used set server output on you will be able to see the error in screen also as below

anonymous block completed
Error code 100: ORA-01403: no data found


There are two variable "SQLCODE" and "SQLERRM" which we have used to retrieve the error code and error message.

These are system defined code and can not be used directly , in order to use them we must assign them to some variable.

Now in an ideal fault handling scenario we can call a procedure in the fault handling block.

One important thing to note here is that when we call a procedure in fault handling block we must call the procedure define with

PRAGMA AUTONOMOUS_TRANSACTION

This is again similar to transaction concept in Oracle SOA suite.

When you define a procedure of function with PRAGMA AUTONOMOUS_TRANSACTION the procedure or function will run in a separate transaction.

that means it will be independent of the primary block where it is getting called.

Triggers in Pl/SQL

As we have event in SOA in a similar way we have triggers in pl/sql

Triggers cause a function/program to be called when a particular event occurs.

Lets understand this example.

The first thing you have know is that you can not have trigger on object hold be sys schema

so connect to the db using a different schema other than sys

now create two tables in the same


CREATE TABLE Old_product
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );

CREATE TABLE New_product
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2)

Insert a dummy value in the new product table

INSERT INTO NEW_PRODUCT VALUES('100','test','ABC','420');

Now the functionality here is that when ever some one will update the new product table the values of the existing changed component should be copied to old product table.

To achieve this target we will use a trigger.

CREATE or REPLACE TRIGGER price_trigger
BEFORE UPDATE OF unit_price
ON New_product
FOR EACH ROW
BEGIN
INSERT INTO Old_product
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;
/

It says when ever the price is updated in the new product table copy that record in the old product table

We will now try to understand it by updating the new product table

UPDATE NEW_PRODUCT SET unit_price = 800 WHERE product_id = 100

Now as soon as you will execute this command

two things will happen

First the new product table will be updated and a new row will be inserted in the old product table

the new product table will contain a unit price of 800 however the old product table will contain a unit price value of 420

Synonym in oracle

You are logged in to database using sys user

and you wanted to retrieve the hr tables.

there are two ways

1>you can call the hr table by prefixing the table name with hr

i.e if you have to refer employee table.

you can do select * from hr.employees.


Or you can define a synonym

create PUBLIC synonym emp for hr.employees

Now you have created a synonym for hr.employees as emp

so in order to retrieve the data from employees table in hr schema you can just execute the below query

select * from emp;

Read data from database and display result using JAVA coding

import java.sql.*;

public class JavaMySQL {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String username = "sys as sysdba";
String password = "welcome1";
ResultSet result = null;

String sql = "SELECT DETAILS From SERVER";

try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
//System.out.println(statement.execute(sql));
statement.execute(sql);
result= statement.getResultSet();
while(result.next() ){
String val = result.getString(1);
System.out.println(val);}
connection.close();
} catch (SQLException e) {
System.err.println(e);
}
}
}

Create an LOV in ADF

Create a new ADF project and it will create a model and view for you.

Click on model and select the ADF business component and Entity object





name it and select the schema you wanted to use.



select the attribute for which you wanted to create lov and say next



Generate view object and app module



Select model and create a view object



select the model you wanted to use



select the field you wanted to create lov


keep saying next and complete the wizard


got to entity object view -->attributes and list of values and create a new LOV



this was the object view created with model and not with view object.

create a new data source and now move the view object that you have create later under the available model



select the attribute .go to ui hints and complete as shown



Complete the wizard and then create a new page.

From the data control drag and drop the name field in the page in the form of single selection ,select one choice as shown



Save and deploy the page you will be able to see the lov