Monthly Archives: June 2008

ApEx: ORA-20001: Sync Error

Posted on by

While trying to publish an translated application I keep running into the next error:

ORA-20001: Sync error: WWV_FLOW_PAGE_PLUGS.PLUG_QUERY_NO_DATA_FOUND …

After trying some more I got:

WWV_FLOW_PAGE_PLUGS.CSV_OUTPUT_LINK_TEXT
WWV_FLOW_PAGE_PLUGS.PLUG_FOOTER
WWV_FLOW_PAGE_PLUGS.PRN_PAGE_HEADER

After trying 25 times it was finally published but I was not planning to try that often each time. If you are running ApEx version 3.0.1.00.07 or 3.0.1.00.08 then this is actually a database bug but you can find a patch for a workaround on Metalink, Patch nr: 6456920.

Description ORA-22922 WHEN PUBLISHING A TRANSLATED APPLICATION
Product Oracle Application Express (formerly HTML DB)
Release 10.2.0.3

After running this patch I could publish my translated application without any problem.

ApEx Xliff ORA-31011: XML parsing failed

Posted on by

Recently I came across this error while trying to publish a translated application. The application needed to be translated in the polish language, this was the first time that I came across a language with special signs in it.

I quickly found out that I cannot just save the xliff file on my windows machine, I need to choose the correct type of file otherwise the special characters will be lost. So the translated language is polish and my target database has the WE8MSWIN1252 character set.

I saved the xliff file with type Unicode file using notepad. When I tried publishing the next error appeared:

ORA-31011: XML parsing failed. ORA-19202: Error occurred in XML processing ( LPX-00216: Invalid character 0 (0×0). Error at line 1 ).

Not really knowing what the problem could be I tried saving it as UTF8 type file with notepad. If I tried to publish the new file I got a different error:

ORA-31011: XML parsing failed. ORA-19202: Error occurred in XML processing( LPX-00210: ‘<' expected instead of '¿'. Error at line 1 ).

After some research I found out that notepad(Microsoft) adds a BOM (Byte Order Mark), these are 3 bytes at the start of the document that indicate the character encoding of the document. So I tried a non-Microsoft tool like ‘UltraEdit’ and saved my document with type UTF-8 – NO BOM. Now I could perfectly upload and publish my application.

Migrate from Hibernate to Oracle Toplink (EclipseLink)

Posted on by

Douglas Clark, Director of Product Management, has recently participated in a discussion with Oracle ACE’s regarding the key differentiators of Oracle Toplink versus other ORM Mappings.

This is a very interesting discussion for key decision makers who are thinking about ORM Solutions for their existing or new JEE Applications.

The key differentiators according to Doug Clark:

  1. Performance and scalability: Our out of the box caching architecture is allows us to minimize object creation and share instances. The caching offers out of the box support for single node and clustered deployments. We have been involved in many internal and external benchmarking efforts that maintain our confidence that we have the best performing and scaling ORM solution available.
  2. Support for leading relation databases: We continue to support all leading relational databases with extensions specific to each. We are also the best ORM solution for the Oracle database. We continue to enhance this support in 11gR1 and EclipseLink.
  3. A comprehensive persistence solution: While we offer industry leading object-relational support we have also leveraged our core mapping functionality to deliver object-XML (JAXB), Service Data Object (SDO), as well as non-relational (EIS via JCA) and Database Web Services. Depending on your requirements you can use one or more of the persistence services based on the same core persistence engine.
  4. Donated to Open Source Community: Full functionality of Oracle TopLink now available in open source EclipseLink project. OracleAS/SOA customers will continue to leverage the functionality of TopLink now developed in open source. Those looking for an open source solution can now choose to use EclipseLink and gain the benefits of our long commercial usage and our ongoing development efforts.
  5. JPA Support: As the JPA 1.0 specification co-leads Oracle and the TopLink/EclipseLink team has been focussed on delivering a JPA compliant solution with supporting integration with JDeveloper, ADF, Spring, and the Elcipse IDE (Dali project). We have delivered the JPA 1.0 reference implementation and with EclipseLink will now deliver the JPA 2.0 reference implementation. We are focussed on standards based development while still offering many advanced capabilities as well.While Hibernate may have the current lead in developer mind-share we are focussed on continuing to deliver our world-class functionality to the entire Java community.

From Forms to SOA

Posted on by

Today Grant Ronald was in Europe for the Technology Seminars organised by Oracle, of course the subject was Forms.

For more information regarding forms, soa and the road ahead you should bookmark his blog.

The title of the seminar: ‘From Forms to SOA’ and we were invited to present our experiences with SOA, Forms and the combination of both worlds as well.

It was great to meet up with Grant, because I’m more a SOA-person as a Forms-person and it was the first time to meet up with him.

First Grant gave a presentation regarding:

  • Oracle Forms Strategy: From Client Server to SOA
  • SOA, BPEL and Web Services: Calling Services from Oracle Forms
  • Building Services in JDeveloper

It was great to hear that Grant participates in as well product management for Forms as Jdeveloper to enable the same productivity and declarative approach as forms developers are used to working with in Forms application.

In the 1st part Grant explained how both worlds, SOA and Forms, colide and the key phrase here = upgrade and integrate. First you need to move your client/server application to the web, using web forms. After the upgrade you can then integrate the needed services such as web services, bpel, esb, … into your existing web forms application. In other words there’s no need to have a big bang approach and throw away all the investments you’ve made, but upgrade and integrate. As you can read and see as well, Forms 11g will hold new functionalities to enable integration even more, such as AQ to enable asynchronous messages for example, javascript capabilities, …

In the 2nd part Grant showed how you can integrate such a Forms application with an existing bpel process, which actually is a web service, and with existing web services. The key phrase here = you don’t need to write any line of java code to enable this integration.

How to accomplish this without writing any line of java code, use Jdeveloper and the Java Importer of Forms:

  • Create a web service proxy client for the existing bpel process and web services via the ‘web service proxy client’-wizard in Jdeveloper. You just need to copy/paste the wsdl-location of the bpel process and/or external webservice and Jdeveloper will do the rest.
  • Deploy this web service proxy client to your application server, were your web forms are hosted as well
  • Generate pl/sql wrapper classes via the ‘Java Importer’ in your Forms Builder
  • And the Forms Developer can invoke web services using pl/sql functionality without any knowledge of the underlying technology

In the 3d part Grant gave a demo regarding the 4GL-experience a Forms Developer will have when working with Jdeveloper to build a ‘Forms-like’ application, in other words data-entry, – retrieval pages.

To have the same look-and-feel, capabilities, userfriendliness and declarative approach as in Forms Builder the chosen technologies for MVC are ADF Faces and BC4J, these are the 4GL-like technologies to choose when coming from a Forms-background.

Using these technologies Grant explained how to define business logic using validation in BC4J, how to define page flows in the faces config file and how to define the different screens in a declarative manner. Using BC4J Grant also explained how Entity Objects, View Objects and the Application Module relate to components known to a Forms Developer.

After his introduction different Oracle partners presented their cases regarding Forms and SOA and in that matter we’ve presented a SOA and Forms integration case from the Netherlands.

Most important part of all: we had an audience of more than 90 people, it was great to see such an amount of people and interest in these technologies.

Best of breed applications are slowly moving towards ‘best of all worlds’, there are ‘no’ limits anymore.

Calling Webservices from pl/sql

Posted on by

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;