Wednesday, June 17, 2020

12c OSB - DVM Implementation


Create a Service bus project

Create a Proxy schema:

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.proxyDVM.org"
targetNamespace="http://www.proxyDVM.org" elementFormDefault="qualified">
<xsd:element name="Request">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CountryCodeIn" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Response">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CountryCodeOut" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Create a proxy wsdl based on the created schema.






Create a proxy and pipeline based on the created proxy wsdl.





Create a Country.dvm 




Open the pipeline and drag and drop pipeline pair node and take a assign activity and use the dvm to fetch the mapping values.

dvm:lookup('DVMSBProject/CountryCode', 'CountryName', $body/prox:Request/prox:CountryCodeIn, 'CountryCode', 'NotFound')


Now create one xquery to map this dvm output to proxy response.














Use the created Xquery in the replace activity



deploy to the server and test





Tuesday, June 16, 2020

MS Outlook Web - how to download mail from outlook web

You can drag and drop emails to add them as attachments as a workaround (see this answer). You can download an email like this:

Start a new message
Click to edit the message in a new window.
Select all emails you would like to download.
Drag the emails to the new message. This will add them as attachments.
Click the down arrow on the attachment and download the email message, or send the message to yourself and then download all attachments.

Monday, June 15, 2020

SOA 12c - Any type xml data to XML parsing

Here we will see how to accept AnyType Data(xsd:anyType) and convert it into a valid xml data.

Data to be passed as Any type XML string data:
<?xml version="1.0" encoding="UTF-8" ?>
<HREmployeeData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:schemaLocation="http://www.example.org SOA/Schemas/XMLFormat.xsd" xmlns="http://www.example.org">
  <First>testF</First>
  <Last>testL</Last>
  <Phone>phNo</Phone>
  <Income>in</Income>
  <Status>Employed</Status>
  <JobHistory>
    <JobTitle>JT</JobTitle>
    <Salary>Sal</Salary>
  </JobHistory>
</HREmployeeData>

Create a Input and Output schema for a sync BPEL service.

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
targetNamespace="http://www.example.org" elementFormDefault="qualified">
<xsd:element name="HREmployeeDataIn">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="AnyTypeXMLData" type="xsd:anyType"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="HREmployeeDataOut">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="First" type="xsd:string"/>
<xsd:element name="Last" type="xsd:string"/>
<xsd:element name="Phone" type="xsd:string"/>
<xsd:element name="Income" type="xsd:double"/>
<xsd:element name="Status" type="xsd:string"/>
<xsd:element name="JobHistory">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="JobTitle" type="xsd:string"/>
<xsd:element name="Salary" type="xsd:double"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Create the schema say "XMLFormat.xsd" which xml string data to be passed as input to the service.

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
targetNamespace="http://www.example.org" elementFormDefault="qualified">
<xsd:element name="HREmployeeData">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="First" type="xsd:string"/>
<xsd:element name="Last" type="xsd:string"/>
<xsd:element name="Phone" type="xsd:string"/>
<xsd:element name="Income" type="xsd:double"/>
<xsd:element name="Status" type="xsd:string"/>
<xsd:element name="JobHistory">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="JobTitle" type="xsd:string"/>
<xsd:element name="Salary" type="xsd:double"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Use the Input and Output schema and create a BPEL Service.
Open the BPLE Process and create a variable varXMLTypeData using XMLformat.xsd
Use parseXML() function to AnyTypeXMLDATA Input and store in varXMLTypeData variable

Please note that when you are selecting the anyType data, in Assign activity this will not get copied in the expression builder, hence you need to manually add this data.

oraext:parseXML($inputVariable.payload/ns1:AnyTypeXMLData)



Parsing done. Now assign the parsed elements to output elements

Deploy and test




SQL - How to Optimize SQL Queries

1. Try not to use select * to query SQL, but select specific fields.
Counter example:
select * from employee;
Positive example:
select id,name from employee;
Reason:
By using only the required fields we can save our resources and reduce network overhead.
It is likely that the covered index will not be used, which will result in a query back to the table.

2. If you know that there is only one query result, it is recommended to use limit 1
Suppose that there is an employee table, and you want to find a person named jay.
CREATE TABLE employee (
id int(11) NOT NULL,
name varchar(255) DEFAULT NULL,
age int(11) DEFAULT NULL,
date datetime DEFAULT NULL,
sex int(1) DEFAULT NULL,
PRIMARY KEY (`id`) );
Counter-example:
select id,name from employee where name='jay';
Positive example:
select id,name from employee where name='jay' limit 1;
Reason:
After adding limit 1, when a corresponding record is found, it will not continue to scan down, and the efficiency will be greatly improved.

3. Try to avoid using or in the where clause to join conditions
Create a new user table, it has a common index userId, the table structure is as follows:
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) )
Suppose now that you need to query users with a userid 1 or the age of 18, it is easy to have the following SQL.
Counter-example:
select * from user where userid = 1 or age = 18;
Positive example:
//Use union all 
select * from user where userid=1 
union all 
select * from user where age = 18;
//Or write two separate SQL
select * from user where userid=1;
select * from user where age = 18;
Reason:
The use of or may invalidate the index and thus it requires a full table scan.
For the case of or without index, it is assumed that it has taken the index of userId, but when it comes to the age query condition, it has to perform a full table scan, which requires a three-step process: full table scan + index scan + merge

4. Optimize limit paging
When we do daily paging, we usually use limits to achieve, but when the offset is particularly large, the query efficiency becomes low. Because Mysql does not skip the offset and directly fetch the data.
Counter-example:
select id,name,age from employee limit 10000,10;
Positive example:
//Solution 1: Return the largest record (offset) of the last query
select id,name from employee where id>10000 limit 10;
//Solution 2: order by + index
select id,name from employee order by id  limit 10000,10;
Reason:
If you use optimization solution 1, it returns the last query record (offset), so that you can skip the offset, efficiency has improved a lot.
Option two uses order by + index, which can also improve query efficiency.

5. Optimize your like statement
In daily development, if you use fuzzy keyword queries, it is easy to think of like, but like is likely to invalidate your index.
Counter-example:
select userId,name from user where userId like '%123';
Positive example:
select userId,name from user where userId like '123%';

6. Use where conditions to limit the data to be queried to avoid returning extra rows
Suppose the scenario is Query whether a user is a member. I have seen the old implementation code like this.
Counter-example:
List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);
Positive example:
Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId!=null;
Reason: Check what data is needed, avoid returning unnecessary data, and save expenses and computational overhead

7. You should avoid using the != or <> operator in the where clause as much as possible, otherwise the engine will give up using the index and perform a full table scan
Counter-example:
select age,name  from user where age <>18;
Positive example:
//You can consider separate two sql write
select age,name  from user where age <18;
select age,name  from user where age >18;
Reason: Using != and <> is likely to invalidate the index

8. If you insert too much data, consider bulk insertion
If you want to insert too much data, consider bulk insert
Counter-example:
for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}
Positive example:
//One batch of 500 inserts, carried out in batches
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>
Bulk insertion performance is good, time-saving
To make an analogy: If you need to move 10,000 bricks to the top of the building, you have an elevator.
The elevator can put an appropriate amount of bricks at a time (up to 500), you can choose to transport one brick at a time, or you can transport 500 bricks at a time. Which time do you think is expensive?

9. Use the distinct keyword with caution
The distinct keyword is generally used to filter duplicate records to return unique records. When used in the case of querying one field or few fields, it brings optimization effect to the query.
However, when there are many fields, it will greatly reduce the query efficiency.
Counter-example:
SELECT DISTINCT * from  user;
Positive example:
select DISTINCT name from user;
Reason: The CPU time and occupancy time of the statement with distinct are higher than the statement without distinct.
Because when querying many fields, if you use distinct, the database engine will compare the data and filter out the duplicate data. However, this comparison and filtering process will consume system resources and CPU time.

10. Remove redundant and duplicate indexes
Counter-example:
KEY 'idx_userId' ('userId')  
KEY 'idx_userId_age' ('userId','age')
Positive example:
//Delete the userId index, because the combined index (A, B) is equivalent to creating the (A) and (A, B) indexes
KEY 'idx_userId_age' ('userId','age')
Reasons: Duplicate indexes need to be maintained, and the optimizer also needs to consider them one by one when optimizing queries, which will affect performance.

11. If the amount of data is large, optimize your modify/delete statement
Avoid modifying or deleting too much data at the same time, because it will cause high CPU utilization, which will affect others’ access to the database.
Counter-example:
//Delete 100,000 or 1 million+ at a time?
delete from user where id <100000;
//Or use single cycle operation, low efficiency and long time
for(User user:list){
   delete from user; }
Positive example:
//Delete in batches, such as 500 each time
delete user where id<500;
delete product where id>=500 and id<1000;
Reason: To delete too much data at one time, there may be a lock wait timeout exceed error, so it is recommended to operate in batches.

12. Consider using default values ​​instead of null in the where clause
Counter-example:
select * from user where age is not null;
Positive example:
select * from user where age>0; //Set 0 as default
Reason:
If you replace the null value with the default value, it often makes indexing possible, and at the same time, the expression will be relatively clear.

13. Try to replace union with union all
If there are no duplicate records in the search results, it is recommended to replace union with union all.
Counter-example:
select * from user where userid=1 
union  
select * from user where age = 10
Positive example:
select * from user where userid=1 
union all  
select * from user where age = 10
Reason:
If you use union, regardless of whether the search results are repeated, it will try to merge and sort them before outputting the final results.
If the search results are known to have no duplicate records, use union all instead of the union, which will improve efficiency.

14. Use numeric fields as much as possible. If the fields only contain numeric information, try not to design them as a character type.
Counter-example:
'king_id' varchar(20) NOT NULL;
Positive example:
'king_id' int(11) NOT NULL;
Reason: Compared with numeric fields, character types will reduce the performance of query and connection, and will increase storage overhead.

15. Use varchar/nvarchar instead of char/nchar whenever possible
Counter-example:
'deptName' char(100) DEFAULT NULL
Positive example:
'deptName' varchar(100) DEFAULT NULL
Reason:
First, because the storage space of the variable-length field is small, hence, the storage space can be saved.
Secondly, for queries, searching in a relatively small field is more efficient.

16. Use explain to analyze your SQL plan
When writing SQL in daily development, try to develop a habit. Use explain to analyze the SQL you wrote, especially the index.
explain select * from user where userid = 10086 or age =18;

Thursday, June 11, 2020

XSD - enumeration -Restrictions on a Set of Values of a element

Restrictions on a Set of Values
To limit the content of an XML element to a set of acceptable values, we would use the enumeration constraint.

The example below defines an element called "Color" with a restriction. The only acceptable values are: green, red, blue:

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
            targetNamespace="http://www.example.org" elementFormDefault="qualified">
  <xsd:element name="SomeElement">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Color" type="xsd:colorT"/>
        <xsd:element name="code" type="xsd:string"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
      <xsd:simpleType name="colorT">
      <xsd:restriction base="xsd:string">
        <xsd:enumeration value="green"/>
        <xsd:enumeration value="red"/>
        <xsd:enumeration value="blue"/>
      </xsd:restriction>
    </xsd:simpleType>
</xsd:schema>

XSD - Define a complex type using type attribute or directly by naming

Complex Element is an XML element which can contain other elements and/or attributes. We can create a complex element in two ways −
  • Define a complex type and then create an element using the type attribute
  • Define a complex type directly by naming

Define a Complex Type and then create an element using type attribute.

Here JobHistory element is referring to  JobType complex type using type attribute

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.poc.org" xmlns:ns0="http://www.poc.org" targetNamespace="http://www.poc.org"
            elementFormDefault="qualified">
  <xsd:element name="HREmployeeData">   
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="First" type="xsd:string"/>
        <xsd:element name="Last" type="xsd:string"/>
        <xsd:element name="Phone" type="xsd:string"/>
        <xsd:element name="Income" type="xsd:double"/>
        <xsd:element name="Status" type="xsd:string"/>
        <xsd:element name="JobHistory" type="ns0:JobType"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
    <xsd:complexType name="JobType">
      <xsd:sequence>
        <xsd:element name="JobTitle" type="xsd:string"/>
        <xsd:element name="Salary" type="xsd:double"/>
      </xsd:sequence>
    </xsd:complexType>
</xsd:schema>

Define a complex type directly by naming

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
            targetNamespace="http://www.example.org" elementFormDefault="qualified">
  <xsd:element name="HREmployeeData">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="First" type="xsd:string"/>
        <xsd:element name="Last" type="xsd:string"/>
        <xsd:element name="Phone" type="xsd:string"/>
        <xsd:element name="Income" type="xsd:double"/>
        <xsd:element name="Status" type="xsd:string"/>
        <xsd:element name="JobHistory">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="JobTitle" type="xsd:string"/>
              <xsd:element name="Salary" type="xsd:double"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>


Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...