 Just read Dimitri Gielis posting that he has integrated the ApexLib Framework into his DG Tournament application. Great to see that the ApexLib Framework also works in another application than in my protected environment. Thanks Dimitri for trying out! Labels: ApexLib
 « ... Read full posting ... »
Tom Kyte just called my attention to the following posting. I like the idea. My teachers should have used that technique in my school time, too! I will not start to include lies into my blog postings :-), but I also like the conclusion of the posting. - "Experts" can be wrong, and say things that sound right - so build a habit of evaluating new information and checksum it against things you already accept as fact.
- If you see something wrong, take the initiative to flag it as misinformation.
I don't consider me an APEX expert, that's why always crosscheck what you are reading in my blog and if you find some misinformation or doubts about what I write, please let me know! Discussions makes life much more interesting! :-)
« ... Read full posting ... »
A year ago Bernhard Fischer-Wasels blogged about how many APEX application pages he found with Google (search for inurl:/pls/htmldb). On the 23-Dec-2005 he found about 19.000 entries. How many do you thing do we find 1 year later? Guess... Google finds 162.000 entries!!!What an increase! How many do you guess do we have next year? :-)
« ... Read full posting ... »
Just have uploaded release v0.13 of the ApexLib Framework. It contains the following bugfixes: - Bug# 1620834: ApexLib_Error: Attach a space after APEXLIB_TABFORM_ROW_PREFIX
- Bug# 1619435: ApexLib_Error: field information should not be visible on error page
- Bug# 1620953: ApexLib_TabForm: Error message for invalid format has wrong placeholders
- Bug# 1620456: Views: Support Application Format Mask PICK_DATE_FORMAT_MASK
Files added/changed: - ApexLib_Error.pkb
- ApexLib_TabForm.pkb
- ApexLib_views_for_userschema.sql
- ApexLib_views_for_flows_schema.sql
Labels: ApexLib
« ... Read full posting ... »
I thought it's time to put all the steps which are required to integrate the ApexLib Framework from the different postings into one integrated instruction. Makes it much easier when you use the framework for the first time. Read the newest version of the How To Integrate document in the SVN repository. Note: The above version shows the most current version of the document which may already contain steps for features which have not been released yet. When you integrate the Framework into your application, always use the version of the document which is provided in the zip-file! Labels: ApexLib
« ... Read full posting ... »
Just read Pete Finnigan's Oracle security blog where he is pointing to the very interesting presentation Hacking and Hardening Oracle Express Edition done by Red Database Security at the UKOUG 2006. Everybody who is deploying his APEX application on Oracle XE should read that! Labels: Oracle XE, security
« ... Read full posting ... »
The following overview, lists the postings I have done so far. I think it gives a better start what has already been posting, then going through the monthly archive pages of the blog. If you like this blog you may want to subscribe with your RSS reader (eg Google Reader, ...) so that you get informed when there is a new posting. Never heard of RSS? Read my related posting. Note: Postings which are outdated are not included in the overview. Last update of the list: 27-Jan-2008
« ... Read full posting ... »
Just read an interesting posting on Igor's Oracle Lab about the planned PL/SQL enhancements in Oracle 11g. I really like some of the planned features. Want to get the full story? Check out the presentation done by Tom Kyte. Labels: Oracle 11g beta, Other blogs, pl/sql
« ... Read full posting ... »
Thanks to Andree who found an error in my description for the Hacking the APEX error page. Seems that I screwed something up when I last updated the posting. <script language="javascript" type="text/javascript">
<!--
ApexLib_handleErrorMessage("t7Messages", "t7Notification", "#6699cc", "#000000");
//-->
</script>
Should be <script language="javascript" type="text/javascript">
<!--
ApexLib_handleErrorPage("t7Messages", "t7Notification", "#6699cc", "#000000");
//-->
</script>
Thanks to Andree who informed me about that!
« ... Read full posting ... »
 Do you want to have highlighting for the tabular form column cells which have an error? Then you should read the rest of the posting! I have enhanced the original Hacking the APEX error page code (the original posting has been updated) and created a new package ApexLib_Error which can be used to "raise" errors in a process or validation process when you process your tabular form values. The advantage of this package is that it allows you to provide the column and row number when you raise an error.
When the error message is displayed above the tabular form region, all the column cells which are related to an error are highlighted.
Check out the updated Enhanced Tabular Form with column cell highlighting example.
This new package enhances the existing Plug & play tabular form handling solution. You have different possibilities to use the new package. It has methods which can be used in validation process of type Function Returning Error Text and PL/SQL error. If you use it in a process you always have to use the raise* interface.
Let's do some examples:
Create a page validation with the following settings- Identify the validation level: Page level validation
- Select a validation method: PL/SQL
- Select the type of PL/SQL validation you wish to create: Function Returning Error Text
- Error Display Location: On Error Page (it always has to be on error page!)
- Validation:
BEGIN
ApexLib_Error.addError
( pError => '%label has to be between %0 and %1'
, pColumnName => 'SALARY'
, pRow => 1
, p0 => 1000
, p1 => 5000
);
ApexLib_Error.addError
( pError => 'SALARY_NOT_MATCHING'
, pFieldId => 'f03'
, pRow => 5
, p0 => 1000
, p1 => 5000
);
RETURN ApexLib_Error.getErrorStack;
END;
What does this code do?
It puts to errors onto an error stack, the first call identifies the column by the name (works only for updateable tabular forms) and the error text is hard coded, but with placeholders.
The second call uses a defined text message (Shared Components/Text Message) and identifies the column with the fxx syntax (in the case if you know to which HTML form item the column is mapped to). At the end of the validation process the error stack is returned to APEX. If no error is on the stack, nothing is returned. So this statement should always be the last statement in your validation process!
Let's do another example!
Create a page validation or process with the following settings- Identify the validation level: Page level validation
- Select a validation method: PL/SQL
- Select the type of PL/SQL validation you wish to create: PL/SQL Error
- Error Display Location: On Error Page (it always has to be on error page!)
- Validation:
BEGIN
ApexLib_Error.addError
( pError => '%label has to be between %0 and %1'
, pColumnName => 'SALARY'
, pRow => 1
, p0 => 1000
, p1 => 5000
);
ApexLib_Error.addError
( pError => 'SALARY_NOT_MATCHING'
, pFieldId => 'f03'
, pRow => 5
, p0 => 1000
, p1 => 5000
);
ApexLib_Error.raiseErrorStack;
END;
Same as the above example, the difference is at the end of the code. Instead of returning the error stack we raise it with an RAISE_APPLICATION_ERROR so that APEX can capture it. This method of adding errors to the stack, allows you that you can also have multiple errors.
Another example
Same as above, but this time with the following code:
BEGIN
ApexLib_Error.raiseError
( pError => '%label has to be between %0 and %1'
, pColumnName => 'SALARY'
, pRow => 1
, p0 => 1000
, p1 => 5000
);
END;
This will immediately abort processing and the error is returned to APEX.
For a real world example check out the Plug & play tabular form handling posting.
The code has been tested with Firefox 2.0 and IE 7.0.
Where do I get the source code?
I have started a new SourceForge project, get the source code there.
Have questions? Don't hesitate to write me a note!
Update
Fixed an issue with Internet Explorer.
Labels: ApexLib, error, tabular form
« ... Read full posting ... »
I have implemented a small improvement into my Generic solution for cascading lovs. If you have a hierarchy of cascading lovs as I have it on my example page, the old version did an AJAX call for all cascading lovs and there cascading lovs, ... each time a value was selected. But normally these calls will always result in an empty select list, because the value of the parent lov hasn't been set yet.
The new version will only ask the server for new values if all values which are used by the lov are entered. If there is an empty value, it will just clear the lov values.
In the case if you have a more complex lov which does some defaulting and where it's not necessary to fill out the parent value, you can use the $APEXLIB_ALWAYS_REFRESH$ hint in the comment text to enforce that the lov is always populated from the database. Use this hint only if you have for example a similar where clause in your lov.WHERE DEPTNO = NVL(:P4_DEPTNO, 10)
Version History:
v0.09 18-December-2006
- CR# 1617835: ApexLib_Lov: Performance - don't do a server roundtrip if it isn't necessary.
- Files changed:
- ApexLib.js
- ApexLib_views_for_flows_schema.sql
- ApexLib_views_for_userschema.sql
- ApexLib_Lov.pks
- ApexLib_Lov_for_flows_schema.sql
- ApexLib_Lov_for_userschema.sql
Labels: ApexLib, cascading lov
« ... Read full posting ... »
Maybe you already had the "joy" to find out that accessing the "Row Selector" column of an updateable tabular form isn't that straightforward as expected. Because the corresponding g_f01 array isn't indexed by row number as the other column-arrays of the tabular form. Instead it contains as many values as you have checked rows. The value of the array entry contains the "real" row number for which it has been checked. So you can have the situation that g_f01 has a count of 2, but your g_f02 containing the EMPNO contains 15 rows. In the case if the g_f02 is your "driving" array, because you want to process all the records and only for the checked ones you want to do something special, it can get really challenging for a new APEX programmer (and even for a more experienced one). That's why I have added new functionality to my Plug & play tabular form handling package, to make accessing the row selector as easy and straightforward as possible.
The package ApexLib_TabForm now contains the additional methods isRowSelectorChecked, setRowSelector. The hasRowChanged has been enhanced to also check for the row selector column.
Let's demonstrate the new functions with an example!
BEGIN
FOR ii IN 1 .. ApexLib_TabForm.getRowCount
LOOP
IF ApexLib_TabForm.hasRowChanged(ii, TRUE)
THEN
IF ApexLib_TabForm.isRowSelectorChecked(ii)
THEN
callAbc
( pEmpNo => ApexLib_TabForm.NV('EMPNO', ii)
, pEname => ApexLib_TabForm.V ('ENAME', ii)
, pHireDate => ApexLib_TabForm.DV('HIRE_DATE', ii)
);
ELSE
callXyz
( pEmpNo => ApexLib_TabForm.NV('EMPNO', ii)
, pEname => ApexLib_TabForm.V ('ENAME', ii)
, pHireDate => ApexLib_TabForm.DV('HIRE_DATE', ii)
);
END IF;
END IF;
END LOOP;
END;
This example demonstrates several features of the ApexLib_TabForm package.
- With getRowCount you don't have to care that you use the correct g_fxx array to process all records. Remember: If you have a row selector, g_f01 just contains as many records as you have checked values. So you would have to use g_f02 in that case.
- hasRowChanged compares the old- with the new checksum, to verify if the record has been changed. If the new additional boolean parameter pIncludeRowSelector is set to TRUE, the row selector column will also be checked.
- isRowSelectorChecked will return TRUE if the checkbox has been set for that row.
- NV, V and DV to access the column by name. You don't have to worry anymore that when somebody is resorting the columns of the report, that it will break your code. This functions will also return the value of the column with the correct PL/SQL data type by applying the format mask which is defined for the column.
Get the source code from the original posting and try it out!
Labels: ApexLib, tabular form
« ... Read full posting ... »
 Do you like the separate APEX error page which is called when APEX raises an error (eg in the MRU/MRD process for tabular forms)? If yes, you don't have to read further, if your answer is NO you may should read the rest of this posting...
Wouldn't it be cool and much user friendlier to have this error message displayed as inline message, as we can do it for validation processes? You thought that's not possible, because when the page is re-rendered, all the changes in the tabular form are lost... It is possible!
Yesterday I had an idea and did a "little" (ok it took a little bit longer, because I'm a newbie in JavaScript :-) ) hacking and finally came up with a solution which you just plug into our application and all error messages which normally displayed in the separate error page are now displayed as inline message, without re-rendering and loosing your entered data.
But first try out the following examples.
Standard Tabular Form example with old error handling
After you have done that, check out my
Enhanced Tabular Form example with inline error messages.
Isn't that cool? :-)
How to integrate it into your application?
Follow the steps in my How to integrate posting.
Vola, that's it! If an error message is displayed now on the APEX error page, the text will be extracted and pasted into the caller page.
See also
Hacking APEX error page - part 2
I'm new to JavaScript programming, so if you see "some errors/you don't do that in JavaScript", please let me know. I would appreciate that!
Or if you have any other suggestions or problems, don't hesitate to drop me a note. I haven't done a lot of testing yet, but it seems to work fine with FireFox 2.0 and IE 6.0.
Don't forget to check out the other ApexLib framework solutions!
Labels: APEX hacks, ApexLib, error, tabular form
« ... Read full posting ... »
I have done some minor enhancements and bug corrections for the ApexLib framework. It now supports Multiselect lovs, too. For details see the change log. BTW, did you know that the Generic solution for cascading lovs, also works if the first item is a text item which restrict another lov? I have updated the example on apex.oracle.com to include a multiselect lov and a lov which depends on a text item. Change log of version v0.06 14-December-2006: - Bug# 1615618: ApexLib_TabForm: error raised when no updateable report region exists.
Change log of version v0.05 14-December-2006 - CR# 1615591: ApexLib_Lov: add support for multiselect list.
Labels: ApexLib, cascading lov
« ... Read full posting ... »
I want to welcome the iAdvise blog in the APEX blog community! A recent posting was for example, how to " Deploy Your Multilingual application in a production environment". Welcome and keep up posting! Labels: multilingual, Other blogs
« ... Read full posting ... »
I have updated the Generic solution for cascading lovs, it offers now supports for popup lovs, too. I have also updated the example on apex.oracle.com to include a popup lov. Change log of version 0.03 (12-December-2006): - Added support for cascading popup lovs.
- Don't generate a dependency if the referenced item issues a redirect/submit anyway.
- Corrected compilation errors in the ApexLib_Lov_for_flows_schema.pkb
Update: Speed sometimes kills :-) Change log of version 0.04 (12-December-2006): - Corrected a bug in the clearLovNullValues if called from the on-demand-process.
- Corrected wrong check in ApexLib.js.
Labels: ApexLib, cascading lov
« ... Read full posting ... »
I still remember the time when I was an APEX newbie and I really got confused with tabular forms (updateable reports). They behaved very different from what I knew about "normal" page items. So what where the issues which confused me? - It isn't possible to create any validations on the columns of the report, like I can do it for page items.
- You have to use the APEX_Application.g_fxx arrays instead of using bind variables as for page items.
- To find the correct array you have to look into the generated HTML code for the correct mapping.
- If the order of the columns gets changed, your code gets screwed up, because the columns now maps to a different g_fxx array.
- If a column has a condition or authorization the g_fxx array changes again, in the case the column doesn't get rendered.
- The code isn't really readable if there are just references to g_f01, g_f02, ...
- The format mask has to be duplicated if the g_fxx is accessed and you want to get the correct data type (eg NUMBER, DATE)
- When you want to get the number of array entries you have to take care that you don't use the array for a checkbox column, because that just contains the checked values!
- You have no function to find out if the row has changed.
- When the validation of a format mask or required column fails, the error message is very internal. The user doesn't have a clue which column and which row was effected. And it stops after the first error.
That's a long list and that's why I did some programming on the weekend to get a solution for the above short comings, so that newbies and experienced APEX developers don't have to care anymore.
To make a long story short, I have created a package which you just call once in your application and which takes care of the above problems. It allows you to- reference your columns by name without having to hassling around to which array your column is mapped.
- To get the correct number of rows to process
- Identify which row has changed
- Get your data with the correct data type (NUMBER, DATE) without having to take care about format masks.
- Add a required column check by declaration, instead of coding it.
- Get more meaningfull error message than APEX provides.
But first try it by yourself, I have set up a tabular form with the default APEX handling for tabular forms. Follow the instructions and play around with it.
Standard Tabular Form example
After you have done that, check out my
Enhanced Tabular Form example.
How to integrate it into your application?
Follow the steps in my How to integrate posting.
In the case if you don't want that one of your report regions is processed by the library, add $APEXLIB_IGNORE_VALIDATION$ in the region comment to skip the processing.
That's the basic integration which does the required column and format mask check. If you want to access your column in your own validation code, follow the example to see how to do that.
Example
- Create a new page
- Page Type: Form/Tabular Form
- Allowed Operations: Update, Insert and Delete
- Table / View Name: EMPLOYEES
- Select Columns(s): Everything except of PHONE_NUMBER, COMMISSION_PCT, MANAGER_ID and DEPARTMENT_ID
- Primary Key Column1: EMPLOYEE_ID
- Source Type: Existing trigger
- Updateable Columns: ALL
- Confirm everything on the following pages
- Open the Report Attributes of the created region.
- For the columns LAST_NAME, EMAIL, HIRE_DATE, JOB_ID go to the Column Link section and enter $APEXLIB_REQUIRED$ as Link Attributes
- For the column HIRE_DATE set the following properties
- Display As: "Date Picker"
- Date Picker Format Mask: "DD-MM-YYYY" => we use a different format mask than the database has, so that auto date convertion doesn't work!
- Element Width: 10
- For the column JOB_ID set the following properties
- For the column SALARY set the following properties
- Number / Date Format: FML999G990D00
- Let's create some extra validation, so that you see how easy it is to access your columns. Create a validation for the created page
- Identify the validation level: Page level validation
- Select a validation method: PL/SQL
- Select the type of PL/SQL validation you wish to create: Function Returning Error Text
- Sequence: 10
- Validation Name: Validate Hire Date and Salary
- Error Display Location: On Error Page
- Validation:
DECLARE
vMinSalary NUMBER;
vMaxSalary NUMBER;
BEGIN
FOR ii IN 1 .. ApexLib_TabForm.getRowCount
LOOP
IF ApexLib_TabForm.hasRowChanged(ii)
THEN
IF ApexLib_TabForm.DV('HIRE_DATE', ii) > SYSDATE
THEN
ApexLib_Error.addError
( pError => 'Hire Date is in the future'
, pColumnName => 'HIRE_DATE'
, pRow => ii
);
END IF;
--
SELECT MIN_SALARY
, MAX_SALARY
INTO vMinSalary
, vMaxSalary
FROM JOBS
WHERE JOB_ID = ApexLib_TabForm.V('JOB_ID', ii)
;
IF ApexLib_TabForm.NV('SALARY', ii) NOT BETWEEN vMinSalary AND vMaxSalary
THEN
ApexLib_Error.addError
( pError => 'Salary has to be between %0 and %1'
, pColumnName => 'SALARY'
, pRow => ii
, p0 => vMinSalary
, p1 => vMaxSalary
);
END IF;
END IF;
END LOOP;
--
RETURN ApexLib_Error.getErrorStack;
END;
- Error Message: Error occurred.
As you can see in the code, you don't have to care about date convertion, you are only processing the changed records. The code is still understandable when you look at the code 5 days later, aren't names more meaningful than just references
to APEX_Application.g_f01? ;-)
You have different getter functions to access your columns. ApexLib_TabForm.V, -NV and -DV have the same purpose as the existing APEX functions which you can use for items. Check out the package specification of the ApexLib_TabForm package, all the functions are documented.
BTW, if you are not satisfied with the error message the validateColumns procedure is raising for required and format mask check. You can define your own error text by creating a message (Shared Components/Text Messages). The name has to be APEXLIB_TABFORM_COLUMN_REQUIRED (Format: Column "%0" is required.) or APEXLIB_TABFORM_INVALID_FORMAT (Format: Column value of "%0" doesn't match to the format "%1".) The row prefix can be define with APEXLIB_TABFORM_ROW_PREFIX (Format: Row %0: ).
Impact on your existing application
Nothing really changes except that you have better error messages in your tabular form, so it's no problem to use it also for your existing application.
Update
Known restrictions
- If you use APEX_Item or HTMLDB_Item to build your own form elements. I can't map the columns, because I don't know which index you have used.
- If a column has a condition or authorization. See below.
Problems?
Enable debugging, it will show you what the library is doing and which columns are mapped to which array. Look for ApexLib_TabForm in the output.
What's next?
I have now a question. Where do you install the packages? Are you allowed to install them into the FLOWS schema? Because that would make the condition checking a breeze. If there is a big demand to just install the packages into the application schema, I have to re-code the condition/authorization checking, because it's not accessable from the application schema.
Do you have other ideas and troubles with tabular form handling which you want to automate? Feedback very welcome!
Labels: ApexLib, tabular form
« ... Read full posting ... »
I think most of us had already the problem, that when the Display Null property for a LOV has been set to "Yes", that APEX had generating a %null% as indexing value for this "null" lov entry. The problem now is that when the user selects the "null" entry from the select list, the page item gets populated with the %null%. If you don't write some code to set the item to NULL when it has %null% as value, the database will raise an error (eg invalid number...).
Recently I posted a generic solution for that on the OTN forum.
I now have integrated the code into my ApexLib_Lov package which I have developed for my Generic solution for cascading select lists/lovs.
Integration into your Application
- For the general installation instructions see the above blog entry. You don't have to install the JavaScript library if you just want to use the %null% solution.
- Create a Application Process (Shared Components\Application Process)
In the case if you don't want to reset the %null% for a lov, you can specify $APEXLIB_IGNORE_LOV_NULL$ in the page item comment to skip the reset.
That's it! That solution will now work for all your pages.
Labels: ApexLib, tips
« ... Read full posting ... »
 If you are using Firefox for your APEX development you may want to check out my list of add-ons I'm currently using for the web development. If you have any useful add-ons you are using, drop me a note!
FireBug
 If you are doing JavaScript programming than this add-on is a MUST. Because it provides a JavaScript debugger and that's really sometimes necessary, especially if you just started writing JavaScript as I do.
Download it at https://addons.mozilla.org/firefox/1843/
View formatted source
 Very useful add-on to visually see which DIVs, SPANs, TABLEs, ... are building up your HTML page. And the best is it shows you the CSS-style sheet info for each HTML element when you move your mouse over it. That includes in which CSS-file and line number the style info has been defined. Very useful if you want to look how others are building there stuff... :-)
Activate it through Tools/View formatted source/Enable inline mode. If you click on the "source" tags, a new window will be opend with a tree structure of you HTML page. If you move your mouse over an element it will show you the CSS info.
On the Firefox addon page no V2.0 version is available, but somebody did a hack
so that it also works with V2.0, check it out at http://mcuadros.es/2006/10/25/extension-view-formatted-source-en-firefox-20/
Download it at https://addons.mozilla.org/firefox/697/ or at http://mcuadros.es/2006/10/25/extension-view-formatted-source-en-firefox-20/
Live HTTP Headers
With this addon you see what's going on in the background, what data is transfered from and what is coming back from the server.
Download it at https://addons.mozilla.org/firefox/3829/
View cookies
It adds a tab to the Page Info dialog box, which shows the cookies of the current web-page.
Download it at https://addons.mozilla.org/firefox/315/
Web Developer
Just downloaded and installed this add-on. Very feature rich, lot of options, ...
Will see how useful it is during development.
Download it at https://addons.mozilla.org/firefox/60/
Labels: Firefox, Javascript, tips
« ... Read full posting ... »
The German APEX community website has just released a new edition of there How-To articles. This edition has two articles about Integrating the HTML editor FCKEditor and Adding fuzzy logic search to your APEX application. Labels: Javascript, Other blogs, tips
« ... Read full posting ... »
Just read a very interesting paper titled Best of Oracle Security 2006, which was brought to my attention by Pete Finnigan's Oracle security weblog. It covers fixed security problems in Oracle DB, mod_plsql, APEX, ... which you/or your DBA may should know of. Too bad for the english speaking readers, it's just available in german. Labels: APEX, Oracle, security
« ... Read full posting ... »
Then you have to check out the blog of Dimitri Gielis! A month ago he did a posting about how to integrate the Highslide JS thumbnail viewer (check out there homepage, they have a nice demonstration gallery) into an APEX application. If you haven't attended Oracle Open World 2006 in SF (like me), you have to check out his diary about OOW. You feel as if you have been there! :-) Labels: Javascript, Other blogs
« ... Read full posting ... »
You like the build-in APEX Debugger which you can be activated through the Developers Toolbar? All links which APEX is generating when rendering the page contain the debug flag too. But when you enter something into a form and press the submit button or any other button, the Debug mode is disabled and you don't see what APEX is doing to process the data. It's time to "hack" the APEX debugger!
- Go to Shared Components and create two Application Items named APEXLIB_SAVED_DEBUG and APEXLIB_SAVED_REQUEST
- Create a new Application Process with the following settings
- Create another Application Process with the following settings
That's it, when you now active the Debug Mode by clicking into the Developer Toolbar, the debug mode setting will also stay when you press a button to submit the page.
Now you see how APEX is processing your changed data and which branches are firing.
Background information
What does the code do?
- First it stores the current REQUEST information into a field, because after the data has been processed a redirect occurs which clears the info in the :REQUEST bind variable, but we need that info.
- In the second process (on-load) we check if the page has been called with a request. For page calls with an URL link the request is always null. So if it's a REQUEST call we use our stored value from the last page rendering (where the debug flag is contained in the URL).
Happy debugging!
Update
As Vikas was pointing out on the OTN forum, the hack is only useful for pre-2.2 APEX versions, because APEX 2.2 had an enhancement in that area.
Labels: APEX hacks, tips
« ... Read full posting ... »
On the OTN forum someone was asking today...
Can I determine if I am running a page "normally" or with the developer toolbar visible, ie as a developer? If this were the case, I'd add in a condition on this to ensure it was always available to me when developing but not to end users.
 After looking around in the APEX packages I found the global variable WWV_Flow_Developer_Toolbar.g_ok_to_show_toolbar, but the problem with this package is that it's not public and you can't access it in your application.
But there is another package which is public and which gives you the information that you are running in "developer mode".
IF APEX_Application.g_edit_cookie_session_id IS NOT NULL
THEN
... in developer mode ...
ELSE
... not in developer mode ...
END IF;
Pre-APEX 2.2 users have to use WWV_Flow.g_edit_cookie_session_id instead.
Labels: APEX hacks, tips
« ... Read full posting ... »
You have created an On-Demand Process to do an AJAX call from JavaScript, like Carl Backstrom showed it in an example, but the On-Demand Process doesn't return anything. What's wrong?
There can be multiple causes what can be wrong with an On-Demand Process. When you read the documentation you will notice that
Any failures of authentication, authorization, or process conditions do not result in visible error messages or other indicators of such failures and most often result in a blank page being displayed.
This also includes errors when the PL/SQL code of your On-Demand Process doesn't compile or when you specify a wrong process name. This is because of security concerns as Scott Spadafore confirmed in a recent posting.
So what to do if APEX doesn't tell us anything?
You first have to figure out where the problem of your On-Demand Process is.
- Has a wrong process name be specified?
- Does it raise an exception which causes a blank page?
- Does the PL/SQL code not compile?
The best way to test it is to start the On-Demand Process directly through the proper URL. It's much easier than calling it from some JavaScript code where you don't see what is returned.
Use the following syntax to call it:
f?p=application_id:0:session:APPLICATION_PROCESS=process_name:::item-names:item-values
Where:
- application_id is the application ID or alphanumeric alias
- session is the session ID (run your application and get it from the URL)
- APPLICATION_PROCESS=process_name is the keyword APPLICATION_PROCESS= followed by either the process ID or an alphanumeric name of an application-level process having a Process Point of On Demand
- item-names are a list of application- or page item names which you want to set. They are seperated by a comma
- item-values are a list of values corresponding to the item-names, also separated by a comma.
An example URL would be
http://apex.oracle.com/pls/otn/f?p=33231:0:1725326667635628:
APPLICATION_PROCESS=ApexLib_getLovResult:::APEXLIB_REFERENCE_TYPE,APEXLIB_REFERENCE_ID:ABC,123
The best way to get the correct process name is to copy-paste it from the process definition. It wouldn't be the first time that there is a type. :-)
Didn't work? The next step is to replace the existing PL/SQL code of the On-Demand Process with just the following code, to make sure that the call works at least.
HTP.p('Hallo world');
Didn't work, too? Then I have no clue neither what to do next in such a case. Check again the URL and process name if they are ok.
If you got the "Hallo world" then it seems that your PL/SQL code is raising an exception or the PL/SQL code is syntactically wrong.
To handle the first possibility add an exception handler around your code, to show us the exception which is raised by Oracle.
BEGIN
[...Here comes your existing code...]
EXCEPTION WHEN OTHERS THEN
HTP.p('Error: '||SQLERRM);
END;
Does it show an error message? With this error message you should have a hint what's going wrong in your code. If not, add some HTP.P calls for debugging purpose to your code.
If it still doesn't show an error message, than it seems that your PL/SQL code is syntactically wrong. Have you already tried to run/compile the code stand alone in SQL*Plus/Toad/SQL Developer?
Happy testing!
Labels: tips
« ... Read full posting ... »
 Is the build-in SVG charting not "sexy" enough for your managment? Recently I came across two threads on the OTN Apex forum where they where discussing using a flash charting solution instead. Denes Kubicek has put together a really nice "out of the box" solution, which can be used in your Apex application. Check out his demonstration application showing the possibilities of this flash charting solution in combination with Apex! I'm sure with this charts you will get a " WOW that's cool!" from your users and managers! Labels: chart, tips
« ... Read full posting ... »
If you are able to read german, check out the excellent APEX community website hosted by Carsten Czarski of Oracle Germany. There are a bunch of How-To articles, which walk you through complicated stuff by providing a very detailed description what to do. Piece of cake, as the Americans would say! :-) Every two weeks two new topics are covered. Last edition covers Exporting via commandline and About DBMS_SCHEDULER. This site is a must read for german APEX developers! Labels: Other blogs
« ... Read full posting ... »
If you have read my previous posting about Caution when using PL/SQL functions in SQL statements and a related thread on the OTN forum, you know that using the V or NV function in your SQL-where clause can have a performance impact on your query. I have created wrapper functions for V and NV which use the DETERMINISTIC optimizer hint, so that the optimizer/query engine just calls them once for the query and not for every row.
I have also added a DV function which is simular to NV. It will return the value as date.
Installation instructions can be found in the script header.
NOTE: This drop in replacement may improve performance, but it would be best if you avoid the V function in your SQL statement, always use the bind variable syntax (:PX_VARIABLE) instead! That's is the best for the optimizer and it works with all Oracle DB versions.
DATABASE VERSION: I was just informed by Tom Kyte that
10gr2 supports deterministic as an optimization in SQL for the first
time. Prior to 10gr2 - deterministic was all about function based indexes only - the SQL engine ignored it.
So only if you have a 10.2.x database, this drop in replacement will give you a speed up of your query.
UPDATE: Seems that my link for the file doesn't work. Added the content of the script to the posting.
--******************************************************************************
--
-- PROJECT: Apex Library
--
-- FILE: ApexLib_V_functions_replacement.sql
--
-- DESCRIPTION:
--
-- This script contains a drop in replacement for the APEX functions
-- V and NV which are used to read the value of an APEX item.
--
-- Why do I need a replacement for them? Checkout my blog posting at
-- http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- and the OTN forum discussion at
-- http://forums.oracle.com/forums/thread.jspa?threadID=445423&messageID=1576799
--
-- This script also contains a DV function (for date) to complete the existing
-- V (for string) and NV functions (for number).
--
-- NOTE:
--
-- The package has to be loaded into the schema where your application tables
-- are located. That should be the same schema as it is specified by
-- "First Schema Provisioned" at "Manage Workspaces\Workspace Details".
-- => search for "First Schema Provisioned" in the online help.
--
-- If you use a other version than APEX 2.2.x then you have to replace the
-- schema FLOWS_020200 with the schema your version is using (eg FLOWS_020000)
--
-- SUPPORTED APEX VERSIONS:
--
-- 2.x
--
-- AUTHORS:
--
-- PW: Patrick Wolf (http://inside-apex.blogspot.com/)
--
-- SVN HEADER:
--
-- $Id: ApexLib_V_functions_patch.sql 140 2007-02-08 19:26:56Z patrick_wolf $
--
--******************************************************************************
--
CREATE OR REPLACE FUNCTION V
( p_item IN VARCHAR2
, p_flow IN NUMBER := NULL
, p_scope IN VARCHAR2 := 'SESSION_AND_USER'
, p_escape IN VARCHAR2 := 'N'
)
RETURN VARCHAR2 DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX V function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
RETURN FLOWS_020200.V
( p_item => p_item
, p_flow => p_flow
, p_scope => p_scope
, p_escape => p_escape
);
END V;
/
--
CREATE OR REPLACE FUNCTION NV
( p_item IN VARCHAR2
)
RETURN NUMBER DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX NV function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
RETURN TO_NUMBER(FLOWS_020200.V(p_item));
END NV;
/
--
CREATE OR REPLACE FUNCTION DV
( p_item IN VARCHAR2
, p_format_mask IN VARCHAR2 := NULL
)
RETURN DATE DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX V function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
IF p_format_mask IS NOT NULL
THEN
RETURN TO_DATE(FLOWS_020200.V(p_item), p_format_mask);
ELSE
RETURN TO_DATE(FLOWS_020200.V(p_item));
END IF;
END DV;
/
Labels: APEX hacks, ApexLib, performance, tips
« ... Read full posting ... »
 It has already been demonstrated at Oracle Open World 2006 and not it's here! An evaluation release has been uploaded to OTN. Check it out at OTN - APEX Migration Workshop. There is also an interesting blog run by Donal Daly who is one of the developers of the migration workshop. Labels: migration
« ... Read full posting ... »
|
Just read Dimitri Gielis posting that he has integrated the ApexLib Framework into his DG Tournament application.
