An enhancement request I was assigned was worded thus:
“User will optionally enter the Phone number (IF the phone was blank the system will default the store’s area code).”
I interpret this to mean that the Customer Phone number (land line) field should remain optional, but if entered, it should check if the local area code had been entered, and if not, default it according to the local store’s area code. We can assume that the area code has already been entered if the phone number starts with a zero (0).
This is for a retail chain with stores throughout Australia and New Zealand, and the Apex session knows the operator’s store ID. I can look up the country code and phone number for their store with a simple query, which will return values such as (these are just made up examples):
Country AU, Phone: +61 8 9123 4567 – area code should be 08
Country AU, Phone: 08 91234567 – area code should be 08
Country AU, Phone: +61 2 12345678 – area code should be 02
Country AU, Phone: 0408 123 456 – no landline area code
Country NZ, Phone: +64 3 123456 – area code should be 03
Country NZ, Phone: 0423 456 121 – area code should be 04
They only want to default the area code for landlines, so if the store’s phone number happens to be a mobile phone number it should not do any defaulting.
Step 1: create a database function (in a database package, natch) to return the landline area code for any given store ID.
FUNCTION get_store_landline_area_code (p_store_id IN VARCHAR2) RETURN VARCHAR2 IS v_area_code VARCHAR2(2); v_country_code stores_vw.country_code%TYPE; v_telephone_number stores_vw.telephone_number%TYPE; BEGIN IF p_store_code IS NOT NULL THEN BEGIN SELECT country_code ,telephone_number INTO v_country_code ,v_telephone_number FROM stores_vw WHERE store_id = p_store_id; v_area_code := CASE -- Australian International land line WHEN p_country_code = 'AU' AND REGEXP_LIKE(p_telephone_number, '^\+61( ?)') --e.g. +61 8 9752 6100 THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1) -- Australian Local land line WHEN p_country_code = 'AU' AND REGEXP_LIKE(p_telephone_number, '^0') THEN SUBSTR(p_telephone_number, 1, 2) -- New Zealand International land line WHEN p_country_code = 'NZ' AND REGEXP_LIKE(p_telephone_number, '^\+64( ?)') -- e.g. +64 3 1234 567 THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1) -- New Zealand Local land line WHEN p_country_code = 'NZ' AND REGEXP_LIKE(p_telephone_number, '^0') THEN SUBSTR(p_telephone_number, 1, 2) ELSE NULL END; EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN NULL; END; END IF; RETURN v_area_code; END get_store_landline_area_code;
Step 2: add a Dynamic Action to prepend the area code to the phone number, if it wasn’t entered already:
Selection Type: Item(s)
$v("P1_CUSTOMER_PHONE_NUMBER").length > 0 && $v("P1_CUSTOMER_PHONE_NUMBER").charAt(0) != "0"
True Action: Set Value
Set Type: PL/SQL Expression
my_util_pkg.get_store_landline_area_code(:F_USER_STORE_ID) || :P1_CUSTOMER_PHONE_NUMBER
Now, when the user types in a local land line but forget the prefix, the system will automatically add it in as soon as they tab out of the field. If the phone number field is unchanged, or is left blank, this will do nothing.
It assumes that the customer’s phone number uses the same prefix as the store, which in most cases will be true. Ultimately the user will still need to check that the phone number is correct for the customer.
I recently was working on an application in Apex 4.2.1.00.08, where the application had several pages with Interactive Reports.
On all these pages, the IR worked fine – except for one crucial page, where the IR’s action menu didn’t work (Select Columns, for example, showed a little circle instead of the expected shuttle region; all the column headings menus would freeze the page; and other issues).
In Console I could see the following errors get raised (depending on which IR widget I tried):
Uncaught SyntaxError: Unexpected token ) desktop_all.min.js?v=4.2.1.00.08:14 $u_evaldesktop_all.min.js?v=4.2.1.00.08:14 _Return widget.interactiveReport.min.js?v=4.2.1.00.08:1 b.onreadystatechange desktop_all.min.js?v=4.2.1.00.08:15
Uncaught TypeError: Object #<error> has no method 'cloneNode' desktop_all.min.js?v=4.2.1.00.08:14 dhtml_ShuttleObject desktop_all.min.js?v=4.2.1.00.08:14 _Return widget.interactiveReport.min.js?v=4.2.1.00.08:1 b.onreadystatechange desktop_all.min.js?v=4.2.1.00.08:15
Uncaught TypeError: Cannot read property 'undefined' of undefined widget.interactiveReport.min.js?v=4.2.1.00.08:1 dialog.column_check widget.interactiveReport.min.js?v=4.2.1.00.08:1 _Return widget.interactiveReport.min.js?v=4.2.1.00.08:1 b.onreadystatechange desktop_all.min.js?v=4.2.1.00.08:15
The one thing in common was that my IR also had a Display Condition on it. In my case, the condition was based on an application item, not REQUEST. I removed the condition, and the problem went away.
I’ve tried to make a reproducible test case with a fresh application, but unfortunately with no success – which means I haven’t yet isolated the actual cause of the issue. A PL/SQL condition like “1=1″ doesn’t reproduce the problem. If I have a PL/SQL Expression like “:P1_SHOW = ‘Y'”, or a Value of Item / Column in Expression 1 = Expression 2 with a similar effect, the problem is reproduced – but only in this application.
As a workaround I’ve used a Dynamic Action to hide the IR on page load if required.
Update: thanks Christian for pointing out that I mistook this – it’s not CLIENT_INFO that I was using, but CLIENT_IDENTIFIER – and that behaviour hasn’t changed.
I’ve just gotten around to reading the Patch Set Notes for Apex 4.2.3, and noticed this bit:
8.5 Changes in How Oracle Application Express Populates CLIENT_INFO in V$SESSION and GV$SESSION
The Oracle Application Express 4.2.2.00.11 patch set changes how Application Express populates the CLIENT_INFO value in V$SESSION and GV$SESSION. The new information in this field is workspace ID, followed by colon (:), followed by the authenticated username.
Tip: You may have to adapt database instance monitoring scripts which interpret CLIENT_INFO and expect the previous content for Oracle Application Express sessions (username ‘:’ workspace id).
I have no idea why the patch set notes talk about “workspace id” here, since as far as I can tell, Apex actually puts the session ID there. I haven’t tested this in 4.2.3 yet though. Anyone care to verify this for me?
Because it’s a PL/SQL expression, the Apex engine must execute this as dynamic PL/SQL – requiring a parse/execute/fetch. This might take maybe 0.03 seconds or so. If there’s only one condition like this on a page, it won’t make any difference. But if there are 50 conditions on a page, it can make a difference to the overall page performance – adding up to 1 whole second or more to the page request, which can be noticeable.
The better alternative is to use the condition type Value of Item / Column in Expression 1 = Expression 2, e.g.:
AUSOUG is holding a series of conferences this year right across the country – starting in Sydney on 15-16 August, touring the other major city centres, and ending in Perth on 12-13 November.
The Perth program is still being finalized but the lineup is looking good. You can see the current list here: http://www.ausoug.org.au/insync13/insync13-perth-program.html
I’ll be talking about Oracle Virtual Private Database or RLS and its use in Apex applications. I’ve made good use of this technology in a recent project which is now live and I’m looking forward to presenting what I’ve learned. Abstract
Make sure you register soon – pre-registrations close soon for some locations.
UPDATE: The Perth program is now published: INSYNC13_Program_Perth.pdf
UPDATE 2: The slide deck if you’re interested can be seen here.
If your schemas are like those I deal with, almost every table has a doppelgänger which serves as a journal table; an “after insert, update or delete” trigger copies each and every change into the journal table. It’s a bit of a drag on performance for large updates, isn’t it?
I was reading through the docs (as one does) and noticed this bit:
Scenario: You want to record every change to hr.employees.salary in a new table, employee_salaries. A single UPDATE statement will update many rows of the table hr.employees; therefore, bulk-inserting rows into employee.salaries is more efficient than inserting them individually.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-3. You do not need a BEFORE STATEMENT section to initialize idx or salaries, because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
The example shows how to use a compound trigger to not only copy the records to another table, but to do so with a far more efficient bulk insert. Immediately my journal table triggers sprang to mind – would this approach give me a performance boost?
The answer is, yes.
My test cases are linked below – emp1 is a table with an ordinary set of triggers, which copies each insert/update/delete into its journal table (emp1$jn) individually for each row. emp2 is a table with a compound trigger instead, which does a bulk insert of 100 journal entries at a time.
I ran a simple test case involving 100,000 inserts and 100,000 updates, into both tables; the first time, I did emp1 first followed by emp2; in the second time, I reversed the order. From the results below you’ll see I got a consistent improvement, shaving about 4-7 seconds off of about 21 seconds, an improvement of 19% to 35%. This is with the default value of 100 for the bulk operation; tweaking this might wring a bit more speed out of it (at the cost of using more memory per session).
Of course, this performance benefit only occurs for multi-row operations; if your application is only doing single-row inserts, updates or deletes you won’t see any difference in performance. However, I still think this method is neater (only one trigger) than the alternative so would recommend. The only reason I wouldn’t use this method is if my target might potentially be a pre-11g database, which doesn’t support compound triggers.
Here are the test case scripts if you want to check it out for yourself:
Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options insert emp1 (test run #1) 100000 rows created. Elapsed: 00:00:21.19 update emp1 (test run #1) 100000 rows updated. Elapsed: 00:00:21.40 insert emp2 (test run #1) 100000 rows created. Elapsed: 00:00:16.01 update emp2 (test run #1) 100000 rows updated. Elapsed: 00:00:13.89 Rollback complete. insert emp2 (test run #2) 100000 rows created. Elapsed: 00:00:15.94 update emp2 (test run #2) 100000 rows updated. Elapsed: 00:00:16.60 insert emp1 (test run #2) 100000 rows created. Elapsed: 00:00:21.01 update emp1 (test run #2) 100000 rows updated. Elapsed: 00:00:20.48 Rollback complete.
And here, in all its glory, is the fabulous compound trigger:
CREATE OR REPLACE TRIGGER emp2$trg FOR INSERT OR UPDATE OR DELETE ON emp2 COMPOUND TRIGGER FLUSH_THRESHOLD CONSTANT SIMPLE_INTEGER := 100; TYPE jnl_t IS TABLE OF emp2$jn%ROWTYPE INDEX BY SIMPLE_INTEGER; jnls jnl_t; rec emp2$jn%ROWTYPE; blank emp2$jn%ROWTYPE; PROCEDURE flush_array (arr IN OUT jnl_t) IS BEGIN FORALL i IN 1..arr.COUNT INSERT INTO emp2$jn VALUES arr(i); arr.DELETE; END flush_array; BEFORE EACH ROW IS BEGIN IF INSERTING THEN IF :NEW.db_created_by IS NULL THEN :NEW.db_created_by := NVL(v('APP_USER'), USER); END IF; ELSIF UPDATING THEN :NEW.db_modified_on := SYSDATE; :NEW.db_modified_by := NVL(v('APP_USER'), USER); :NEW.version_id := :OLD.version_id + 1; END IF; END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN rec := blank; IF INSERTING OR UPDATING THEN rec.id := :NEW.id; rec.name := :NEW.name; rec.db_created_on := :NEW.db_created_on; rec.db_created_by := :NEW.db_created_by; rec.db_modified_on := :NEW.db_modified_on; rec.db_modified_by := :NEW.db_modified_by; rec.version_id := :NEW.version_id; IF INSERTING THEN rec.jn_action := 'I'; ELSIF UPDATING THEN rec.jn_action := 'U'; END IF; ELSIF DELETING THEN rec.id := :OLD.id; rec.name := :OLD.name; rec.db_created_on := :OLD.db_created_on; rec.db_created_by := :OLD.db_created_by; rec.db_modified_on := :OLD.db_modified_on; rec.db_modified_by := :OLD.db_modified_by; rec.version_id := :OLD.version_id; rec.jn_action := 'D'; END IF; rec.jn_timestamp := SYSTIMESTAMP; jnls(NVL(jnls.LAST,0) + 1) := rec; IF jnls.COUNT >= FLUSH_THRESHOLD THEN flush_array(arr => jnls); END IF; END AFTER EACH ROW; AFTER STATEMENT IS BEGIN flush_array(arr => jnls); END AFTER STATEMENT; END emp2$trg;
Just to be clear: it’s not that it’s a compound trigger that impacts the performance; it’s the bulk insert. However, using the compound trigger made the bulk operation much simpler and neater to implement.
UPDATE 14/08/2014: I came across a bug in the trigger which caused it to not flush the array when doing a MERGE. I found I had to pass the array as a parameter internally.
If you’re in Perth on Wednesday the 26th, come for breakfast at the Oracle offices and hear me talk about my experiences with the PL/SQL Function Result Cache.
More details here: www.ausoug.org.au/cms/rest/event/1936
I love the Apex UI, it makes development so much easier and more convenient – and makes it easy to impress clients when I can quickly fix issues right there and then, using nothing but their computer and their browser, no additional software tools needed.
However, at my main client they have a fairly strict “scripted releases only” policy which is a really excellent policy – deployments must always be provided as a script to run on the command line. This makes for less errors and a little less work for the person who runs the deployment.
In Apex it’s easy to create deployment scripts that will run right in SQL*Plus. You can export a workspace, an application, images, etc. as scripts that will run in SQL*Plus with almost no problem. There’s just a few little things to be aware of, and that’s the subject of this post.
1. Setting the session workspace
Normally if you log into Apex and import an application export script, it will be imported without problem. Also, if you log into SQL*Plus and try to run it, it will work fine as well.
The only difference comes if you want to deploy it into a different workspace ID to the one the application was exported from – e.g. if you want to have two workspaces on one database, one for dev, one for test, when you log into your test schema and try to run it, you’ll see something like this:
SQL> @f118.sql APPLICATION 118 - My Wonderful App Set Credentials... Check Compatibility... Set Application ID... begin * ERROR at line 1: ORA-20001: Package variable g_security_group_id must be set. ORA-06512: at "APEX_040100.WWV_FLOW_API", line 73 ORA-06512: at "APEX_040100.WWV_FLOW_API", line 342 ORA-06512: at line 4
Side note: if you’re using Windows, the SQL*Plus window will disappear too quickly for you to see the error (as the generated apex script sets it to exit on error) – so you should SPOOL to a log file to see the output.
To fix this issue, you need to run a little bit of PL/SQL before you run the export, to override the workspace ID that the script should use:
declare v_workspace_id NUMBER; begin select workspace_id into v_workspace_id from apex_workspaces where workspace = 'TESTWORKSPACE'; apex_application_install.set_workspace_id (v_workspace_id); apex_util.set_security_group_id (p_security_group_id => apex_application_install.get_workspace_id); apex_application_install.set_schema('TESTSCHEMA'); apex_application_install.set_application_id(119); apex_application_install.generate_offset; apex_application_install.set_application_alias('TESTAPP'); end; /
This will tell the Apex installer to use a different workspace – and a different schema, application ID and alias as well, since 118 already exists on this server. If your app doesn’t have an alias you can omit that last step. Since we’re changing the application ID, we need to get all the other IDs (e.g. item and button internal IDs) throughout the application changed as well, so we call generate_offset which makes sure they won’t conflict.
2. Installing Images
This is easy. Same remarks apply as above if you’re installing the image script into a different workspace.
3. Installing CSS Files
If you export your CSS files using the Apex export facility, these will work just as well as the above, and the same considerations apply if you’re installing into a different workspace.
If you created your CSS export file manually using Shared Components -> Cascading Style Sheets and clicking on your stylesheet and clicking “Display Create File Script“, you will find it doesn’t quite work as well as you might expect. It does work, except that the file doesn’t include a COMMIT at the end. Which normally wouldn’t be much of a problem, until you discover late that the person deploying your scripts didn’t know they should issue a commit (which, of course, would have merely meant the file wasn’t imported) – and they didn’t actually close their session straight away either, but just left it open on their desktop while they went to lunch or a meeting or something.
This meant that when I sent the test team onto the system, the application looked a little “strange”, and all the text was black instead of the pretty colours they’d asked for – because the CSS file wasn’t found. And when I tried to fix this by attempting to re-import the CSS, my session hung (should that be “hanged”? or “became hung”?) – because the deployment person’s session was still holding the relevant locks. Eventually they committed their session and closed it, and the autocommit nature of SQL*Plus ended up fixing the issue magically for us anyway. Which made things interesting the next day as I was trying to work out what had gone wrong, when the system was now working fine, as if innocently saying to me, “what problem?”.
4. A little bug with Data Load tables
We’re on Apex 4.1.1 If you have any CSV Import function in your application using Apex’s Data Loading feature, if you export the application from one schema and import into another schema, you’ll find that the Data Load will simply not work, because the export incorrectly hardcodes the owner of the data load table in the call to create_load_table. This bug is described here: https://forums.oracle.com/forums/thread.jspa?messageID=10307103� and apparently there’s a patch for it.
wwv_flow_api.create_load_table( p_id =>4846012021772170+ wwv_flow_api.g_id_offset, p_flow_id => wwv_flow.g_flow_id, p_name =>'IMPORT_TABLE', p_owner =>'MYSCHEMA', p_table_name =>'IMPORT_TABLE', p_unique_column_1 =>'ID', p_is_uk1_case_sensitive =>'Y', p_unique_column_2 =>'', p_is_uk2_case_sensitive =>'N', p_unique_column_3 =>'', p_is_uk3_case_sensitive =>'N', p_wizard_page_ids =>'', p_comments =>'');
The workaround I’ve been using is, before importing into a different schema, I just edit the application script to fix the p_owner in the calls to wwv_flow_api.create_load_table.
5. Automating the Export
I don’t know if this is improved in later versions, but at the moment you can only export Applications using the provided API – no other objects (such as images or CSS files). Just a sample bit of code (you’ll need to put other bits around this to do what you want with the clob – e.g. my script spits it out to serverout so that SQL*Plus will write it to a sql file):
l_clob := WWV_FLOW_UTILITIES.export_application_to_clob (p_application_id => &APP_ID. ,p_export_ir_public_reports => 'Y' ,p_export_ir_private_reports => 'Y' ,p_export_ir_notifications => 'Y' );
That’s all my tips for scripting Apex deployments for now. If I encounter any more I’ll add them here.
Related: “What’s the Difference” – comparing exports to find diffs on an Apex application – http://blog.sydoracle.com/2011/11/whats-difference.html