Oracle 4 Newbies: thé Powerday

After our ‘Oracle for Newbies’ seminar, we invited all attendees for a Powerday. The participants would get the opportunity to try out everything we had shown in the seminar. Last Monday we started our Powerday with 11 curious attendees.

The ‘half a day’ – Powerday consisted of 3 parts: 1) the installation of Oracle Express Edition and an upgrade to the last Oracle Application Express version, 2) building a wizard driven application and 3) building and modifying manually some apex functionality.

For the installation part, we asked every attendee to bring his own portable. We planned to have the software successfully installed on every pc in one hour. And yes, they all did it. After 1 hour and 10 minutes, everybody had an Oracle database with Apex 3.1 up and running!

For the second part, we gave a demo on how to create a wizard-based application. Now it was up to them! We provided some excel sheets with the data. Starting from these excel sheets, thanks to the ease of Oracle Application Express, they managed to create the Oracle data model, fill the tables with the excel data and create a web application to do all possible data manipulation. And again… One hour later, they all had their first own Apex application up and running.

In the last part of our Powerday, we did some more advanced and ‘not so wizard-driven’ stuff like modifying or adding some functionality in the application, implementing blob functionality (upload and download of images), implementing security (authorization) and lots of other nice things. Not only ‘we’ did all this, at the end of the day, they all had these features implemented in their application.

All participants were enthusiast, and hopefully we convinced them to start using Oracle and Apex.

Hibernate Transaction Choices

I take part in a project using Spring MVC and Hibernate technologies. During this project, we had to make some transaction choices. The transaction management could be handled by the HibernateTransactionManager. With the use of annotations, the manager could recognize what kind of transaction should be opened.

A transaction could run in different kind of modes. The default flushing mode is auto. In this case the database session is sometimes flushed before query execution in order to ensure that queries never return stale state. The side effect of unexpected flushes caused us some problems in the execution of DML statements within business logic methods.

What if you have to update some relevant objects before the edited object may be saved to the database? Or you want to do some validation on the unsaved object and you need other persistent objects to do this validation. We consider an object being persistent when the state of the object is the same as its state in the database.

On most forums, hibernate users propose to override the auto flush mode by the manual flush mode to prevent unwanted flushes. A database session in manual flushmode is only flushed when Session.flush() is explicitly called by the application. My team members and I do not favor this kind of solution. It’s like overruling the normal transaction behavior.

We prefer to split up the transaction methods. Like I said in Spring MVC it is possible to indicate which kind of transaction should be opened by the HibernateTransationManager. This is done with annotations set in classes and class methods. If the method only retrieves data from the database, the transaction should not be flushed and will be a read-only method.

We did some trials on our application. Within our model, managers are containing business logic interacting with the Dao’s setting up statements to save and retrieve data information. An important decision is at which level a new transaction should be created. As the Dao’s setting up statements, it might be a good solution to open and close transactions as Dao methods are called.

For example:
We try to update the salary of an employee in a company. When the employee’s salary is more than his superior, the superior’s salary is increased with the same value.

public EmployeeManager{
private EmployeeDAO employeeDAO;

public Employee update(EmployeeDto employeeDto){

// retrieve the object as currently known in the DB
Employee employee = employeeDAO.getById(employeeDto.getId());
Employee superior = employee.getSuperior();

// compare the salary update
if(employeeDto.getSalary() > superior.getSalary()){
int difference = employeeDto.getSalary() -employee.getSalary();

// increase the superior’s salary and update
superior.setSalary(superior.getSalary() + difference);
employeeDAO.update(superior);
}

// update the employee
return employeeDAO.merge(employeeDto);
}
}

The business logic to update the employee’s salary is maintained by an EmployeeManager.
As entities need to be saved, updated or deleted, this is done through different Dao methods.
In this case three transactions are created.

1) employeeDAO.getById(employeeDto.getId()) – type: read-only
2) employeeDAO.update(superior) – type: normal (auto flush)
3) employeeDAO.merge(employeeDto) – type: normal (auto flush)

What if something went wrong in the last transaction method? Only the last transaction will be rolled back. The superior’s salary could be increased when the employee’s salary is still the same. If the user retries to increase the employee’s salary, the superior’s salary will receive twice the wage storage.

For information:
At the end of the transaction, a merge is done instead of an update to prevent a Hibernate TransientObjectException. The transient object – employeeDto – could not be updated when it is not yet persistent with the database. With the use of merge; the changes between the transient and the persistent object are merged to the database.

It’s clear this is not the right transaction choice. We moved the transaction management to the manager level. A better choice, cause now the whole manager method will end in one transaction and also the child methods executed within take over the same transaction.

Another example:
The wage storage rules remain but the superior will be updated by cascade. This means no update action should be called, the update is done automatically with the merge of the employee.

We also update the last name of the employee and check if the name is not already used by another employee.

public EmployeeManager {
private EmployeeDAO employeeDAO;

public Employee update(EmployeeDto employeeDto) throws EmployeeException {

// retrieve the object as currently known in the DB
Employee employee = employeeDAO.getById(employeeDto.getId());
Employee superior = employee.getSuperior();

// compare the salary update
if(employeeDto.getSalary() > superior.getSalary()){
int difference = employeeDto.getSalary() -employee.getSalary();

// increase the superior’s salary and update
superior.setSalary(superior.getSalary() + difference);
}

if(!employeeDto.getLastName().equals(employee.getLastName()) &&
lastNameIsUsedByOtherEmployees(employeeDto.getLastName()){
throw new EmployeeException(“Last name is already used by another employee”);
}

// update the employee
return employeeDAO.merge(employeeDto);
}

private Boolean lastNameIsUsedByOtherEmployees(String lastName){
List employees = employeeDAO.getEmployeesByLastName(lastName);

if(employees.size() > 0){
return true;
}

return false;
}
}

The whole method results in one transaction. Data is read from the database and at the end of the method the employee is updated. Propose the new salary of the employee is higher than his superior and also the person’s name is changed. While the persistent superior object is changed, employees are fetched from the database via the employee’s last name validation. That moment the object state is synchronized with the database and the superior is updated directly before the employee might have been updated. This is caused by the auto flush mode the transaction is running on.

The transaction is flushed whenever changes to a persistent object are made. As soon as a new read is done, Hibernate updates the object state of the employee object. We didn’t aim to flush (commit) at this point of our transaction. We still have to do validation and maybe an exception will to be thrown.

Another solution is to split manager methods into read-only and not read-only transactions. Validation of objects is done with read-only methods and the real database changes are done in another method.

This could look like this:

public class EmployeeManager{
private EmployeeDAO employeeDAO;

public void validate (EmployeeDto employeeDto) throws EmployeeException {

// retrieve the object as currently known in the DB
Employee employee = employeeDAO.getById(employeeDto.getId());

if(!employeeDto.getLastName().equals(employee.getLastName()) &&
lastNameIsUsedByOtherEmployees(employeeDto.getLastName()){
throw new EmployeeException(“Last name is already used by another employee”);
}

return employee;
}

public Employee update(EmployeeDto employeeDto) {
// retrieve the object as currently known in the DB
Employee employee = employeeDAO.getById(employeeDto.getId());
Employee superior = employee.getSuperior();

// compare the salary update
if(employeeDto.getSalary() > superior.getSalary()){
int difference = employeeDto.getSalary() -employee.getSalary();

// increase the superior’s salary and update
superior.setSalary(superior.getSalary() + difference);
}

// update the employee
return employeeDAO.merge(employeeDto);
}
}

public class EmployeeController {
private EmployeeManager employeeManager;

public ModelAndView onSubmit(HttpServletRequest request, HttpServletResponse response, Object command, BindException errors){
EmployeeDto employeeDto = (EmployeeDto) command;

try {
employeeManager.validate (employeeDto);
employeeManager.update(employeeDto);

} catch (EmployeeException e){
errors.rejectValue(“lastName”,”lastName.alreadyUsed”);
return showForm(request, errors, this.getFormView());
}

return new ModelAndView(……);
}

}

The EmployeeController implements two method calls to the manager. The first method is used for validation and second for updating the database.

Do you have other experiences in taking Hibernate transaction choices or do you know a better solution to solve this issue? Please let me know…

Oracle for Newbies, Power Day

In December you have probably read on this blog about our “Oracle for Newbies” seminar. As posted there we wanted to convince newbies that Oracle is not expensive, nor difficult. With free Oracle products (Oracle XE database, Application Express and SQL Developer) we showed how fast you can set up flexible database structures and professional web applications.

As previously announced there will be a follow-up on this seminar: on Monday, February 9th, iAdvise will organize a Power Day. It will be a very practical and interactive day on which participants will get the opportunity to try out everything. We will guide the participants when they are installing the Oracle software and we will help them while they are creating and deploying their first applications. After this session the attendees will be able to develop their own powerful and professional web applications.

Our objective for this Power Day is that newbies can learn Oracle on a pleasant way! We will keep you informed!

For more information about our Power Day, please contact us at info@iadvise.be.

Oracle for Newbies

On Wednesday iAdvise gave an Oracle seminar at the headquarters of Oracle Belgium in Brussels. That’s not THE big news of course, but for this seminar our audience was different. This time we focused the “newbies”, people who‘ve never or hardly heard about Oracle.


With this seminar we wanted to debunk the myths that:

  • Oracle is expensive
  • Oracle is only for the “big” companies
  • Oracle is difficult to install and has steap lurning curve

With life installations and life demo’s we convinced our public. Therefore we only used FREE Oracle products, like Oracle XE Database, Oracle Application Express (Apex) and SQL Developer. We installed the Oracle software life during the seminar. We created a database for a fleet of cars and developed a complete web application in less than no time to manage cars, traffic offences and car accidents. We showed the most important and powerful functionalities of Apex and we even included security.

During the seminar we showed:

  • How easily and quickly you can install the Oracle XE Database and Apex
  • How fast and flexible you can set up a database structure with SQL Developer or Apex
  • How you can create a powerful and professional web application with Apex in no time
  • How Apex is based on a extremely simple architecture
  • How fast you can deploy the application using SQL Developer or Apex
  • How dirt-cheap all these topics are thanks to the free Oracle products

Oracle users already knew this, but at the end of the seminar the “newbies” were also convinced: Oracle is fun !


With the motto “When you hear you forget, when you see you remember, when you do you understand” iAdvise will organize a “Power Day”. During that day we will guide “newbies” while they try this out by theirself.
Are you interested in the “Oracle for Newbies” presentation slides or would you like to have more information about our Power Day? Please contact us at info@iadvise.be.

Apex Unreachable after Database Upgrade from 9.2 to 10.2

Recently we were performing a database upgrade from release 9.2.0.7 to 10.2.0.4. After the successful upgrade we were issuing a problem to reach APEX. We’ve tried to browse to the APEX URL ( :/pls/apex”>http://:/pls/apex ) and the only thing we were receiving was a blank page. The strange thing is that the APEX URL has been rewriten to :/pls/apex/apex”>http://:/pls/apex/apex
There were no errors in the error.log of the Apache and only HTTP 200 entries were written to the access.log of the Apache.

When trying to reach an existing APEX application ::/pls/apex/f?p=120″>http://::/pls/apex/f?p=120 it wasn’t possible to reach the application but the error.log did write some error entries in the error.log of the Apache.

[Wed Sep 17 06:40:19 2008] [error] [client 130.130.203.210] [ecid: 1221626419:193.53.194.211:5604:7448:145,0] mod_plsql: /pls/apex/f HTTP-404 ORA-06550: line 1, column 35:\nPLS-00201: identifier ‘SYS.DBMS_CRYPTO’ must be declared\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\nORA-06512: at “FLOWS_030100.F”, line 279\nORA-06512: at line 31\n

The problem we were facing after the upgrade was a missing grant on the SYS.DBMS_CRYPTO to FLOWS_030100. DBMS_CRYPTO came into the picture from 10G. In previous releases such as 9i the DBMS_OBFUSCATION package is used. The grants to those packages were giving at the APEX install. Upgrading the database after APEX install needs a manually grant of this DBMS_CRYPTO package.

Maarten Fuchs

Manually Editing Translations within Apex without Exporting and Importing XLIFF File

Everyone already knows that translating an application built with Apex involves the following steps:

  1. Map your primary language application to a translated application
  2. Seed and export the translation text of your application into an translation file (XLIFF file)
  3. Translate text identified in translation file
  4. Apply your translation file and publish

Joel Kallman referred to a less known feature in this context during his presentation last month – “Go Global with Oracle Application Express!”- at the ODTUG Conference in New Orleans. Since Apex version 2.2 it is possible to perform your translations even more rapidly, without the need to export and import the XLIFF file again.

Via Apex you can manually edit your translations within the repository. But, you still have to follow the same globalization process: mapping, seeding (without exporting the XLIFF file), translating and publishing (without applying the XLIFF file first).

So, to manually edit a translatable text, navigate to “Shared Components” > “Globalization” > “Translate Application” and follow these steps:

  1. Map your primary language application to a translated application.
    This 1st step is unchanged.
  2. Seed the translatable text (without exporting the XLIFF file).
    Click step 2. Choose your “Language Mapping” and press “Seed Translatable Text”. A message like “Translatable application 143 text seed complete for fr.” appears. Seeding is succeeded now. You may end this step because we don’t want to export an XLIFF file.
  3. Manually edit translation.
    From the “Translation Utilities” list (right on your page), choose “Manually Edit Translations”. The “Translatable Text” page appears. Within the search bar you can enter some search criteria.


    To edit translatable text, click the “Edit” icon; translate your text and press “Apply changes”.

  4. Publish the application (without applying the XLIFF file first).
    From the “Navigate” list (right on your page), choose “Publish Application”. Select the correct language mapping in “Create Application” and press “Publish Application”.

Finished!

This is an alternative and quick manner to achieve translations after small application changes due to bug fixing or other small modifications.

Last remark : Suppose you do have an application to translate using the XLIFF file, then you can edit your XLIFF file either by using a simple text-editor, MS-Word or an XML Editor (XML Spy or JDeveloper)… To avoid the repetitive work with these editors you can always use our own free utility, the XLIFF Translator. Within this translator we provide a kind of a dictionary, so it will be possible to automate a part of the translation process for words/sentences that are repeatedly used. For more information read the blogs about the XLIFF Translator from January 2007 and February 2007.

Our ODTUG presentation about the QA-tool

Like Nathalie Roman announced in the previous blog I had the honour to give my first presentation abroad last Monday at the Big Easy.



It was Jan Huyzentruyt’s idea to work out an Apex QA-tool for our company. Because we wanted to share our idea with the other Apex-adepts we submitted our abstract “Use the Power of APEX Dictionary Views to Increase the Quality of Your APEX Applications”.


Nathalie was our ambassador. She welcomed the audience and she introduced us. First I explained our Apex Development Approach: the broader context in which our quality system originated. With different steps and life demonstrations in between I then showed how the QA-tool evolved from basic to a customizable QA system.



The audience was very attentive and enthusiastic. More than 50 people attended our session. Everything went well until Murphy dropped in. Because of a broken socket we had problems with the power supply.

At the end of the presentation and also afterwards we got a lot of positive feedback: nice presentation, great idea, you should bring it out, … ! Well, we will make it available as soon as possible.



It really was a great experience to present on ODTUG!

For those who attended our session, we hope you enjoyed the presentation!
People who weren’t at ODTUG can download the paper and the presentation from our company website www.iadvise.be.

CU at OOW!

Calling Webservices from pl/sql

For a client of us we had to call a webservice from a database, and the received information that we received from this call was needed in another procedure.

It was the first time for me that I had to do this and I believe that not so many people now of this functionality in the database and that’s why it is obviously a good idea to post this on our blog.

It is even possible to let the database be a webservice provider, but in this case the webservice already existed and should simply be called by the database.

What was the objective: we wanted to call the webservice from the database, get the resulting XML file and analyze some of the content of the returned XML file and then do some actions with the information that the webservice provided us.

Therefore I wrote 3 procedures in a package:
• fnc$_get_xml
• fnc$_handle_xml
• prc$_ws_call

As you will see when you look at the code it is possible to make this much more dynamically but for this case we only had to call only one specific webservice, as usual you can make it very complex but for the blog I made the procedures and functions as simple as possible.

The first function that I will explain is the prc$_ws_call
This procedure will contact the function fnc$_get_xml that will get the xml file. After this function the fnc$_handle_xml will be called to retrieve specific information out of the XML file.
When this is done the received values will be printed


PROCEDURE prc$_ws_call
IS
v_xml VARCHAR2(32767);
v_type cab_base_adres.type%TYPE;
r_receive r_info;
BEGIN
v_xml := fnc$_get_xml(p_search => ‘search value’); –call to webservice
r_receive := fnc$_handle_xml(v_xml); –analization of xml content
dbms_output.put_line(‘value nr1=’||r_receive.value1) ;
dbms_output.put_line(‘value nr2=’||r_receive.value2) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
END;

fnc$_get_xml
This function will receive a parameter(search) this parameter will be included in the soap call(called v_soap_request).
Ones that I have build my soap envelope, I have to create a httpRequest, this is the variable v_httpRequest which is of the type utl_http.req.
For this variable I have to set some parameters like the webservice url that I will call, the content-type (plain text in this case), the content_length of the soap envelope and the specification that this is a soapaction. This is the preparation for the call to the webservice.

Next thing to do is to write this data to the body of the http request, this is done with the utl_http.write_text where we give in the http_request and the soap_request.
Now we want to receive the response from our soap call.
This will be created with the utl_http.get_response which is of the utl_http.resp type.

Next thing we want, is to have this file in a readable form. Therefore we use the utl_http.read_text, that will translate the response in a readable variable.

FUNCTION fnc$_get_xml(p_search VARCHAR2)
RETURN VARCHAR2
IS
v_soapRequest VARCHAR2(32000);
v_soapResponse VARCHAR2(32767);
v_httpRequest utl_http.req;
v_httpResponse utl_http.resp;
BEGIN
v_soapRequest :=
‘<?xml version=”1.0″ encoding=”UTF-8″ standalone=”no”?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV=”http://schemas.xmlsoap.org/soap/envelope/&#8221; xmlns:apachesoap=”http://xml.apache.org/xml-soap&#8221; xmlns:impl=”<the webservice>” xmlns:intf=”<the webservice>” xmlns:soapenc=”http://schemas.xmlsoap.org/soap/encoding/&#8221; xmlns:tns1=”” xmlns:wsdl=”http://schemas.xmlsoap.org/wsdl/&#8221; xmlns:wsdlsoap=”http://schemas.xmlsoap.org/wsdl/soap/&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; >
<SOAP-ENV:Body>
<mns: <webservice name>xmlns:mns=”<services link>” SOAP-ENV:encodingStyle=”http://schemas.xmlsoap.org/soap/encoding/”&gt;
<inputAddress xsi:type=”tns1: <input parameter name>”>
<input search xsi:type=”xsd:string”>’||p_search||’</search>
</mns: <webservice name> >
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>’;

v_httpRequest:= utl_http.begin_request
(”
,’POST’
,’HTTP/1.1′);

utl_http.set_header(v_httpRequest, ‘Content-Type’, ‘text/xml’);
utl_http.set_header(v_httpRequest, ‘Content-Length’, length(v_soapRequest));
utl_http.set_header(v_httpRequest, ‘SOAPAction’, ”);

utl_http.write_text(v_httpRequest, v_soapRequest);
v_httpResponse:= utl_http.get_response(v_httpRequest);
utl_http.read_text(v_httpResponse, v_soapResponse);
utl_http.end_response(v_httpResponse);
RETURN v_soapResponse;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
dbms_output.put_line(‘Error in fnc$_get_xml’) ;
utl_http.end_response(v_httpResponse);
raise;
RETURN NULL;
END;

Great, we have now received the xml file that contains the result of our request. Now we want to retrieve the data we need, out of this XML file.
Therefore I will call the fnc$_handle_xml function with the received XM file as parameter.

I will have to handle the content of the xml file. To make this easier I am going to put the content of the XML file into an XMLType by using the XMLType.createXML function. Now the parameter resp contains the XML file. But I am only interested in a certain part of the XML file. By using the ‘extract’ function I am able to get a certain part out of the hierarchical structure of the XML file.

Next thing that I want to receive is the information of certain parts of this resp variable. I will put his in the resp1 variable which make it possible to always use the resp file for the next value I want to retrieve.

In this example I get 2 values out of the XML file. And I will return this back to the calling procedure.

FUNCTION fnc$_handle_xml(p_xml VARCHAR2)
RETURN r_info
IS
resp XMLType;
resp1 XMLType;
r_result r_info;
BEGIN
resp:= XMLType.createXML(p_xml);
resp:= resp.extract(‘/soap:Envelope/soap:Body/child::node()’
, ‘xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”&#8216;
);

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
);
IF resp1 IS NOT NULL THEN
r_result.value1 := resp1.getStringVal();
END IF;

resp1:= resp.extract(‘/multiRef[2]/<xml level>/text()’
, ‘xmlns:ns2=”<beans url>”‘
);
IF resp1 IS NOT NULL THEN
r_result.value2 := resp1.getStringVal();
END IF;

RETURN r_result;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm) ;
RETURN r_result;
END;