ApEx: Manage Item Help Texts

Recently a customer asked me if he could specify or manage his own help labels. My first reaction was to give him access to my development environment and explain how he could set the help texts for each label. After some more thinking I think anyone would see that this is not really the best solution, what if he accidently deletes items or other components?

Time to work out a custom solution. I started by searching how the help texts for items are saved on the flow user. I found out that if you write a help text, a record is inserted in the wwv_flow_step_item_help table. This record refers to a record in the wwv_flow_step_item table, which is the actual item.

With this information I could make a view and work with an ‘instead of’ trigger to update/insert the help texts. Let’s start with the view, I need all items that are not hidden and order them by page and region and display sequence.

CREATE OR REPLACE FORCE VIEW “V_APEX_ITEM_HELP_TEXT” (“FLOW_ITEM_ID”, “FLOW_ID”, “ITEM_NAME”, “ITEM_LABEL”, “PAGE_ID”, “PAGE_NR”, “REGION_NAME”, “ITEM_HELP_ID”, “ITEM_HELP_TEXT”) AS
SELECT item.id flow_item_id,
item.flow_id flow_id,
item.name item_name,
item.prompt item_label,
item.flow_step_id page_id,
item.item_sequence page_nr,
region.plug_name region_name,
item_help.id item_help_id,
item_help.help_text item_help_text
FROM flows_030000.WWV_FLOW_STEP_ITEMS item,
flows_030000.WWV_FLOW_PAGE_PLUGS region,
flows_030000.WWV_FLOW_STEP_ITEM_help item_help
WHERE item.flow_id = v(‘APP_ID’)
AND item.item_plug_id = region.id
AND item.id = item_help.flow_item_id(+)
AND display_as NOT IN (‘HIDDEN’)
ORDER BY flow_step_id, plug_display_sequence, item_sequence;

We now need the trigger that will update or insert the help text for an item:

create or replace TRIGGER V_APEX_ITEM_HELP_TEXT_BIUD
INSTEAD OF
INSERT OR DELETE OR UPDATE
ON V_APEX_ITEM_HELP_TEXT
REFERENCING OLD AS OLD NEW AS NEW
BEGIN
IF inserting OR deleting THEN
raise_application_error(‘-20010′,’delete are insert not allowed’);
ELSIF updating THEN
——————————————————————————
— insert help text
——————————————————————————
IF :new.item_help_id IS NULL THEN
INSERT INTO flows_030000.wwv_flow_step_item_help ( flow_id,
flow_item_id,
help_text)
VALUES (:new.flow_id,
:new.flow_item_id,
:new.item_help_text);
——————————————————————————
— update help text
——————————————————————————
ELSE
UPDATE flows_030000.wwv_flow_step_item_help
SET help_text = :new.item_help_text
WHERE id = :new.item_help_id;

END IF;
END IF;
END;

The application in question has more than 100 pages so I will give my customer the option to first select the page and then fill in the help items. I started by making a page and create a new item (P9500_PAGE_ID) with type select list with submit, in the list of values source I have the next query:

SELECT page_id || ‘ – ‘ || page_title display_value,
page_id return_value
FROM APEX_APPLICATION_PAGES
ORDER by page_id

The query will list all my pages in my select list. Now I made an updateable report based on my V_APEX_ITEM_HELP_TEXT view.

All done, the instead of trigger will perform the necessary action to save the help texts.

Although this solution has a lot of pro’s and contra’s, this proves again how flexible we can work with ApEx.

JDeveloper Tips & Tricks

In the different projects I’ve worked on the past few years, I’ve always worked in an Oracle-Java environment. This means I’ve always used an Oracle Database, mostly JDeveloper as my IDE environment and deploying to an OC4J instance or an Oracle IAS instance.

During these projects different issues arose regarding business requirements we needed to implement using JSF/ADF or deployment-issues when porting a 9i application to 10g environment, …

I’ve tried to keep my own cookbook regarding these different issues we’ve faced and how we resolved them.

In this post you will find different challenges we’ve addressed regarding working in an Oracle-Java environment. Please feel free to comment and add challenges/issues to the list !


You want to open up documents in a new screen, such as pdf-documents or word-documents
Use ‘targetFrame=_blank’ for a goLink to open the link in a new window

Refresh a table-component when a dropdown-component is changed, a filter.
To refresh a table given a pre-defined filter you can use the following components ‘selectOneChoice’ with a table-component and this table-component holds a partial-trigger which listens to the ‘selectOneChoice’-component. This means you will use the partial-page-rendering feature of jsf which only render the table-component on the page and no other components.

Buttons aren’t shown correctly on a linux environment when using a custom skin
ADF Faces generates images on the fly for the different widgets (buttons, tabs, …), in the different languages, if it’s a multi-lingual application. The buttons, f.ex. are not standard HTML buttons, but GIF images. They are created in a cache directory
(\public_html\WEB-INF\temp\adf\images\cache\\) and then reused for the next runs.

The generation of the image may fail, f.ex. because the physical font is not available on your OS.
In the case of a button, it will then be generated as a standard HTML button, instead of an empty image, with no text.

Change font to “Dialog” instead of using “Arial, Helvetica’ because “Dialog” is a special font, a virtual font, that doesn’t depend on any physical font installed on your machine.
If the application runs in a Linux environment, you need to use either a virtual font, f.ex. Lucida, or a font physically installed on your Linux system

Navigation between pages without validation-exceptions being thrown
You want to be able to navigate away from a creation-page but the validation-exceptions are always triggered => use ‘immediate=true’ attribute on the command-link or the menutab-component so validation isn’t fired
You’re getting ‘current rowkey has been modified’-exceptions, set the enableTokenValidation-attribute on the pageDefinition-file to false so rowkey-validation isn’t performed

Delete files from Jdeveloper or exclude files from the Jdeveloper project
If you want to delete files from your project make sure to use the ‘File-menu’ ‘Erase from disk’ instead of the delete icon in your application navigator. If you use this delete-icon this means an exclude-option is added to your project-properties so the files aren’t shown anymore, but they still exist.

Your application does not work as expected
Try stopping OC4J, clean up the workspace (go to run-menu and choose clean-up), rebuild the entire workspace and run again.

When running the page, the browser remains empty
Apply the same steps as for the previous problem: ‘Your application does not work as expected’
Delete the WEB-INF/temp folder from the view-project and test the application again

The JSPX Design Editor looks strange

If you see this in the JSPX Design Editor:

Then JDeveloper does not properly recognize the Faces tag libraries.
You can try to click the refresh button (indicated by the arrows in the screen shot above).

If that does not work:
1. Close all JSPX’s in JDeveloper.
2. Go to the ViewController Project, to the Project Properties, and then to the JSP Tag Libraries category.
3. Remove all JSP Tag Libraries. When asked to remove other Tag libraries, click OK, but when asked to remove Java libraries click Cancel.
4. Close JDeveloper and open it again.
5. Add the tag libraries
o JSF Core
o JSF HTML
o ADF Faces Components
o ADF Faces HTML
6. Rebuild the ViewController project
7. Open the JSPX again.

Exporting table-date (master or master-detail) to Excel in an ADF-Faces application
Using your ‘FacesCtrlRangeBinding’ the table-component in your pageDefinition-file, you can export the data to an Excel-file. If you have a table that needs to be exported to Excel and when there are columns that you don’t want as a part of the export, create a new table binding in the pagedef with the right columns and use the name of the new table binding as a parameter of the ExportToExcel method in the backing bean of your page.

This could be the case when a calendar object is displayed in the table, typically you will want to use a dateAsString field in the exportToExcel.

You will find the code to support this functionality below; if you have any questions please comment.


You’re getting the following error when trying to connect to an Oracle Database via JDBC:
Error initializing connection: ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified

When you take a look at this URL: http://www.oracle.com/technology/products/jdev/htdocs/10.1.3.0.3/readme.html#ide5
The next topic is very important regarding this issue:
ORA-604/ORA-12705 Error on Some Unsupported Locales in JDBC (4704421)
Make sure you’re not defining a Locale that isn’t supported by JDK 1.5, have a look at the ‘supported locales’ in the JDK 1.5 specs: http://java.sun.com/j2se/1.5.0/docs/guide/intl/locale.doc.html.

Classpath-issues when deploying an application to OC4J or IAS
When you have a look at the OC4J DeveloperGuide (http://download.oracle.com/docs/cd/B31017_01/web.1013/b28952.pdf), more specifically at chapter Executing Queries at Runtime Through the ClassLoading MBean (page 53). You will find EM has all the needed functionality to have an in depth view of the class loading of your specific project.

An example mentioned in the developer guide:

Executing Queries at Runtime Through the ClassLoading MBean
Queries can be
executed on a running OC4J instance by calling the executeQuery operation on the
ClassLoading MBean.
This MBean is accessible through the Web-based
Application Server Control Console interface. See the Oracle Containers for J2EE
Configuration and Administration Guide for details on accessing and using the
MBeans packaged with OC4J.
1. Click the Administration link in the
Application Server Control Console.
2. Click System MBean Browser.
3.
Expand the ClassLoading node in the navigation pane, then select the singleton
MBean instance.
4. Click the Operations tab in the right-hand pane, then
click the executeQuery operation.
5. Enter the name of the query you want to
execute as the value for queryClassName. For example, LoaderTree.
6. Click
the queryArguments icon, then add a new row for each argument you want to
specify. Do NOT enclose arguments in parentheses; these are added automatically
when the operation is invoked. Click OK when finished specifying
arguments.
7. Click the Invoke button to call the operation.

In the chapter ‘Troubleshooting Class-Loading-Related Problems in OC4J’ an explanation is given about the different exceptions that may occur and how to solve them.

Compiler-errors when checked “Use Javac” Compiler option in the Project Properties dialog
This problem has been reported asBUG 6311285 – OJC ALLOWS CASTING OF GENERIC OF IMPL CLASS TO GENERIC OF INTERFACE CLASSA fix is already included with JDeveloper 11.1 Developer PreviewAs a workaround you can use javac instead of ojc for compilation. javac will report such wrong assignments. You need to compile with ojc only if you want to use JDeveloper profiling or code coach.
For more information have a look at the following forum-thread: http://forums.oracle.com/forums/thread.jspa?threadID=537140

Memory Leaks in Jdeveloper:

1)Check the Virtual Memory used by Jdev: edit jdev.conf and increase the JVM memory ( tag AddVMOption).

2)Don’t load technologies you don’t need ( Jdev Preferences – Extension Manager- Extension to Use ).

3)Uncheck the “bells” in Jdev Preferences:Environment: splash scren, silenty reload, decrease undo level;Diagrams: automatically straighten lines

4)Split the project up into different modules: User Management – Reporting – Administering Site – …

No tip but just fun: SOA Facts.