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;
The views expressed on this blog are my own and do not necessarily reflect the views of any Organisations owning these products.I keep on doing R & D with different products in and around Middle ware stack and these posts are result of that.Most of the post are result of my own experiments or ideas taken from other blogs .If in any case You feel content is not right you can comment to remove that post. This blog uses the default features,cookies of blogspot.com
Wednesday, February 29, 2012
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
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
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
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'
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'