Advertisements
RSS

Tag Archives: Database

Using the Database Adapter in Oracle Service Bus 12c

Introduction

Since the Oracle Service Bus is particularly good in handling high amounts of (small) requests very, very quickly it’s a perfect solution for entity/atomic services. In Oracle Fusion Middleware 11g the solution chosen to decouple a database was often using an Oracle SOA Suite 11g project with JCA and Mediator. However if you had specific requirements beyond basis mapping the Mediator component had it’s limits, and in the worst case you would need to extend the project with a BPEL process. From a developers perspective this is the “easy” solution since both the JCA component and Mediator are configured in JDeveloper 11g. And, the alternative, using JCA with OSB this would mean that you have to import the JCA configuration from JDeveloper 11g into your OEPE 11g. Which means more work. However my personal opinion is, that the SOA/Mediator choice is a “bloated” result for such a simple atomic service implementation. The OSB is much more suitable for atomic/entity services and SOA/BPEL should be used for orchestration purposes.

Lucky for us in Oracle Fusion Middleware 12c the development of simple atomic services to decouple a database in Oracle Service Bus 12c is less work due to tthe fact all features of OFMW are available to the developer in one IDE..

00

Getting Started

In this blogpost we will explain how to create an Oracle Service Bus 12c project that can act as a simple atomic service that can decouple your database.
Since a best practice is to NOT implement SQL code (INSERT, SELECT, UPDATE, etc) in your Middleware component we will use a simple PL/SQL package to communicate with the database.

First we will create a service bus project with the name getCaseService and drag the HTTP transport on the proxy service

01

02

02

03

Then drag the Database adapter on the external services and call it getCaseDB

10

11

12

13

The result will be a Oracle Service Bus project with both the newly created HTTP proxy service and the DB business service:

15_result

Then we can go and configure the pipeline. Here is a basic overview of the final result the pipeline will look like:

30_result_met_BS

In the pipleline we will need 2 transformations for the request and response pipelne. In this example we will show XSLT instead of XQuery, but both are usable. First we create an XSLT for the request  pipeline transformation:

20

21

22

23

24

Finally we will also need to make sure the project has the response XSLT we can use for the response pipeline:

25

26

27

28

After we finalized the project we can now deploy our atomic/entity service to the IntegratedWeblogicServer and test the new service

40_result

Advertisements
 
5 Comments

Posted by on 03-02-2015 in Oracle, OSB

 

Tags: , , ,

Delete all artifacts in an Oracle database schema

In our DEV and TST environment we sometimes want to perform a fresh deployment of a database release. Since we got our schemas from the DBA team, but can’t drop/create them ourselves, we found the script below to remove all artifacts from the schema. Packages, tables, sequences, etc. nothing is safe from this armageddon script.

declare stringa varchar2(100);
cursor cur is
select *
from user_objects;
begin
for c in cur loop
begin
stringa := '';
if c.object_type = 'VIEW' then
stringa := 'drop view ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'TABLE' then
stringa := 'drop table ' || c.object_name || ' cascade constraints';
EXECUTE immediate stringa;

elsif c.object_type = 'SEQUENCE' then
stringa := 'drop sequence ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'PACKAGE' then
stringa := 'drop package ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'TRIGGER' then
stringa := 'drop trigger ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'PROCEDURE' then
stringa := 'drop procedure ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'FUNCTION' then
stringa := 'drop function ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'SYNONYM' then
stringa := 'drop synonym ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'INDEX' then
stringa := 'drop index ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'PACKAGE BODY' then
stringa := 'drop PACKAGE BODY ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'DATABASE LINK' then
stringa := 'drop database link ' || c.object_name;
EXECUTE immediate stringa;

elsif c.object_type = 'TYPE' then
stringa := 'drop type ' || c.object_name;
EXECUTE immediate stringa;

end if;

 exception
when others then
null;
end;
end loop;
-- PURGE recyclebin
end;

Way to valuable not to store, so here it is.
All credits go to Massimo @ Oracle OTN Community for his code.

 
Leave a comment

Posted by on 29-08-2013 in Database, Oracle

 

Tags: ,

Transformation on XMLType in Oracle XML DB

When using a XMLType table/column in Oracle XML DB to store structured data you are able to perform transformations with the XMLTRANSFORM command. We have a requirement to remove all namespaces for the inserted XML content and decided to implement this mechanism in the database.

Probably piece of cake for a Oracle XML DB guru, I needed Google ;-)

CREATE TABLE XMLSTYLESHEETS
(
CODE VARCHAR2(6 CHAR) NOT NULL,
STYLESHEET "XMLTYPE" NOT NULL,
);

We insert the XSLT stylesheet for removing namespaces in the table:

INSERT INTO XMLSTYLESHEETS (CODE, STYLESHEET)
values ('REMNAM' ,'
<xsl:stylesheet version="1.0" xmlns:xsl=http://www.w3.org/1999/XSL/Transform>
<xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@* | node()"/>
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="."/>
</xsl:attribute>
</xsl:template>
<xsl:template match="comment() | text() | processing-instruction()">
<xsl:copy></xsl:copy>
</xsl:template>
</xsl:stylesheet>');

Then we create a trigger to

create or replace
trigger MYTRIGGER
BEFORE INSERT OR UPDATE ON MYTABLEWITHXML
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
-- remove namespaces from XMLTYPE input
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
SELECT XMLTRANSFORM(:new.data_xml, x.stylesheet)
INTO :new.data_xml
FROM XMLSTYLESHEETS x
WHERE x.CODE = 'REMNAM';
-- DBMS_OUTPUT.PUT_LINE(:new.data_xml.GetClobVal());
END;

Let’s test the trigger:

Insert into MYTABLEWITHXML(MYDATAXML)
values ('
<bk:book xmlns:bk="urn:loc.gov:books" xmlns:isbn="urn:ISBN:0-395-36341-6">
<bk:title>Cheaper by the Dozen</bk:title>
<isbn:number>1568491379</isbn:number>
</bk:book>');

When enabling the DBMS_OUTPUT lines we are able to validate the result of the trigger.
JDeveloper DBMS output

 
Leave a comment

Posted by on 13-08-2013 in Oracle, XMLDB, XSLT

 

Tags: , , , ,