Feed aggregator

Dynamic Images in PDF - What 32k Limit?

Marc Sewtz - Thu, 2012-02-23 14:36
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:


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);
dbms_lob.createtemporary (l_res, true);
dbms_lob.open (l_res, dbms_lob.lob_readwrite);

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('
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).

This blog has moved!

Chris Muir - Wed, 2012-02-22 19:48
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!

Oracle Fusion Middleware PS5 is out

Marc Kelderman - Wed, 2012-02-22 14:00
A short article, but Oracle Fusion Middleware patchset #5 is out! Now downloadble via:

Oracle Fusion Applications 101: Enterprise Scheduling Service

Andries Hanekom - Sat, 2012-02-18 22:58
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)
Want to know more, see the Oracle® Fusion Applications Developer's Guide for Oracle Enterprise Scheduler for more detail.

#OracleFusionApplicationLesson 2 for Oracle EBS Developers: Your going to be defining concurrent programs through JDeveloper.

Looking for Technical Writer

Elena's Blog - Wed, 2012-02-15 00:23
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

Microgen DBClarity Developer is now Production

Sue Harper - Mon, 2012-02-13 16:42
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.

Working on a few more demos: EDFs and the SQL Editor

Sue Harper - Mon, 2012-02-13 06:52
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.

Welcome to 2012! Is it Spring in the Air?

Sue Harper - Mon, 2012-02-13 06:51
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!

New SQL Developer 3.0 Webinar

Sue Harper - Mon, 2012-02-13 06:51
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.

First Webinar on DBClarity Developer - Learn More!

Sue Harper - Mon, 2012-02-13 06:51
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.

Check for Updates in SQL Developer

Sue Harper - Mon, 2012-02-13 06:47
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.

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.

SQL Developer 3.0 Does Schema Browsing

Sue Harper - Mon, 2012-02-13 06:47
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.

SQL Developer 3.0 Does Table API

Sue Harper - Mon, 2012-02-13 06:47
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:

DBClarity Developer Beta 2 is Now Out

Sue Harper - Mon, 2012-02-13 06:46
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.

So What's Different Then? Are you a PL/SQL Developer?

Sue Harper - Mon, 2012-02-13 06:46
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.

Microgen DBClarity Developer: Getting Started

Sue Harper - Mon, 2012-02-13 06:46
"Microgen DBClarity Developer enables the graphical definition and implementation of SQL queries and procedures” http://www.microgen.com/dbclarity/

What does that mean? Sometimes it’s difficult to get passed the marketing as you try to develop a real understanding of what a product can do for you. In the case of DBClarity Developer, what do I mean by a “graphical design” when it comes to writing SQL code? Lots of database-related products allow you to create SQL queries using a drag-and-drop approach, so what’s new here? Well, before I get into all that, you should take a look at the product.

It’s always been a bit of a challenge for me to tell the story of a product in 5 minutes! I tend to be overenthusiastic while I attempt to show users every available feature. That may be fine if you’ve used the product in question before, as this approach can help to fill in the gaps, but if you’re new to a product, it’s overwhelming at best. With this in mind, I’ve created a short video (Posted on YouTube) to walk you through the first few minutes of working with DBClarity Developer.
The video is specifically a “Getting Started” demonstration and is designed to help you get up and running, once you have downloaded and installed the product. You can see the various examples we have provided with the product, there is a brief product tour and I show you how to create a database connection and review the SQL queries we have provided. I also show you how to build the tables and set up the demo environment associated with the tutorials. Once you have watched the video, you can start creating your own queries or watch the video on how to create your first query, which you can find on the DBClarity Developer website. http://www.microgen.com/dbclarity/

Updates, News and Beta Software

Sue Harper - Mon, 2012-02-13 06:46
Those of you who follow my blog, know that I write from time to time. I started blogging back in the day when I was working on modeling and the SQL support offered in Oracle JDeveloper and then in 2006, started this blog with the launch of Oracle SQL Developer and then later to SQL Developer Data Modeler. Recently my blogging dropped right off because, after 19 years at Oracle, I decided to “hang up my boots” and try something new. Although taking photos in Cornwall really appeals, it seems that when we decide to start fresh, perhaps we don’t venture too far from where we’ve been before- well, it is true for me.

After a restful, technology–free 2 month break, I started in a new position at Microgen, a company based in the City of London. (I can see St Paul’s Cathedral from the window!) I’m in the Microgen Aptitude Solutions Division; so still in technology, still working with products that build applications and still talking to databases. My new laptop has SQL Developer on it and I can connect to an Oracle Database!

Microgen DBClarity Developer is Beta

I’ll talk a little about the tools and technology I’m now involved with, in the weeks and months to come; today I want to tell you about a beta program we’re running for Microgen DBClarity Developer. This new product is focussed on people who want to query the database, by writing SQL queries and procedures, but who are not necessarily familiar with SQL syntax. The idea is that through the graphical interface IT and Business users can work together on gathering data. There is an overview on the website: http://www.microgen.com/dbclarity/

Consider writing a SELECT statement for a correlated query; if you’re a DBA, SQL or PL/SQL developer you can do this faster than I type these sentences, but if you’re with someone who knows what information they want from the tables they have access to, more complex SQL Queries may be daunting to them. If you can represent your queries graphically to them, then the whole discussion may become much easier. This tool is not a SQL Developer replacement, so why not download it, take a look and see what you think? I’d love to hear your feedback. I’ll be back soon with tips on how to get started and how to build a few basic queries. The software is shipped with a set of tutorial examples, so you can see what I mean quite quickly. Here’s how:

1. Download the software from here: http://www.microgen.com/dbclarity/

2. Expand “My Projects” and double-click to select the DBClarity Tutorial of your choice. (This opens a new tab set with the project selected.)

3. The tutorial projects are seeded with a number of defined objects. (I prefer to use the Dictionary View. You can switch to this by selecting the view choice icon just above the project. Once you have switched views, you’ll see a list that includes SQL Rules, SQL Expressions etc.)

4. Whether you switch views or not, expand the SQL Rules, select and double-click to open the rule. This displays a visual representation of your SQL Query.

5. To see the SQL Query, select the Show SQL icon.

For more help and information, take a look at the set of tutorial slides related to the examples on the website.

SQL Developer Data Modeler 3.1

As for my history…I see that SQL Developer Data Modeler 3.1 is now beta. Have fun with that!

Create Bigfile Tablespace – Oracle Managed Files (OMF)

Tyler Muth - Fri, 2012-02-10 10:40
Working with Exadata a lot (almost exclusively) I create almost all my tablespaces as bigfile using OMF. For some reason I can never find the right syntax for this when I need it. So, that’s the main reason for this post. On a related note, wow I use “column mode” in Notepad++ (link) a lot […]
Categories: DBA Blogs, Development

Model-based Cascading LOV's For JHeadstart

JHeadstart - Fri, 2012-02-10 02:32

Written By Peter Koletzke, 8 February, 2012 (posted by Steven)


While searching for employees, users need to find a department by first finding a location from a City LOV (dropdown list). After selecting a city, the City and Postal Code appear on the screen.  LocationId also returns from the LOV, but is hidden because users should not see the ID.  

Then the user opens a Departments LOV (pop up LOV with query fields), which displays only departments within the selected city. After selecting a department, the Department ID and Department Name are returned to the screen. 


Implementing a cascading LOV consists mostly of work in the Model project. It is important that you prove that the cascading LOV works in the Model project before implementing it in the ViewController project. A brief list of the special components and settings needed to implement a cascading LOV follows. 

You can access a detailed recipe for this technique here. A sample application that results from this recipe is here.


  • Base view object for the Employees entity object: EmployeeMaster
  • Read-only view objects for the Locations LOV and Departments LOV queries
  • View criteria on the Departments LOV to represent a WHERE clause for location_id = :bind_variable
  • Locations and Departments added as reference entity objects to EmployeeMaster
  • Calculated attributes in EmployeesMaster as follows. All have Queryable as “true” and Display Hint as “Display.”
    • LocCity (LOCATIONS.CITY)
    • DeptDepartmentName (DEPARTMENTS.DEPARTMENT_ID)
  • Locations LOV on the Employees.LocCity attribute, returns city, location ID (hidden), and postal code
  • Departments LOV on the Employees.DepartmentName, returns DepartmentName and DepartmentId, uses DepartmentsLOV view object accessor with the detail LOV view criteria mentioned above (parameter value = LocLocationId)
  • AutoSubmit UI hint set for the two LOV items: LocCity, DeptDepartmentName
  • Dependencies to LocCity from DeptDepartmentName and DepartmentId (so they clear when the city changes)
  • View criteria on EmployeeMaster for a list of attributes needed in Quick Search area. An additional view criteria is needed for the Advanced Search area. LocLocationId is listed in both view criteria, but its UI Hint for Rendered Mode is “Never.” Deselect Show in List on the UI Hints tab.


All actions are in the JHS application definition editor unless noted.

  • Set the LocLocationId item so it does not display in the table or in the form.
  • Set Quick Search View criteria and Advanced Search View criteria properties to the names of the appropriate (but separate) view criteria you created for to list attributes in these search sections
  • Set Depends On Item(s) properties for LocPostalCode as “LocCity,” for DepartmentId as “DeptDepartmentName,LocCity” so the LOV return fields on the edit page are set after an LOV selection.
  • Select the Clear/Refresh Value? property for DepartmentId so it clears when the city or department name changes.
  • For JHS select Clear/Refresh? Value for DeptDepartmentName.
  • For JHS edit adf-config-EmployeeMaster.xml after generation to define a depends-on refresh for DeptDepartmentName.

Categories: Development


Subscribe to Oracle FAQ aggregator