Feed aggregator

I have written a few entries about Microgen DBClarity Developer, the new graphical SQL tool we've just launched and would love to hear what you think. Have you taken a look yet? Have you read some of the material and said, "I already have a SQL tool" and gone on to read another blog? What does drag-and-drop SQL code conjure up in your head?

Just today I was looking at this code:

I used another SQL tool too - I used Oracle SQL Developer to run my scripts and to populate and verify the data in the tables - why change old habits when they work well?! The fun thing is that I can use DBClarity Developer to produce that SQL by creating this diagram, using easy drag-and-drop...


When last did you write a more complex piece of SQL and scratch your head just a little to start with? How often do you execute the same piece of code? The graphical aspect of DBClarity Developer means that you don't need to worry about the code, the product does that. You build up the graphical flow and we create the code.  The good thing is that you don't need to decide on the database at the outset; you can use this same graphical representation to produce the SQL for Oracle, SQL Server or Teradata.  These graphical SQL rules are part of your project, so you can reuse them.

Visit the site today.  DBClarity Developer is production, why not download and try it out? 

We performed a disaster recovery (DR) test at our DR site in October 2011 which involved recovering Oracle database VMs with storage on a  SAN.  Once the database VM was recovered from a NetApp volume snapshot, it was started and we discovered Oracle went through its usual crash recovery and the database was running as […]

The last few weeks we had some strange behaviour on our Oracle Fusion Middleware stack. This was first related to two different SOA 11g production clusters. One 2-node cluster and one 4-node cluster. Both environments run into a Java out-of-memory.

While he have running this production environments for a long time, we thought we have tuned this very well; on database as on application server. As I spoke earlier on Java Garbage collection on http://orasoa.blogspot.com/2011/09/soa-11g-improved-memory-and-garbage.html, and on tuning the SOA Suite 11g; http://orasoa.blogspot.com/2011/08/tuning-soa-11g-in-nutshell.html.

We first thought, it had to do the the applications we made; Composites, Custom Java Classes, Services. Is something changed in the meantime? We also thought it had to do with the amount of data we are processing, is there more data processed than normal? Something changed in the database? The network? The physical storage? All of them were not changed, looking in the program code; we did not find anything special.

We tried to analyse the a heap dump, digged into log files, Oracle Support Bug Database and many-many-other-things. I stared a long time to this

#
# A fatal error has been detected by the Java Runtime Environment:
#
# java.lang.OutOfMemoryError: requested 32756 bytes for ChunkPool::allocate. Out of swap space?
#

The I was triggered by a bug in the Oracle/Sun Java; http://bugs.sun.com/view_bug.do?bug_id=6973402 . For some reason I downloaded the latest Java 1.6 SDK and applied this on one managed server on one the SOA 11g Suite.

The result was amazing!

• Less CPU
• Less Heap usage
• Normal garbage collections

We applied this on our SOA 11g Suite clusters and had no issues.

We see this difference also on de Oracle Service Bus (!).  My advise, roll out Java 1.6.0 build 30 (or later) on your existing production environments, SOA Suite, WebCenter, ServiceBus, etc!


Here are the results of our instance running without Java 1.6.0_b30 and at the bottom with Java 1.6.0_b30. And see the incredible difference. I did not show the CPU graphs with the garbage collections, but those are also different.

I'm sure you've seen the flood of announcements from the other Fusion Middleware products about the 11.1.1.6 release. We got in on the fun too.You can download it here. And for a fresh install - you can start directly from 11.1.1.6. For the most part this is just a bug fix release for us.But there are a couple of enhancements I would like to share.Oracle Virtual DirectoryThe biggest enhancement I would highlight is that we have dramatically simplified configuring OVD for Enterprise User Security (EUS). EUS has been something that has always worked but required to execute lots of individual steps. We now have this setup as a wizard and OVD's own Local Store Adapter holds most of the meta-data. So less work on the enterprise LDAP and fewer steps. It should mean initial EUS configuration by most people can now be done in less than a day. Directory Integration PlatformDIP has been part of Oracle for over a decade but until 11.1.1.6 it required OID. Now it can be used with DSEE or OUD as its metadata store.This now means that if you want to deploy DSEE or OUD but need to synchronize groups & users from AD - you can do it without needing any type of custom code or bringing in a full provisioning product.

I'm sure you've seen the flood of announcements from the other Fusion Middleware products about the 11.1.1.6 release. We got in on the fun too. You can download it here. And for a fresh install - you can start directly from 11.1.1.6. For the most part this is just a bug fix release for us. But there are a couple of enhancements I would like to share. Oracle Virtual Directory The biggest enhancement I would highlight is that we have dramatically simplified configuring OVD for Enterprise User Security (EUS). EUS has been something that has always worked but required to execute lots of individual steps. We now have this setup as a wizard and OVD's own Local Store Adapter holds most of the meta-data. So less work on the enterprise LDAP and fewer steps. It should mean initial EUS configuration by most people can now be done in less than a day. Directory Integration Platform DIP has been part of Oracle for over a decade but until 11.1.1.6 it required OID. Now it can be used with DSEE or OUD as its metadata store. This now means that if you want to deploy DSEE or OUD but need to synchronize groups & users from AD - you can do it without needing any type of custom code or bringing in a full provisioning product.

Looks like it's time to follow up on a Blog posting I wrote in 2008 about including dynamic images in PDF reports. After being called out on that good old 32k limit in a recent Blog posting by Roel Hartman, and reading through the recent OTN Forum posts and tweets on that topic, it would be rude not to respond ;-)

While I did state that we were looking to lift this 32k limit, this has not yet made it in, i.e. the limit is still in place. However in my posting I was also saying that if the XML data is generated by some other way, and the PDF rendering is done using the print API, then the use of larger images would be possible. And that is certainly the case, so let's take a look at how this could be done.

The key piece is our Print API (apex_util.download_print_document and apex_util.get_print_document), with this API you can generate PDF and other documents through a simple PL/SQL API call. This API is taking care of all the communication with BI Publisher or FOP for you. The apex_util.get_print_document API can be called to generate and retrieve the print document as a BLOB in the database for further processing, like storing the document in tables, etc. The apex_util.download_print_document API can be called in an APEX page process to generate and download the print document straight to your client. Both APIs have three different signatures, they allow for programmatically downloading report queries while dynamically associating stored report layouts at runtime, downloading report queries with custom templates stored in your own tables, and generating PDF based on your own custom XML using your own custom templates.

This last scenario is what we want to use for our dynamic images sample, i.e. we're going to generate the report data in XML format ourselves, thus getting around that 32k limit and store the report data in a CLOB. We're then also going to store our report layouts in our own tables, and query them up dynamically at runtime. The API for this looks as follows (for further details, reference the Oracle Application Express API Reference):

-- -----------------------------------------------------------------------------------------------
procedure download_print_document (
--
-- This procedure initiates the download of a print document using XML based report data and RTF or XSL-FO based report layout.
--
-- Arguments:
--   p_file_name            Defines the filename of the print document
--   p_content_disposition: Specifies whether to download the print document or display inline ("attachment", "inline")
--   p_report_data:         XML based report data
--   p_report_layout:       Report layout in XSL-FO or RTF format
--   p_report_layout_type:  Defines the report layout type, that is "xsl-fo" or "rtf"
--   p_document_format:     Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"
--   p_print_server:        URL of of the print server. If not specified, the print server will be derived from preferences
--                          example: http://myserver.mydomain.com:8888/xmlpserver/convert
--
 p_file_name           in varchar,
 p_content_disposition in varchar  default 'attachment',
 p_report_data         in clob,
 p_report_layout       in clob,
 p_report_layout_type  in varchar2 default 'xsl-fo',
 p_document_format     in varchar2 default 'pdf',
 p_print_server        in varchar2 default null
);

Now of course the question is, how do we get our data into XML format, if we don't have APEX take care of that for us, and where do we get the images from and how to we include them in this API call? Getting our data into XML format is the easy part, let's say you want to generate XML data for this query:

select * from emp

You could simply call dbms_xmlgen.getxml, supply the query and retrieve the XML back as a CLOB:

select dbms_xmlgen.getxml('select * from emp') xml_data from dual

Now assuming you have your images stored in a BLOB column, you would need to convert the images into base64 encoded data in order to include them in your XML. I have a blob2clobase64 function included in my sample application, which basically does just that. It should be noted though, that ultimately our API call is going to reach out to BI Publisher via utl_http, meaning you're going to do send your XML data via http to another service, which requires certain characters to be encoded, you can find more information on this here:

http://en.wikipedia.org/wiki/Base64

APEX typically takes care of this for you, in our scenario though, you generate the XML yourself, so you need to encode the base64 data on your own. I have taken care of this in blob2clobase64 function that ships with my sample app.

create or replace function blob2clobase64 (
 p_blob in blob,
 p_escape in char default 'N'
) return clob is
 l_pos       pls_integer := 1;
 l_buffer    varchar2 (32767);
 l_res       clob;
 l_lob_len   integer := dbms_lob.getlength (p_blob);
begin
 dbms_lob.createtemporary (l_res, true);
 dbms_lob.open (l_res, dbms_lob.lob_readwrite);
 loop
  
     l_buffer := utl_raw.cast_to_varchar2 (
                   utl_encode.base64_encode (
                       dbms_lob.substr (p_blob, 48, l_pos)
                   )
               );
     if (p_escape = 'Y') then
         l_buffer := replace(l_buffer,'+','%2B');
         l_buffer := replace(l_buffer,'/','%2F');
         l_buffer := replace(l_buffer,'=','%3D');
     end if;
  
     if length (l_buffer) > 0 then
         dbms_lob.writeappend (l_res, length (l_buffer), l_buffer);
     end if;
     l_pos := l_pos + 48;

     exit when l_pos > l_lob_len;
  
 end loop;
 return l_res;

end blob2clobase64;

So in my example, using a table called eba_pdfimg_images, that includes the data and images I want to print, the XML generation would look like this:

   -- generate XML data
 for c2 in (
     select dbms_xmlgen.getxml('
         select
             id,
             file_name,
             mime_type,
             description,
             blob2clobase64(image,''Y'') image
             from eba_pdfimg_images
     ') xml_data from dual
 ) loop
     l_xml_data := c2.xml_data;
 end loop;

Once I query up my report layout, converted that to a CLOB, and generated my XML data with images as outlined above, I can simply call our API:

    apex_util.download_print_document (
     p_file_name           => 'image_demo',
     p_content_disposition => 'ATTACHMENT',
     p_report_data         => l_xml_data ,
     p_report_layout       => l_print_layout,
     p_report_layout_type  => 'rtf',
     p_document_format     => :P1_FORMAT
 );

This will generate a file called image_demo, and based on whether you choose to generate Word or PDF, you'll get ‘.rtf' or ‘.pdf' file back.

So that's it. You can generate your RTF templates with the BI Publisher Word Plug-In as you normally would. And you can take your report query SQL and wrap it into a dbms_xmlgen.getxml call, and then you'll be able to include much larger images in your PDF dynamically. Want to give it a try? Here's my update sample app (logon as demo/demo123):

Dynamic Images in PDF Reports

I uploaded three images, and two report layouts. If you're trying out your own images or templates, I ask that you please remove when done, and report back if you encounter any issues. Also, if you want to try this out locally, you can download the app (sample_pdf_with_images.sql) along with the RTF layouts and images here:

Download Sample App

Please note, this app requires BI Publisher to be configured as your print server, and the current release of APEX 4.1.1 (my sample is an APEX 4.1.1 export, but you should be able to use the same technique on APEX 4.0 and above).

No really, it has! You can find my new Oracle blog here.

For the record this older blog will stay to allow readers to continue to benefit from the existing posts.

I hope to see you on the new blog!

A short article, but Oracle Fusion Middleware patchset #5 is out! Now downloadble via:

I suspect these posts will continually try to relate functionality in Fusion Applications (FA) with my background in Oracle e-Business Suite (EBS), so apologies if your from the JD Edwards, PeopleSoft or Siebel crowd.

SAP? Well no apologies or sympathy, we all have our problems.

The Enterprise Scheduling Service (ESS) provides the EBS Concurrent Manager equivalent service in FA but with an extended list of features and functionality. The Enterprise Scheduler is a J2EE application that provides schedule based callbacks to other applications to run their jobs, it does not execute the jobs itself.

Here is some interesting points to note:

• Completely Metadata driven: the metadata consists of job definitions, including the executable class, parameters and schedules. All Metadata is created using Oracle JDeveloper, limited functionality via FA ESS Job Definition UI to register a Job.
• Job Definition or Job = Concurrent Program in EBS.
• Job Set = Request Set in EBS, sequential or parallel set of Job Steps, where a Job Step can be a single Job or another Job Set.
• Different job types, including JAVA, SQL, PL/SQL, SQL*LOADER, C, HOST and BIPublisher.
• Includes features like Incompatibility, Schedules and Workshifts.
• Monitoring of jobs through ESS Monitor UI and/or Oracle Fusion Applications Control (Enterprise Manager)

We are looking for a talented technical writer, experienced in Linux and Virtualization. It is important that the candidate knows Docbook XML in depth. Basics requirements:

• 3 or more years previous experience in writing software documentation (please provide URLs of your writings)
• Experience with writing documentation for system level software and operating systems
• Strong knowledge of the Linux operating system
• Strong knowledge of XML, DocBook XML, and XSL style sheets (and motivation to help maintain and expand our tools and infrastructure)
• Ability to administer own workstation and test environment
• Good experience with distributed working environments and versioning systems such as SVN

The location where we are looking to hire is mainly Ireland, possibly considering also Scandinavian countries. Position Description

I'm pleased to announce that Microgen DBClarity Developer is now production and available for download from the Microgen site, where you can also find an overview flyer and presentations, tutorials, short feature videos, and more.

For other news, read the following press release: Microgen releases DBClarity Developer.

You can also watch the full set of  videos on YouTube.


Have fun!  Let us know how it goes.  All contact details are shown on the website.

I tried adding a new entry to my blog a few times since my last entry. My plan is to highlight a few key areas in DBClarity Developer so that you can learn a little more about what the product does. I have a list of around 10 topics, which morphs and expands as my ideas change. Having shown you how to “Get Started” in my first video, the next step is to write about the SQL Editor, as described in the presentation, Tutorial 1 - Introducing the SQL Editor , which is on the DBClarity website. There’s the rub..., for those of you who know me and my back ground, I can almost hear you say, “SQL Developer does this and more” or even “TOAD has a richer editor than this”, and so what am I up to? Well, that’s the point, this is a different product.


I have created a new video, Microgen DBClarity Developer: SQL Editor, which instead of focusing on the SQL Editor, shows you where it fits in, and then goes on to show you how you can start creating the visual queries, what we call SQL Rules. Take a look.

In the next video I’ll take a look at how to deploy your project.

I'm not sure we've had a winter here, well in the south eastern reaches of the UK at least.  It's true that we're walking the dogs in the dark, which means it's winter, but my hands don't stick to the icy metal railings, instead we're walking in the dark to the tune of the dawn chorus!  Yesterday a severe storm wrenched trees from their roots and blew things about the garden, but we're still in double digit degrees (that's Celcius) and so definitely too mild to really be called winter; or am I finally getting used to the weather in the UK?

Still we're back and at 'em now I hope the break was good, restful and fun.  I'm back in London working with the team preparing for the launch of new software.  To that end I have done a mini series of videos on how to do a number of activities in Microgen DBClarity Developer. Instead of listing each video, I have created a YouTube playlist - the videos include "Creating Database Connection", "Adding files to Projects" and "Deploying Projects". 

Have fun!

I see that Lewis Cunningham is doing a free webinar on SQL Developer 3.0 for ODTUG in January.  Here is his post on the news http://it.toolbox.com/blogs/oracle-guide/sql-developer-30-new-features-webinar-43499 There is a link to register and a bit on how he uses SQL Developer in his day-to-day tasks.

Here http://www.odtug.com/apex/f?p=500:235:0::::P235_NEWS_ID:3061 is a list of free ODTUG webinars posted for 2011.

Conference season may be over and you may all be into the swing of the end of year activities, but things are still happening here. DBClarity Developer Beta 1 has been out for just under 2 months and we're about to release a second beta.  Before we do that, we thought we'd show you more about the product and give you the opportunity of seeing what it does and to ask questions.  Register for the live Microgen DBClarity Developer Webinar to be run on Thursday 15th December, and because this is a webinar, it's "at a city near you"; just join in, watch, listen, learn, and ask questions.

DBClarity Developer is a standalone product that provides a graphical interface for creating SQL queries, procedures and functions. The beauty of the graphical interface is that you can create the code graphically and let the product build the SQL, once done, you can revisit the diagram.  It's great for maintenance because you don't need to trawl through lines of code, in an attempt to figure out what it's designed to do, and it's great for collaborative work, because the visual aspect helps when you're explaining how the code works. DBClarity Developer generates SQL code for Oracle, Teradata and SQL Server and works alongside your other development tools.

Why not join the webinar, take a look and see what it's all about? Register here:  Microgen DBClarity Developer Webinar.

SQL Developer is an extensible product - that means that you can write extensions (Java or XML) and include them in the product.  We have teams at Oracle, not part of the SQL Developer development team, doing just that.  The Oracle TimesTen, Data Miner and Rdb teams are all examples of this.  The first Oracle team to write a significant extension, was the Migrations team  - the only difference is that they subsequently became part of the SQL Developer development team and so work on other features in addition to the Migrations work - but I digress...
There are teams outside Oracle who are also writing extensions for SQL Developer.  You can see a list of them here - SQL Developer Third Party Extensions. Listed on the site they're pretty harmless; you can look at the list of extensions, review the individual websites and decide to download and include the extension or not. Extensions can also be added using Check for Updates. If your company allows you to download software from OTN and other sites, then this is an easy way to get new updates and extensions. SQL Developer is no longer using this Check for Updates facility to update the core functionality.  We only use it to advertise when there is a new release available and if we have a full new extension for download.  We also use the Check for Updates utility to help you download and install the correct drivers to connect to some third-party databases. In the end the Check for Updates utility is most useful for our customers and partners who are developing extensions.

Yesterday I added a number of new extensions to the Check for Updates Utility and updated a few of the existing extensions. 

How to Install Extensions
Start up your current release of SQL Developer and select the menu Help > Check for Updates.... Navigate to Step 2 to search through the various update centers available. You may be prompted for Proxy details if you have a firewall, once that is confirmed you'll be presented with the screen below. If you have a local file, which you have downloaded or even created, then you can select "Install from Local File" and complete the install. For Oracle, SQL Developer or customer created extensions, select the "Search Update Centers.
The choices are as follows:

• Oracle SQL Developer: All version control extensions and  any announcements about the latest SQL Developer release.
• Oracle Extensions: All extensions created by Oracle teams external to the SQL Developer team. This excludes extensions we automatically ship with the product, like the TimesTen extension.
• Third party extensions:  This lists all extensions not developed by Oracle and includes some third-party database drivers.

All extensions are listed alphabetically and each has a description and version details. Select each extension you want to install and click Next.  Most extensions have license agreements associated with them. Read and accept each license.  Once you have agreed to all licenses, the extensions will be downloaded and installed. All extensions will be installed in the \sqldeveloper\sqldeveloper\extensions directory, where you installed SQL Developer.

Alert!
Oracle does not test these extensions and you should be aware of what you are downloading and installing. You should also be aware that some extensions are free and some have a free trial period. Most if the extension developers are active on the  SQL Developer forum. on OTN so if you have queries about the extensions, ask them on the forum.

I hope you take an opportunity of popping over to Kris's blog - he's just done a piece on the new pdf support in SQL Developer 3.0 The pdf support is great when you export your data you can export it to PDF. But not just that - there are loads of settings to control and format the file. Take a look at his encrypted pdf blog for a taster.

Schema Browsing
I want to tell you about the Schema Browser. This is a great feature for those of you who work with objects you don't own. So for example, if you have access to run queries or update tables that have been created by another schema, in all releases, including SQL Developer 3.0, the way to review these tables is either through the Other Users node or by creating synonyms for the tables and then having the tables displayed via the synonyms in your own connection. So it's a little fiddly. In SQL Developer 3.0, we have added in a new schema browser. To find it, select a connections, right-click to invoke the context menu and select Schema Browser. This opens a new navigator for that schema. Now you can do a number of things.

Firstly, select the schema you want to work with. This is equivalent to finding the user in the Other Users node. Then you can select the object-type you want to work with. Let's say, for arguments sake, that you select tables - You can filter the tables listed. The filter option is the same filter dialog you can use to filter any of the nodes in the Connections navigator.

In the image, I have circled the first two options. The filtering criteria can be set by using the additional drop lists.
There is another filter - it's a client-side filter - and this allows you to quickly filter objects in the already filtered list. In my example, I have a number of tables in the filtered list, and then I have reduced the list further by using the extra client-side filtering.

Try it out.

Well, I couldn't wait a day to do another post. Here is a feature you would not have seen at OpenWorld. SQL Developer 3.0 generates a Table API. Now those of you who go far back enough will know other tools that did this and we have had a number of feature requests to add in a table API generator, so here we go, a TAPI!

Why a Table API? Well, some teams would never dream of letting developers insert, update , delete or generally work directly with tables. Many teams want to track who, what, when, where events and having a Table API (I call them "table wrappers") is a start point to add in that kind of code. Here's how you'll find it:

What a funny old world this is, the more things change, the more they stay the same..., more of that thought another day.  What I can tell you is that Tristan's webinar introducing DBClarity Developer is now available to listen to and watch. This is just over 30 minutes and should show you where DBClarity Developer fits into the scheme of things - specially if you're still thinking it's another SQL Developer or TOAD tool.  Go on try it.
For a tool new to this market, it's interesting to see which companies are looking at DBClarity Developer and why they are - the story is the same - They have loads of developers, building ad hoc reporting queries and they want them to be efficient.  (that's both the developers writing the queries and the queries themselves!)  Now we know we can't all be SQL gurus, so if we can use a tool to produce more efficient SQL Queries then why not? So if you have 100s of developers writing ad hoc SQL reports, have a look at DBClarity Developer.  Today is as good as day as any as we have just released DBClarity Developer Beta 2!

I've spent the last few days recording more demo videos and will publish a few of those in the next few weeks.

Microgen's tools are new to me, and having worked with Oracle for so many years, I need to see where they fit in and why they may be of interest to you. I'm going to do another video to show you more "How To..." bits, but I thought the screenshot on our website might go a long way to helping you understand. The graphic on the left hand side is what you build, the code on the right hand side is what the product produces. I think it's really neat.  The main idea is that business end of the procedure can be discussed with a business user.

Take a look at Tristan's blog entry today, where he talks about just that "Why Microgen DBClarity Developer?" He's the product manager and has worked with customers and the products for some years.

There is a Free Download of DBClarity Developer (Beta), if you'd like to try it out.