Jeff Kemp

Subscribe to Jeff Kemp feed Jeff Kemp
Oracle Database: Get it Right
Updated: 4 hours 33 min ago

Apex Plugin: Password Strength Estimator

Tue, 2017-08-15 23:26

I needed a simple password strength prompt for users when they need to create or change their password on my website. After a bit of Googling I found the “Low-Budget Password Strength Estimator” which is supposedly used by Dropbox, so you know it’s good :)

This simple javascript library runs entirely within the client’s browser, and when presented with a candidate password, gives a score from 0 (very poor) to 4 (very good). It can also return extra feedback, including a warning message for poor passwords, as well as suggestions for making a password more secure.

preview-verypoor

So I’ve created a very simple Dynamic Action plugin (try the demo here) that allows you to add this functionality to any item on your page. You can specify a minimum length for the password, and can override the default messages for each score. You can also select whether or not the feedback warnings or suggestions are shown.

preview-poor

It seems to catch a lot of poor passwords, including ones comprising common words and names, and ones involving a simple sequence or repetition.

preview-good

preview-strong

Obviously it’s only really useful for password entry fields; but don’t use it on your Login page!

Download from: https://github.com/jeffreykemp/jk64-plugin-passwordstrength


Filed under: APEX, Oracle Tagged: APEX, passwords, plug-ins

Apex Reports: One Link, Multiple Destinations

Wed, 2017-08-02 01:22

Every Interactive Report has an optional set of “Link” attributes that allow you to specify the location where the user should be redirected if they click a link next to a record in the report. You can choose “Link to Custom Target” and use the Link Builder to easily specify the target application, page, item values to pass, and other attributes.

linkbuilder1

What if the report combines different entities, and you need to direct the user to a different page depending on the type of entity? Or, if you need to direct the user to a different page with different attributes depending on the status of the record?

One method is to generate the URL in the report query using apex_page.get_url (Apex 5+) or apex_util.prepare_url (Apex 4 or earlier), or (God forbid) you could generate the url yourself using string concatenation.

A more declarative solution is to instead use Apex page redirects. This solution involves the following:

  1. Add some hidden items to the page to store the parameters for each target page;
  2. Add a Branch to the page for each target page; and
  3. Add a Request to the link to signal the page that a redirect has been requested.

Here’s an example. My page 550 has an interactive report which combines TI records with TRQ records (both of which have a very similar structure). If the user clicks on a TI record they should be redirected to p510 with the corresponding TI_ID, and if they click on a TRQ record they should be redirected to p305 with the corresponding TRQ_ID.

Here’s the link attributes for this report:

linkbuilder2

Notice that the page now redirects back to itself with the request set to “GOTO_TARGET”, along with the IDs required. My report query has been constructed so that every record will only have a TI_ID or a TRQ_ID, never both at the same time; so the link will ensure that only one of the P550_GOTO_xxx_ID values will be set.

The page then just needs two Branches: one for each target. Conditions on each branch ensures they only get activated if the request has been set, and the branch is selected based on which “GOTO ID” item has been set:

branch1

branch2

For a normal navigation to this report (e.g. from another page), the request should be blank (or some other value) so none of the Branches should be triggered.

For a relatively simple scenario like this, I like the declarative approach. Each branch can take advantage of the full range of Apex features such as the link builder, security conditions, build options, etc.

Note: this method works just as well for Classic report links as well.

The thing to be mindful of is that the order of the branches, and the condition on each branch, must be carefully selected so that the correct branch is activated in each situation. I’ve shown a simple example which works because I have ensured that only one of the ID parameters is set at the same time. If a record has both IDs, the condition on the first branch “GOTO_TARGET (TI_ID)” will evaluate to True and it will be used, regardless of what GOTO_TRQ_ID was set to.

If there were numerous potential destination pages, with a large number of parameters to pass, I might choose the apex_page.get_url method instead.

Related


Filed under: APEX Tagged: APEX, tips-&-tricks

Using LetsEncrypt on Amazon Linux

Thu, 2017-07-27 22:00

sslapexFor a number of years now I’ve been using LetsEncrypt to provide free SSL certificates for the Apex applications I provide. These certificates last for 90 days and are renewed automatically by a simple script on my server.

By the way – if you’re not already using https for your public-facing Apex applications, you should, okay – even if your site doesn’t have data entry.

Each LetsEncrypt certificate can cover multiple subdomains. They don’t currently support wildcard domains (e.g. *.example.com) but they are planning to add this next year (2018).

To install LetsEncrypt I ran the following on my Amazon Linux instance (note – this is my web server, not my database server):

cd /opt
git clone https://github.com/letsencrypt/letsencrypt
cd letsencrypt
./letsencrypt-auto -v --debug

(when prompted at the last step I typed “c” to cancel the subsequent steps)

It wasn’t easy at first because I got a number of errors which I’d google (or search the community forum) and eventually find reasonable answers. I’ve had to reinstall a number of times, as the OS is patched regularly and certbot is updated from time to time.

I use Apache to provide about a dozen virtual hosts and therefore the automated installation option didn’t work for me. Instead, I’ve got lines like these in each VirtualHost:

<VirtualHost *:443>
 ServerName subdomain.mydomain.com
 ServerAlias subdomain.mydomain.com
 SSLEngine on
 SSLCertificateFile "/etc/letsencrypt/live/mydomain.com/cert.pem"
 SSLCertificateKeyFile "/etc/letsencrypt/live/mydomain.com/privkey.pem"
 SSLCertificateChainFile "/etc/letsencrypt/live/mydomain.com/chain.pem"
 ...
</VirtualHost>

To register a certificate I used the following command as root (all one line):

/opt/letsencrypt/letsencrypt-auto
certonly --webroot -w /var/www/html
-d mydomain.com,www.mydomain.com,sub1.mydomain.com,sub2.mydomain.com

This generates all the keys and certificates and stores them locally. No private keys ever leave the server. This command is using SAN to combine multiple subdomains in one certificate. I run this command again separately for each domain.

To renew all my certificates I run the following command as root:

/opt/letsencrypt/letsencrypt-auto renew -n --no-self-upgrade
service httpd restart

This will automatically skip any certificates that are not yet due to expire. I’ve put the above script in a file which is run by cron on a monthly basis.

0 20 1 * * /path-to-script/renewall.sh

To get usage info on the options:

/opt/letsencrypt/letsencrypt-auto --help

Since it’s free, one cannot expect support from LetsEncrypt directly if there are issues; however, there is an active LetsEncrypt support community which can be helpful at times.

But it’s certainly made a big difference to my bottom line, and provided a bit of peace-of-mind to my users.

Related


Filed under: Other Tagged: amazon-web-services, letsencrypt, server-config, SSL

Compiling views: when the FORCE fails you

Sun, 2017-04-02 20:01

Darth-Vader-selfieThe order in which your deployment scripts create views is important. This is a fact that I was reminded of when I had to fix a minor issue in the deployment of version #2 of my application recently.

Normally, you can just generate a create or replace force view script for all your views and just run it in each environment, then recompile your schema after they’re finished – and everything’s fine. However, if views depend on other views, you can run into a logical problem if you don’t create them in the order of dependency.

Software Release 1.0

create table t (id number, name varchar2(100));
create or replace force view tv_base as
select t.*, 'hello' as stat from t;
create or replace force view tv_alpha as
select t.* from tv_base t;

desc tv_alpha;
Name Null Type
---- ---- -------------
ID        NUMBER
NAME      VARCHAR2(100)
STAT      CHAR(5)

Here we have our first version of the schema, with a table and two views based on it. Let’s say that the tv_base includes some derived expressions, and tv_alpha is intended to do some joins on other tables for more detailed reporting.

Software Release 1.1

alter table t add (phone varchar2(10));
create or replace force view tv_alpha as
select t.* from tv_base t;
create or replace force view tv_base as
select t.*, 'hello' as stat from t;

Now, in the second release of the software, we added a new column to the table, and duly recompiled the views. In the development environment the view recompilation may happen multiple times (because other changes are being made to the views as well) – and nothing’s wrong. Everything works as expected.

However, when we run the deployment scripts in the Test environment, the “run all views” script has been run just once; and due to the way it was generated, the views are created in alphabetical order – so tv_alpha was recreated first, followed by tv_base. Now, when we describe the view, we see that it’s missing the new column:

desc tv_alpha;
Name Null Type
---- ---- -------------
ID        NUMBER
NAME      VARCHAR2(100)
STAT      CHAR(5)

Whoops. What’s happened, of course, is that when tv_alpha was recompiled, tv_base still hadn’t been recompiled and so it didn’t have the new column in it yet. Oracle internally defines views with SELECT * expanded to list all the columns. The view won’t gain the new column until we REPLACE the view with a new one using SELECT *. By that time, it’s too late for tv_alpha – it had already been compiled, successfully, so it doesn’t see the new column.

Lesson Learnt

What should we learn from this? Be wary of SELECT * in your views. Don’t get me wrong: they are very handy, especially during initial development of your application; but they can surprise you if not handled carefully and I would suggest it’s good practice to expand those SELECT *‘s into a discrete list of columns.

Some people would go so far as to completely outlaw SELECT *, and even views-on-views, for reasons such as the above. I’m not so dogmatic, because in my view there are some good reasons to use them in some situations.


Filed under: SQL Tagged: development, SQL

Show greyscale icon as red

Tue, 2017-03-14 03:03

I have an editable tabular form using Apex’s old greyscale edit link icons:

greyscale-icons

The users complained that they currently have to click each link to drill down to the detail records to find and fix any errors; they wanted the screen to indicate which detail records were already fine and which ones needed attention.

Since screen real-estate is limited here, I wanted to indicate the problems by showing a red edit link instead of the default greyscale one; since this application is using an old theme I didn’t feel like converting it to use Font Awesome (not yet, at least) and neither did I want to create a whole new image and upload it. Instead, I tried a CSS trick to convert the greyscale image to a red shade.

I used this informative post to work out what I needed: https://css-tricks.com/color-filters-can-turn-your-gray-skies-blue/

WARNING: Unfortunately this trick does NOT work in IE (tested in IE11). Blast.

Firstly, I added a column to the underlying query that determines if the error needs to be indicated or not:

select ...,
       case when {error condition}
       then 'btnerr' end as year1_err
from mytable...

I set the new column type to Hidden Column.

The link column is rendered using a Link-type column, with Link Text set to:

<img src="#IMAGE_PREFIX#e2.gif" alt="">

I changed this to:

<img src="#IMAGE_PREFIX#e2.gif" alt="" class="#YEAR1_ERR#">

What this does is if there is an error for a particular record, the class "btnerr" is added to the img tag. Rows with no error will simply have class="" which does nothing.

Now, to make the greyscale image show up as red, I need to add an SVG filter to the HTML Header in the page:

<svg style="display:none"><defs>
  <filter id="redshader">
    <feColorMatrix type="matrix"
      values="0.7 0.7 0.7 0 0
              0.2 0.2 0.2 0 0
              0.2 0.2 0.2 0 0
              0   0   0   1 0"/>
  </filter>
</defs></svg>

I made up the values for the R G B lines with some trial and error. The filter is applied to the buttons with the btnerr class with this CSS in the Inline CSS property of the page:

img.btnerr {filter:url(#redshader);}

The result is quite effective:

greyscale-colorize

But, as I noted earlier, this solution does not work in IE, so that’s a big fail.

NOTE: if this application was using the Universal Theme I would simply apply a simple font color style to the icon since it would be using a font instead of an image icon.


Filed under: APEX Tagged: APEX, CSS, tips-&-tricks

A nice Descending Index Range Scan

Thu, 2017-03-09 20:06

I’ve been aware of some of the ways that Oracle database optimises index accesses for queries, but I’m also aware that you have to test each critical query to ensure that the expected optimisations are taking effect.

I had this simple query, the requirement of which is to get the “previous status” for a record from a journal table. Since the journal table records all inserts, updates and deletes, and this query is called immediately after an update, to get the previous status we need to query the journal for the record most recently prior to the most recent record. Since the “version_id” column is incremented for each update, we can use that as the sort order.


select status_code
from (select rownum rn, status_code
      from   xxtim_requests$jn jn
      where  jn.trq_id = :trq_id
      order by version_id desc)
where rn = 2;

The xxtim_requests$jn table has an ordinary index on (trq_id, version_id). This query is embedded in some PL/SQL with an INTO clause – so it will only fetch one record (plus a 2nd fetch to detect TOO_MANY_ROWS which we know won’t happen).

The table is relatively small (in dev it only has 6K records, and production data volumes are expected to grow very slowly) but regardless, I was pleased to find that (at least, in Oracle 12.1) it uses a nice optimisation so that it not only uses the index, it is choosing to use a Descending scan on it – which means it avoids a SORT operation, and should very quickly return the 2nd record that we desire.

index_scan_range_descending.PNG

It looks quite similar in effect to the “COUNT STOPKEY” optimisation you can see on “ROWNUM=1” queries. If this was a much larger table and this query needed to be faster or was being run more frequently, I’d probably consider appending status_code to the index in order to avoid the table access. In this case, however, I don’t think it’s necessary.


Filed under: SQL Tagged: oracle12c, SQL

IRs with Subscriptions that might not work

Thu, 2017-02-23 20:31

If you have an Interactive Report with the Subscription feature enabled, users can “subscribe” to the report, getting a daily email with the results of the report. Unfortunately, however, this feature doesn’t work as expected if it relies on session state – e.g. if the query uses bind variables based on page items to filter the records. In this case, the subscription will run the query with a default session state – Apex doesn’t remember what the page item values were when the user subscribed to the report.

This is a query I used to quickly pick out all the Interactive Reports that have the Subscription feature enabled but which might rely on session state to work – i.e. it relies on items submitted from the page, refers to a bind variable or to a system context:


select workspace, application_id, application_name,
page_id, region_name, page_items_to_submit
from apex_application_page_ir
where show_notify = 'Yes'
and (page_items_to_submit is not null
or regexp_like(sql_query,':[A-Z]','i')
or regexp_like(sql_query,'SYS_CONTEXT','i')
);

For these reports, I reviewed them and where appropriate, turned off the Subscription feature. Note that this query is not perfect and might give some false positives and negatives.


Filed under: Oracle

Quick Pick in Apex Report

Mon, 2017-01-30 02:08

I have an Interactive Report that includes some editable columns, and the users wanted to include some “quick picks” on these columns to make it easy to copy data from a previous period. The user can choose to type in a new value, or click the “quick pick” to quickly data-enter the suggested value.

example-report-quickpick

Normally, a simple page item can have a quick pick by setting the Show Quick Picks attribute on the item. This is not, however, available as an option when generating Apex items in a report.

To do this, I added code like the following to my report query:

SELECT ...
      ,APEX_ITEM.textarea(5,x.ytd_comments
         ,p_rows => 1
         ,p_cols => 30
         ,p_item_id => 'f05_'||to_char(rownum,'fm00000'))
       || case when x.prev_ytd_comments is not null
          then '<a href="javascript:$(''#'
            || 'f05_' || to_char(rownum,'fm00000')
            || ''').val('
            || apex_escape.js_literal(x.prev_ytd_comments)
            || ').trigger(''change'')">'
            || apex_escape.html(x.prev_ytd_comments)
            || '</a>'
          end
       as edit_ytd_comments
FROM my_report_view x;

This results in the following HTML code being generated:

html-report-quickpick

In the report definition, the EDIT_YTD_COMMENTS column has Escape Special Characters set to No. This runs a real risk of adding a XSS attack vector to your application, so be very careful to escape any user-entered data (such as prev_ytd_comments in the example above) before allowing it to be included. In this case, the user-entered data is rendered as the link text (so is escaped using APEX_ESCAPE.html) and also within some javascript (so is escaped using APEX_ESCAPE.js_literal).

So, if the data includes any characters that conflict with html or javascript, it is neatly escaped:

html-quickpick-bad

And it is shown on screen as expected, and clicking the link copies the data correctly into the item:

example-quickpick-bad

This technique should, of course, work with most of the different item types you can generate with APEX_ITEM.

Recommended further reading:


Filed under: APEX Tagged: apex-5.0, javascript, tips-&-tricks

Powerless Javascript

Thu, 2017-01-19 19:37

I was writing a small javascript function, part of which needed to evaluate 10 to the power of a parameter – I couldn’t remember what the exponentiation operator is in javascript so as usually I hit F12 and typed the following into the console:

10**3

jspower

Wrote and tested the code, checked in to source control. Job done.

A few days later we deployed a new release that included dozens of bug fixes into UAT for testing. Soon after a tester showed me a screen where a lot of stuff wasn’t looking right, and things that had been working for a long time was not working at all.

Developer: “It works fine on my machine.”

After some playing around on their browser I noted that it seemed half of the javascript code I’d written was not running at all. A look at their browser console revealed two things:

  1. they are using Internet Explorer 11
  2. a compilation error was accusing the line with the ** operator

The error meant that all javascript following that point in the file was never executed, causing the strange behaviour experienced by the testers.

A bit of googling revealed that the ** operator was only added to javascript relatively recently and was supported by Chrome 52 and Edge browser but not IE. So I quickly rewrote it to use Math.pow(n,m) and applied a quick patch to UAT to get things back on track.

I think there’s a lesson there somewhere. Probably, the lesson is something like “if you try drive-by javascript coding, you’re gonna have a bad time.”


Filed under: APEX Tagged: exponentiation, javascript, lesson-learned

Target=_Blank for Cards report template

Sat, 2016-12-24 02:20

cardsreport.PNGI wanted to use the “Cards” report template for a small report which lists file attachments. When the user clicks on one of the cards, the file should download and open in a new tab/window. Unfortunately, the Cards report template does not include a placeholder for extra attributes for the anchor tag, so it won’t let me add “target=_blank” like I would normally.

One solution is to edit the Cards template to add the extra placeholder; however, this means breaking the subscription from the universal theme.

As a workaround for this I’ve added a small bit of javascript to add the attribute after page load, and whenever the region is refreshed.

  • Set report static ID, e.g. “mycardsreport”
  • Add Dynamic Action:
    • Event = After Refresh
    • Selection Type = Region
    • Region = (the region)
  • Add True Action: Execute JavaScript Code
    • Code = $("#mycardsreport a.t-Card-wrap").attr("target","_blank"); (replace the report static ID in the selector)
    • Fire On Page Load = Yes

Note: this code affects all cards in the chosen report.


Filed under: APEX Tagged: APEX, javascript, jQuery, tips-&-tricks

Report Link: Save before Navigation

Sun, 2016-10-30 23:46

“We always click ‘Apply Changes’, then we click the button we actually wanted” – a user

Typically an Apex report with an “Open” or “Edit” icon will simply do an immediate navigation to the target page, passing the ID of the record to edit. When the user clicks this link, the page is not submitted and the user is instead redirected.

It’s easy to quickly build large and complex applications in Apex but it’s also very easy to build something that confuses and disorients your users. They only need one instance when something doesn’t work how they expected it to, and they will lose trust in your application.

For example: if most buttons save their changes, but one button doesn’t, they might not notice straight away, and then wonder what happened to their work. If you’re lucky, they will raise a defect notice and you can fix it. More likely (and worse), they’ll decide it’s their fault, and begrudgingly accept slow and unnecessary extra steps as part of the process.

You can improve this situation by taking care to ensure that everything works the way your users expect. For most buttons in an Apex page, this is easy and straightforward: just make sure the buttons submit the page. The only buttons that should do a redirect are those that a user should expect will NOT save the changes – e.g. a “Cancel” button.

For the example of an icon in a report region, it’s not so straightforward. The page might include some editable items (e.g. a page for editing a “header” record) – and if the user doesn’t Save their changes before clicking the report link their changes will be lost on navigation.

To solve this problem you can make the edit links first submit the page before navigating. The way I do this is as follows (in this example, the report query is on the “emp” table:

    1. Add a hidden item P1_EDIT_ID to the page
      • Set Value Protected to No
    2. Add something like this to the report query (without the newlines):
      'javascript:apex.submit(
        {request:''SAVE_EDIT_ROW'',
         set:{''P1_EDIT_ID'':''' || emp.rec_id || '''}
        })' AS edit_link
      • Set this new column to Hidden Column
    3. Modify the edit link Target:
      • Type = URL
      • URL = #EDIT_LINK#
    4. Add a Branch at point “After Processing”
      • Set the Target to the page to navigate to
      • Set the item for the record ID to &P1_EDIT_ID.
      • Set the Condition to the following PL/SQL Expression:
        :REQUEST='SAVE_EDIT_ROW' AND :P1_EDIT_ID IS NOT NULL
      • Make sure the branch is evaluated before any other branches (at least, any others that might respond to this request)
    5. Modify any existing Processing so that the request SAVE_EDIT_ROW will cause any changes on the page to be saved.

You can, of course, choose different item names and request names if needed (just update it in the code you entered earlier). For example, to make it work with the default Apex DML process you might need to use a request like “APPLY_CHANGES_EDIT_ROW”.

Now, when the user makes some changes to the form, then clicks one of the record Edit links, the page will first be submitted before navigating to the child row.

Adding buttons to Apex pages is easy. Making sure every last one of them does exactly what the user expects, nothing more, and nothing less, is the tricky part!


Filed under: Oracle Tagged: tips-&-tricks

Enhancement Request for SQL Developer for users of Logger

Mon, 2016-10-24 07:57

Juergen Schuster, who has been enthusiastically trying OraOpenSource Logger, raised an idea for the debug/instrumentation library requesting the addition of a standard synonym “l” for the package. The motive behind this request was to allow our PL/SQL code to remain easy to read, in spite of all the calls to logger sprinkled throughout that are needed for effective debugging and instrumentation.

In the judgement of some (myself included) the addition of the synonym to the standard package would run the risk of causing clashes on some people’s systems; and ensuring that Logger is installable on all systems “out of the box” should, I think, take precedence.

However, the readability of code is still an issue; so it was with that in mind that I suggested that perhaps an enhancement of our favourite development IDE would go a long way to improving the situation.

Therefore, I have raised the following enhancement request at the SQL Developer Exchange:

Logger: show/hide or dim (highlight) debug/instrumentation code

“The oracle open source Logger instrumentation library is gaining popularity and it would be great to build some specific support for it into SQL Developer, whether as a plugin or builtin. To enhance code readability, it would be helpful for PL/SQL developers to be able to hide/show, or dim (e.g. grey highlight) any code calling their preferred debug/instrumentation library (e.g. Logger).

“One way I expect this might work is that the Code Editor would be given a configurable list of oracle object identifiers (e.g. “logger”, “logger_logs”); any PL/SQL declarations or lines of code containing references to these objects would be greyed out, or be able to be rolled up (with something like the +/- gutter buttons).”

Mockup #1 (alternative syntax highlighting option):

loggerdim

Mockup #2 (identifier slugs in header bar to show/hide, with icons in the gutter showing where lines have been hidden):

loggerdim2.png

“Gold-plated” Option: add an option to the SQL Editor’s right-click context menu – on any identifier, select “Hide all lines with reference to this” and it adds the identifier to the list of things that are hidden!

If you like the idea (or at least agree with the motive behind it) please vote for it.


Filed under: Tools Tagged: enhancement, Logger, oracle-sql-developer

Restriction when column default is sequence.nextval

Mon, 2016-10-03 04:39

Oracle 12c introduced the ability to specify sequence.nextval as the default on a column, which is really nice – including the fact that it eliminates one of your excuses why you don’t decommission those old triggers.

Unfortunately it doesn’t work as you might expect if you use an INSERT ALL statement; it evaluates the default expression once per statement, instead of once per row.

Test case:


create sequence test_seq;

create table test_tab
( id number default test_seq.nextval primary key
, dummy varchar2(100) not null );

insert into test_tab (dummy) values ('xyz');

1 row inserted.

insert all
into test_tab (dummy) values ('abc')
into test_tab (dummy) values ('def')
select null from dual;

Error report -
SQL Error: ORA-00001: unique constraint
(SCOTT.SYS_C00123456) violated

A minor issue, usually, but something to be aware of – especially if you’re not in the habit of declaring your unique constraints to the database!


create sequence test_seq;

create table test_stupid_tab
( id number default test_seq.nextval
, dummy varchar2(100) not null );

insert into test_tab (dummy) values ('xyz');

1 row inserted.

insert all
into test_tab (dummy) values ('abc')
into test_tab (dummy) values ('def')
select null from dual;

2 rows inserted.

select * from test_tab;

i dummy
= =====
1 xyz
2 abc
2 def


Filed under: Oracle Tagged: gotcha, oracle12c, SQL

Must See: AUSOUG Connect 2016 Perth

Tue, 2016-09-06 00:49

banner_oaug-web

If you can get yourself to Perth in November, you must try to get to AUSOUG Connect. Here is my “Must See” list based on the current program (subject to change):

Monday 7 Nov

Tuesday 8 Nov

Lots of Apex goodies, a new (to me, at least) database IDE, plus plenty of solid Oracle database content – there’ll probably be a few conflicts leading to some decisions to make on the day.

I’m looking forward to a long-awaited return of the AUSOUG conference series – I hope to see you all there.


Filed under: AUSOUG Tagged: AUSOUG

Send SMS, MMS and Voice messages from Oracle PL/SQL

Wed, 2016-08-17 02:31

testing receipt of sms and mms

If you need to send almost any message to almost any phone from your Oracle database, and you want to use straight PL/SQL, you may want to consider using my Clicksend API.

  • SMS (Short Message Service)
  • MMS (Multimedia Message Service)
  • Text to Voice

I have released the first beta version of my Oracle PL/SQL API for Clicksend. Read the installation instructions, API reference and download the release from here:

https://github.com/jeffreykemp/clicksend-plsql-api

Sending an SMS is as simple as adding this anywhere in your code:

begin
  clicksend_pkg.send_sms
    (p_sender  => 'Mr Tester'
    ,p_mobile  => '+61411111111'
    ,p_message => 'Hi, message sent at '
     || to_char(systimestamp,'DD/MM/YYYY HH24:MI:SS.FF')
    );
  clicksend_pkg.push_queue;
  commit;
end;

All you need to do is signup for a Clicksend account. You’ll only be charged for messages actually sent, but they do require you to pay in advance – e.g. $20 gets you about 300 messages (Australian numbers). You can get test settings so that you can try it out for free.

I’ve been using Clicksend for years now, and have been satisfied with their service and the speed and reliability of getting messages to people’s mobiles. When I encountered any issues, a chat with their support quickly resolved them, and they were quick to offer free credits when things weren’t working out as expected.

If you want to send a photo to someone’s phone via MMS (although I’m not sure what the use-case for this might be), you need to first upload the image somewhere online, because the API only accepts a URL. In my case, I would use the Amazon S3 API from the Alexandria PL/SQL Library, then pass the generated URL to the clicksend API. There is a file upload feature that ClickSend provides, I plan to add an API call to take advantage of this which will make this seamless – and provide some file conversion capabilities as well.

begin
  clicksend_pkg.send_mms
    (p_sender  => 'Mr Tester'
    ,p_mobile  => '+61411111111'
    ,p_subject => 'testing mms'
    ,p_message => 'testing '
     || to_char(systimestamp,'DD/MM/YYYY HH24:MI:SS.FF')
    ,p_media_file_url =>
'https://s3-ap-southeast-2.amazonaws.com/jk64/jk64logo.jpg'
    );
  clicksend_pkg.push_queue;
  commit;
end;

You can send a voice message to someone (e.g. if they don’t have a mobile phone) using the Text to Voice API.

begin
  clicksend_pkg.send_voice
    (p_phone_no     => '+61411111111'
    ,p_message      => 'Hello. This message was sent on '
     || to_char(sysdate,'fmDay DD Month YYYY '
                || '"at" HH:MI am, SS "seconds"')
     || '. Have a nice day.'
    ,p_voice_lang   => 'en-gb' -- British English
    ,p_voice_gender => 'male'
    ,p_schedule_dt  => sysdate + interval '2' minute
    );
  clicksend_pkg.push_queue;
  commit;
end;

You have to tell the API what language the message is in. For a number of languages, you can specify the accent/dialect (e.g. American English, British English, or Aussie) and gender (male or female). You can see the full list here.

All calls to the send_sms, send_mms and send_voice procedures use Oracle AQ to make the messages transactional. It’s up to you to either COMMIT or ROLLBACK, which determines whether the message is actually sent or not. All messages go into a single queue.

You can have a message be scheduled at a particular point in time by setting the p_schedule_dt parameter.

The default installation creates a job that runs every 5 minutes to push the queue. You can also call push_queue directly in your code after calling a send_xxx procedure. This creates a job to push the queue as well, so it won’t interfere with your transaction.

All messages get logged in a table, clicksend_msg_log. The log includes a column clicksend_cost which allows you to monitor your costs. To check your account balance, call get_credit_balance.

Please try it out if you can and let me know of any issues or suggestions for improvement.


Filed under: Oracle, PL/SQL Tagged: Clicksend, PL/SQL

File Upload Improvements in Apex 5.1

Sun, 2016-07-31 22:28

Warning: this is based on the Apex 5.1 Early Adopter and details may change.

file_upload_5_1_ea

The standard File Upload item type is getting a nice little upgrade in Apex 5.1. By simply changing attributes on the item, you can allow users to select multiple files (from a single directory) at the same time.

In addition, you can now restrict the type of file they may choose, according to the MIME type of the file, e.g. image/jpg. This file type restriction can use a wildcard, e.g. image/*, and can have multiple patterns separated by commas, e.g. image/png,application/pdf.

file_upload_5_1_ea_demo

Normally, to access the file that was uploaded you would query APEX_APPLICATION_TEMP_FILES with a predicate like name = :P1_FILE_ITEM. If multiple files are allowed, however, the item will be set to a comma-delimited list of names, so the suggested code to get the files is:

declare
  arr apex_global.vc_arr2;
begin
  arr := apex_util.string_to_table(:P1_MULTIPLE_FILES);
  for i in 1..arr.count loop
    select t.whatever
    into   your_variable
    from   apex_application_temp_files t
    where  t.name = arr(i);
  end loop;
end;

You can play with a simple demo here: https://apexea.oracle.com/pls/apex/f?p=UPLOAD_DEMO&cs=JK64 (login as demo / demodemo).

If you want to support drag-and-drop, image copy&paste, load large files asynchronously, or restrict the maximum file size that may be uploaded, you will probably want to consider a plugin instead, like Daniel Hochleitner’s DropZone.


Filed under: APEX Tagged: APEX, apex-5.1

Interactive Grids (Apex 5.1 EA) and TAPIs

Wed, 2016-06-29 22:37

DISCLAIMER: this article is based on Early Adopter 1.

event_types_ig

I’ve finally got back to looking at my reference TAPI Apex application. I’ve greatly simplified it (e.g. removed the dependency on Logger, much as I wanted to keep it) and included one dependency (CSV_UTIL_PKG) to make it much simpler to install and try. The notice about compilation errors still applies: it is provided for information/entertainment purposes only and is not intended to be a fully working system. The online demo for Apex 5.0 has been updated accordingly.

I next turned my attention to Apex 5.1 Early Adopter, in which the most exciting feature is the all-new Interactive Grid which may replace IRs and tabular forms. I have installed my reference TAPI Apex application, everything still works fine without changes.

I wanted my sample application to include both the old Tabular Forms as well as the new Interactive Grid, so I started by making copies of some of my old “Grid Edit” (tabular form) pages. You will find these under the “Venues” and “Event Types” menus in the sample application. I then converted the tabular form regions to Interactive Grids, and after some fiddling have found that I need to make a small change to my Apex API to suit them. The code I wrote for the tabular forms doesn’t work for IGs; in fact, the new code is simpler, e.g.:

PROCEDURE apply_ig (rv IN VENUES$TAPI.rvtype) IS
  r VENUES$TAPI.rowtype;
BEGIN
  CASE v('APEX$ROW_STATUS')
  WHEN 'I' THEN
    r := VENUES$TAPI.ins (rv => rv);
    sv('VENUE_ID', r.venue_id);
  WHEN 'U' THEN
    r := VENUES$TAPI.upd (rv => rv);
  WHEN 'D' THEN
    VENUES$TAPI.del (rv => rv);
  END CASE;
END apply_ig;

You may notice a few things here:

(1) APEX$ROW_STATUS for inserted rows is ‘I’ instead of ‘C’; also, it is set to ‘D’ (unlike under tabular forms, where it isn’t set for deleted rows).

(2) After inserting a new record, the session state for the Primary Key column(s) must be set if the insert might have set them – including if the “Primary Key” in the region is ROWID. Otherwise, Apex 5.1 raises No Data Found when it tries to retrieve the new row.

(3) I did not have to make any changes to my TAPI at all :)

Here’s the example from my Event Types table, which doesn’t have a surrogate key, so we use ROWID instead:

PROCEDURE apply_ig (rv IN EVENT_TYPES$TAPI.rvtype) IS
  r EVENT_TYPES$TAPI.rowtype;
BEGIN
  CASE v('APEX$ROW_STATUS')
  WHEN 'I' THEN
    r := EVENT_TYPES$TAPI.ins (rv => rv);
    sv('ROWID', r.p_rowid);
  WHEN 'U' THEN
    r := EVENT_TYPES$TAPI.upd (rv => rv);
  WHEN 'D' THEN
    EVENT_TYPES$TAPI.del (rv => rv);
  END CASE;
END apply_ig;

Converting Tabular Form to Interactive Grid

The steps needed to convert a Tabular Form based on my Apex API / TAPI system are relatively straightforward, and only needed a small change to my Apex API.

  1. Select the Tabular Form region
  2. Change Type from “Tabular Form [Legacy]” to “Interactive Grid”
  3. Delete any Region Buttons that were associated with the Tabular form, such as CANCEL, MULTI_ROW_DELETE, SUBMIT, ADD
  4. Set the Page attribute Advanced > Reload on Submit = “Only for Success”
  5. Under region Attributes, set Edit > Enabled to “Yes”
  6. Set Edit > Lost Update Type = “Row Version Column”
  7. Set Edit > Row Version Column = “VERSION_ID”
  8. Set Edit > Add Row If Empty = “No”
  9. If your query already included ROWID, you will need to remove this (as the IG includes the ROWID automatically).
  10. If the table has a Surrogate Key, set the following attributes on the surrogate key column:
    Identification > Type = “Hidden”
    Source > Primary Key = “Yes”
  11. Also, if the table has a Surrogate Key, delete the generated ROWID column. Otherwise, leave it (it will be treated as the Primary Key by both the Interactive Grid as well as the TAPI).
  12. Set any columns Type = “Hidden” where appropriate (e.g. for Surrogate Key columns and VERSION_ID).
  13. Under Validating, create a Validation:
    Editable Region = (your interactive grid region)
    Type = “PL/SQL Function (returning Error Text)”
    PL/SQL = (copy the suggested code from the generated Apex API package) e.g.

        RETURN VENUES$TAPI.val (rv =>
          VENUES$TAPI.rv
            (venue_id     => :VENUE_ID
            ,name         => :NAME
            ,map_position => :MAP_POSITION
            ,version_id   => :VERSION_ID
            ));
        
  14. Under Processing, edit the automatically generated “Save Interactive Grid Data” process:
    Target Type = PL/SQL Code
    PL/SQL = (copy the suggested code from the generated Apex API package) e.g.

        VENUES$APEX.apply_ig (rv =>
          VENUES$TAPI.rv
            (venue_id     => :VENUE_ID
            ,name         => :NAME
            ,map_position => :MAP_POSITION
            ,version_id   => :VERSION_ID
            ));
        

I like how the new Interactive Grid provides all the extra knobs and dials needed to interface cleanly with an existing TAPI implementation. For example, you can control whether it will attempt to Lock each Row for editing – and even allows you to supply Custom PL/SQL to implement the locking. Note that the lock is still only taken when the page is submitted (unlike Oracle Forms, which locks the record as soon as the user starts editing it) – which is why we need to prevent lost updates:

Preventing Lost Updates

The Interactive Grid allows the developer to choose the type of Lost Update protection (Row Values or Row Version Column). The help text for this attribute should be required reading for any database developer. In my case, I might choose to turn this off (by setting Prevent Lost Updates = “No” in the Save Interactive Grid Data process) since my TAPI already does this; in my testing, however, it didn’t hurt to include it.

Other little bits and pieces

I found it interesting that the converted Interactive Grid includes some extra columns automatically: APEX$ROW_SELECTOR (Type = Row Selector), APEX$ROW_ACTION (Type = Actions Menu), and ROWID. These give greater control over what gets included, and you can delete these if they are not required.

Another little gem is the new Column attribute Heading > Alternative Label: “Enter the alternative label to use in dialogs and in the Single Row View. Use an alternative label when the heading contains extra formatting, such as HTML tags, which do not display properly.”.

Demo

If you’d like to play with a working version of the reference application, it’s here (at least, until the EA is refreshed) (login as demo / demo):

https://apexea.oracle.com/pls/apex/f?p=SAMPLE560&c=JK64

I’ve checked in an export of this application to the bitbucket repository (f9674_ea1.sql).


Filed under: APEX Tagged: APEX, apex-5.1, interactive-grid, TAPI

Monitoring AWS Costs

Tue, 2016-06-21 00:38

I’ve been running my Apex sites on Amazon EC2 for many years now, and I’ve gone through a number of infrastructure upgrades and price changes over time. I have some alerts set up, e.g. if a server starts getting very busy or if my estimated charges go over a threshold. Today I got an alert saying my estimated monthly bill will be over $100 which is unusual.

One of the most useful reports in AWS is the Instance Usage Reports (found under Dashboard > Reports > EC2 Instance Usage Report). I tell it to report Cost, grouped by Instance Type, which gives me the following:

aws_instance_usage_report

As you can see, my daily cost was about $1.58 per day, and this shot up on the 16th (note: these rates are for the Sydney region). I was running Oracle on an m1.medium SUSE Linux instance until June 16, when I upgraded it to an m3.medium instance. I have a Reserved Instance (RI) for m1.medium, but not for m3.medium, which is why the cost has shot up. That RI will expire soon; I will purchase an m3.medium RI which will bring the cost of that instance back down to about $1 per day. Until I do that, I will be charged the “On Demand” rate of $4.63 per day.

I’m also running two t2.nano Amazon Linux instances as my frontend Apache servers. Even though they are the smallest available instance type (nano), they barely register over 1% CPU most of the time. I’ve moved all the DNS entries across to one of those nano instances now, so I will soon decommission one which will save me a few extra dollars per month.

As an Apex developer, outsourcing the hardware-related worries to AWS has been the best decision I’ve made. I’ve only suffered a couple of significant outages to date, and in both instances all my servers were still running without issue when connectivity was restored. I can spin up new instances whenever I want, e.g. to test upgrades (you might notice from the graph that I did a test upgrade on an m3.medium instance on June 14).

In case you’re wondering, the total time I needed to take down my Apex instance, take a snapshot, spin up the new instance, and swap the IP address across to it, was about 30 minutes. And that included about 10 minutes lost because I accidentally picked an incorrect option at one point. Not only that, but my upgrade also included changing from magnetic disk to SSD, which seems a bit faster. Overall I’m pretty happy with all that.


Filed under: Oracle

Checkbox Item check / uncheck all

Tue, 2016-06-14 04:00

If you have an ordinary checkbox item based on a list of values, here is a function which will set all the values to checked or unchecked:

function checkboxSetAll (item,checked) {
  $("#"+item+" input[type=checkbox]").attr('checked',checked);
  $("#"+item).trigger("change");
}

For example:

checkboxSetAll("P1_ITEM", true); //select all
checkboxSetAll("P1_ITEM", false); //select none

It works this way because a checkbox item based on a LOV is generated as a set of checkbox input items within a fieldset.

Note: If it’s a checkbox column in a report, you can use this trick instead: Select All / Unselect All Checkbox in Interactive Report Header


Filed under: APEX Tagged: APEX, javascript, jQuery, tips-&-tricks

Unique constraint WWV_FLOW_WORKSHEET_RPTS_UK violated

Thu, 2016-05-12 19:31

If your Apex application import log shows something like this:

...PAGE 73: Transaction Lines Report
declare
*
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
violated
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 16271
ORA-06512: at line 6

(this is on an Apex 4.2.4 instance)

This is due to a Saved Report on an Interactive Report that was included in the export, which conflicts with a different Saved Report in the target instance. The log will, conveniently, tell you which page the IR is on.

The solution for this problem is simple – either:

(a) Export the application with Export Public Interactive Reports and Export Private Interactive Reports set to No;
OR
(b) Delete the Saved Report(s) from the instance you’re exporting from.

You can find all Saved Reports in an instance by running a query like this:

select workspace
      ,application_id
      ,application_name
      ,page_id
      ,application_user
      ,report_name
      ,report_alias
      ,status
from APEX_APPLICATION_PAGE_IR_RPT
where application_user not in ('APXWS_DEFAULT'
                              ,'APXWS_ALTERNATIVE');

You can delete Saved Reports from the Application Builder by going to the page with the Interactive Report, right-click on the IR and choose Edit Saved Reports, then select the report(s) and click Delete Checked.


Filed under: APEX Tagged: APEX, problem-solved

Pages