Wednesday, February 29, 2012

CFGFWK-60850: Test Failed!

1. Created the table:
CREATE TABLE schema_version_registry(owner VARCHAR2(10), mr_type VARCHAR2(10), version VARCHAR2(50));
2. Get the user by running the below query:
select user from dual;
3. Insert the expected values into the table:
INSERT INTO schema_version_registry VALUES (user_name,'MDS','11.1.1.2.0');
COMMIT;
4. Go to your Configuration Wizard and the Test the connection again.
5. If the test is not successful, then there's some problem with the database connection settings.
6. If the test is successul, drop the table and proceed with your Configuration.
DROP TABLE schema_version_registry;

Monday, February 06, 2012

How to use Xquery in OSB

I had a really tough time working with Xquery and how to use the same in our OSB prject.

I will try to create a simple scenario and will try to explain how to use xquery in a osb project.In my simple scenario i am having some input parameter which i am mapping to the output parameters ,i am using xquery for the mapping and using the osb code to call this Xquery.

Here is how my xsd looks like


<?xml version="1.0" encoding="UTF-8"?>
<schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://xmlns.oracle.com/ADF/Xquery/XquerySample"
xmlns="http://www.w3.org/2001/XMLSchema">
<element name="process">
<complexType>
<sequence>
<element name="Name" type="string"/>
<element name="Id" type="string"/>
</sequence>
</complexType>
</element>
<element name="processResponse">
<complexType>
<sequence>
<element name="Emp_Name" type="string"/>
<element name="Emp_id" type="string"/>
<element name="Salary" type="string"/>
</sequence>
</complexType>
</element>
</schema>

and this is how my wsdl service will look like


<?xml version="1.0" encoding="UTF-8"?>
<wsdl:definitions name="XquerySample"
targetNamespace="http://xmlns.oracle.com/ADF/Xquery/XquerySample"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns:client="http://xmlns.oracle.com/ADF/Xquery/XquerySample"
xmlns:plnk="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">

<!-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TYPE DEFINITION - List of services participating in this BPEL process
The default output of the BPEL designer uses strings as input and
output to the BPEL Process. But you can define or import any XML
Schema type and use them as part of the message types.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
<wsdl:types>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<import namespace="http://xmlns.oracle.com/ADF/Xquery/XquerySample" schemaLocation="xsd/XquerySample.xsd" />
</schema>
</wsdl:types>

<!-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MESSAGE TYPE DEFINITION - Definition of the message types used as
part of the port type defintions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
<wsdl:message name="XquerySampleRequestMessage">
<wsdl:part name="payload" element="client:process"/>
</wsdl:message>

<wsdl:message name="XquerySampleResponseMessage">
<wsdl:part name="payload" element="client:processResponse"/>
</wsdl:message>
<wsdl:portType name="XquerySample">
<wsdl:operation name="getXML">
<wsdl:input message="client:XquerySampleRequestMessage"/>
<wsdl:output message="client:XquerySampleResponseMessage"/>
</wsdl:operation>
</wsdl:portType>
<wsdl:binding name="XquerySampleSOAP11Binding" type="client:XquerySample">
<soap:binding style="document"
transport="http://schemas.xmlsoap.org/soap/http"/>
<wsdl:operation name="getXML">
<soap:operation style="document"
soapAction="http://xmlns.oracle.com/ADF/Xquery/XquerySample/getXML"/>
<wsdl:input>
<soap:body use="literal" parts="payload"/>
</wsdl:input>
<wsdl:output>
<soap:body use="literal" parts="payload"/>
</wsdl:output>
</wsdl:operation>
</wsdl:binding>
<wsdl:service name="XquerySample">
<wsdl:port name="XquerySample11Binding"
binding="client:XquerySampleSOAP11Binding">
<soap:address location="http://arpit.com"/>
</wsdl:port>
</wsdl:service>
<!-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PORT TYPE DEFINITION - A port type groups a set of operations into
a logical service unit.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
<!-- portType implemented by the XquerySample BPEL process -->

<!-- portType implemented by the requester of XquerySample BPEL process
for asynchronous callback purposes
-->

<!-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PARTNER LINK TYPE DEFINITION
the XquerySample partnerLinkType binds the provider and
requester portType into an asynchronous conversation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -->
</wsdl:definitions>


NOw i will import the same in my OSB project.
The same can be imported in any of the OSB server as they is no concrete wsdl file.



Now create a Proxy Service


So this is how our service will look like




Create a xquery from the eclipse tool as shown




Choose input as source



Choose output as target



Now map the input to output as shown below and pass some constant value to salary attribute.



you can test the xquery by clicking on the third button Test as shown below




now import this transformation file in to your project





now go to the flow of OSB and add a pipeline pair



Add a stage in the response pipeline

FlashBack Drop in Oracle

When you drop a table from Oracle database the data is not deleted all of a sudden from Database,It goes to recycle bin just like in our windows.So if you have dropped a table by mistake you can actually recover it from recycle bin of database.By default this feature is not enabled.

First thing is to enable the recycle bin

To enable the recycle bin for a session we can use the following command
ALTER SESSION SET RECYCLEBIN=ON

In a similar away recycle bin for the entire database can be enabled by using following command
ALTER SYSTEM SET RECYCLEBIN=ON

Now you can view that dropped table using the following select command

SELECT * FROM RECYCLEBIN where original_name='name of table'