Author: ryelpango

Categories
ApEx Oracle PL/SQL

Improving Performance of Your Oracle Application Express Software

I am writing this article with the thread http://forums.oracle.com/forums/thread.jspa?messageID=3205216&#3205216 in mind. We have a very large workflow application written in Oracle Application Express, supporting some 3000+ users. This application was written with care, for obvious reasons. Well for one, it’s written in Application Express, a rapid development tool which is not designed for an application of this magnitude (80+ hits per second, 15 hours a day)! Every condition, expression, process is held in pre-compiled Oracle packages/procedure/functions in order to minimize latching and thus improve performance.

All is well until the application hits peak periods, which is when our CPU was 100% busy all the time. It remained a mystery for about two years until a few days ago. For two years I’ve searched high and low for an answer, because our application has been optimized from top to bottom. We knew what was causing the problem, it is the dreaded PL/SQL block below:

declare
 rc__ number;
 simple_list__ owa_util.vc_arr;
 complex_list__ owa_util.vc_arr;
begin
 owa.init_cgi_env(:n__,:nm__,:v__);
 htp.HTBUF_LEN := 255;
 null;
 null;
 simple_list__(1) := 'sys.%';
 simple_list__(2) := 'dbms\_%';
 simple_list__(3) := 'utl\_%';
 simple_list__(4) := 'owa\_%';
 simple_list__(5) := 'owa.%';
 simple_list__(6) := 'htp.%';
 simple_list__(7) := 'htf.%';
 simple_list__(8) := 'wpg_docload.%';
 if ((owa_match.match_pattern('f', simple_list__, complex_list__, true))) then
  rc__ := 2;
 else
  null;
  null;
  f(p=>:p);
  if (wpg_docload.is_file_download) then
   rc__ := 1;
   wpg_docload.get_download_file(:doc_info);
   null;
   null;
   null;
   commit;
  else
   rc__ := 0;
   null;
   null;
   null;
   commit;
   owa.get_page(:data__,:ndata__);
  end if;
 end if;
 :rc__ := rc__;
end;

The dreaded PL/SQL block above was called about 7,000 times in an hour and consumes 58% of the CPU load in our system (see stats below)!

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
4,014 19,928 6,686 0.60 58.2 c34r978mgkrmf

For two years I could not find the answer. I’ve seen similar problems experienced by other people but it was dismissed as an expected behaviour by Oracle (see “Performance degradation is the expected behavior” http://forums.oracle.com/forums/thread.jspa?messageID=3205216&#3205216). Our DBA hassled me again last week advising that I enhance our application in order to reduce the number of calls to the dreaded SQL. I told him that I did not know when the block of code was called and advised that the only way I could even have a remote chance in finding it, I would need access to HTTP logs as well as database logs. Even Oracle Ace sspadafo said “That looks like the anonymous block built by modplsql to handle the form POST”.

Equipped with renewed vigour to find a solution the problem, our DBA went away and “reversed engineed” the problem so that a solution can be found once and for all! He got back to me a few days later with this email:

Ariel,I did a bit reverse engineer on this internal ApEx API. The core of this API is part of OWA. The related OWA is inside package wpg_docload. The source code of wpg_docload is at $ORACLE_HOME/rdbms/admin/wpgdocs.sql.

Whenever the end-users download a file (images, word docus, etc.), the wpg_docload is called. Obviously we call it too frequently and causes a big performance problem.

For example, between 14:00pm and 15:00pm yesterday, we fetched more than 3000 times for each of the files below:
[oracle@puma-c-app-00 logs]$ grep access.gif junk2 | wc -l
3777
[oracle@puma-c-app-00 logs]$ grep bug.gif junk2 | wc -l
3731
[oracle@puma-c-app-00 logs]$ grep change.gif junk2 | wc -l
3360
[oracle@puma-c-app-00 logs]$ grep dmt1.jpg junk2 | wc -l
3595
[oracle@puma-c-app-00 logs]$ grep dmt2.jpg junk2 | wc -l
4006
[oracle@puma-c-app-00 logs]$ grep dmt3.jpg junk2 | wc -l
3813
[oracle@puma-c-app-00 logs]$ grep dot.gif junk2 | wc -l
3140
[oracle@puma-c-app-00 logs]$ grep htmldb_remix.js junk2 | wc -l
3729
[oracle@puma-c-app-00 logs]$ grep theme.css junk2 | wc -l
3952
[oracle@puma-c-app-00 logs]$ grep whats_new.gif junk2 | wc -l
3538

We have 2 AppServers, so the real fetches are twice. On average, they are accessed 20 times per second!

Because only one CPU can access a particular RAM address at a given time, all other CPUs have to wait. This kind of issues can't be resolved by big/better hardware. The more powerful of the host, the worse it gets. For 4 CPUs, 3 have to wait. For 8 CPUs, 7 have to wait, For 16 CPU, 15 have to wait. While the CPUs are waiting, they are spinning (controlled by a hidden parameter _spin_count in Oracle). Spinning means using 100% CPU doing nothing! This is why your host is always busy.

The solution is to move the above images from database tier to middle tier.

When the image is fetched from database, the PC can't cache it because the PC does not know if the file has changed since last fetch. (If you read the source code I mentioned above, you will see actually Oracle is fully aware of this problem in 1999/2000)

When the image is fetched from Apache, it will be cached in the PC's RAM until the file on Apache server is changed.

After we move the above images into Apache, we will never need to run the culprit API to fetch these files, the workload on database tier will be reduced dramatically. As the images are cached in the PC's RAM, the workload on middle tier will be reduced largely as well.

What do you think?

Cheers,
Han

The revelation hit me like a lightning bolt! I’ve been looking in the wrong place! All this time I’ve been targetting expressions, conditions, computations, processes etc. I never thought to look in Shared Components -> Templates to optimize the application! Who would? Right? Templates control the look and feel of the application (stylesheets, images, themes, etc.), it should contain minimal database calls, right?

Not so with Oracle Application Express. Well, every image you store in Shared Components -> Images are only accessed through a database call specified below:

wwv_flow_file_mgr.get_file?p_security_group_id=923024072426648&p_fname=[FILE_NAME]

You reference these images through the page template through a substitution string eg.:

<script src="#COMPANY_IMAGES#htmldb remix. js" type="text/javascript"></script>
<img src="#COMPANY_IMAGES#sample_picture.jpg" alt="sample picture />

When the image or js is fetched from the database, the client can’t cache it because the client computer does not know if the file has changed since the last fetch. Therefore if you got hundreds of these images on your page, it would mean that you would call the “dreaded code” hundreds of times!

After this information was revealed to me by our brilliant DBA, the fix was simple. Move all images and js source from the database to a web server. Change all references #COMPANY_IMAGES# (sometimes referred to as #WORKSPACE_IMAGES# on some installation) in the page templates.

The performance improvement was almost instant! Within an hour the CPU usage on our system dropped by 40%! Here are the stats:

CPU Usage Date and Time (Today) Percentage reduction
683970966 09/02/05 00 11.32
703065671 09/02/05 01 10.93
709172691 09/02/05 02 11.36
711003880 09/02/05 03 11.37
369238 09/02/05 04 19.83
6961506 09/02/05 05 25.88
9324080 09/02/05 06 19.41
36286117 09/02/05 07 25.12
66275947 09/02/05 08 31.29
95548315 09/02/05 09 41.38
Total CPU(Include waits)    
2030852964 09/02/05 00 25.43
2104019110 09/02/05 01 24.63
2112896205 09/02/05 02 24.76
2118178749 09/02/05 03 24.72
2029337 09/02/05 04 1.24
12410075 09/02/05 05 31.12
19783820 09/02/05 06 21.76
88298905 09/02/05 07 20.7
166585318 09/02/05 08 29.21
238824609 09/02/05 09 41.35

Unbelievable, isn’t it?

Here’s an extract of the email conversation that took place between me and our DBA for your reference:

From: Ariel
Sent: Thursday, 5 February 2009 10:58
To: Han
Subject: RE: Culprit, again

Well done Han, it looks like you nailed it!
It would be interesting to see what Oracle has to say about this issue, considering that some Oracle experts doesn't have a clue when the "dreaded code" is executed, and didn't offer any viable solution to the problem.

http://forums.oracle.com/forums/thread.jspa?messageID=3205216?

eg. Oracle Ace sspadafo said "That looks like the anonymous block built by modplsql to handle the form POST".
Oracle also said "Performance degradation is the expected behavior" and closed the following service request as a non-bug:
https://metalink.oracle.com/CSP/main/article?cmd=show&type=BUG&id=4755226

I think Oracle should reconsider addressing this as a bug considering that it has the ability to run a massive system to the ground.

Hehehe.. You should work for Oracle and make the database better!
They'll probably hire you if you post your analysis below to them.

Regards,
Ariel

_____________________________________________
From: Han
Sent: Thursday, 5 February 2009 10:31
To: Ariel

Subject: RE: Culprit, again

CPU Usage Date and time (Yesterday) CPU Usage Date and Time (Today) Percentage reduction
771304282 09/02/04 00 683970966 09/02/05 00 11.32
789369951 09/02/04 01 703065671 09/02/05 01 10.93
800036699 09/02/04 02 709172691 09/02/05 02 11.36
802199588 09/02/04 03 711003880 09/02/05 03 11.37
460544 09/02/04 04 369238 09/02/05 04 19.83
9392224 09/02/04 05 6961506 09/02/05 05 25.88
11569372 09/02/04 06 9324080 09/02/05 06 19.41
48457545 09/02/04 07 36286117 09/02/05 07 25.12
96454957 09/02/04 08 66275947 09/02/05 08 31.29
162988493 09/02/04 09 95548315 09/02/05 09 41.38

Total CPU(Include waits) Total CPU(Include waits)
2723572445 09/02/04 00 2030852964 09/02/05 00 25.43
2791755271 09/02/04 01 2104019110 09/02/05 01 24.63
2808149247 09/02/04 02 2112896205 09/02/05 02 24.76
2813795083 09/02/04 03 2118178749 09/02/05 03 24.72
2054772 09/02/04 04 2029337 09/02/05 04 1.24
18015757 09/02/04 05 12410075 09/02/05 05 31.12
25285828 09/02/04 06 19783820 09/02/05 06 21.76
111341642 09/02/04 07 88298905 09/02/05 07 20.7
235337742 09/02/04 08 166585318 09/02/05 08 29.21
407219600 09/02/04 09 238824609 09/02/05 09 41.35

_____________________________________________
From: Han
Sent: Wednesday, 4 February 2009 3:15 PM
To: Ariel

Subject: RE: Culprit, again

Ariel,

I did a bit reverse engineer on this internal ApEx API. The core of this API is part of OWA. The related OWA is inside package wpg_docload. The source code of wpg_docload is at $ORACLE_HOME/rdbms/admin/wpgdocs.sql.

Whenever the end-users download a file (images, word docus, etc.), the wpg_docload is called. Obviously we call it too frequently and causes a big performance problem.

For example, between 14:00pm and 15:00pm yesterday, we fetched more than 3000 times for each of the files below:
[oracle@puma-c-app-00 logs]$ grep access.gif junk2 | wc -l
3777
[oracle@puma-c-app-00 logs]$ grep bug.gif junk2 | wc -l
3731
[oracle@puma-c-app-00 logs]$ grep change.gif junk2 | wc -l
3360
[oracle@puma-c-app-00 logs]$ grep dmt1.jpg junk2 | wc -l
3595
[oracle@puma-c-app-00 logs]$ grep dmt2.jpg junk2 | wc -l
4006
[oracle@puma-c-app-00 logs]$ grep dmt3.jpg junk2 | wc -l
3813
[oracle@puma-c-app-00 logs]$ grep dot.gif junk2 | wc -l
3140
[oracle@puma-c-app-00 logs]$ grep htmldb_remix.js junk2 | wc -l
3729
[oracle@puma-c-app-00 logs]$ grep theme.css junk2 | wc -l
3952
[oracle@puma-c-app-00 logs]$ grep whats_new.gif junk2 | wc -l
3538

We have 2 AppServers, so the real fetches are twice. On average, they are accessed 20 times per second!

Because only one CPU can access a particular RAM address at a given time, all other CPUs have to wait. This kind of issues can't be resolved by big/better hardware. The more powerful of the host, the worse it gets. For 4 CPUs, 3 have to wait. For 8 CPUs, 7 have to wait, For 16 CPU, 15 have to wait. While the CPUs are waiting, they are spinning (controlled by a hidden parameter _spin_count in Oracle). Spinning means using 100% CPU doing nothing! This is why your host is always busy.

The solution is to move the above images from database tier to middle tier.

When the image is fetched from database, the PC can't cache it because the PC does not know if the file has changed since last fetch. (If you read the source code I mentioned above, you will see actually Oracle is fully aware of this problem in 1999/2000)

When the image is fetched from Apache, it will be cached in the PC's RAM until the file on Apache server is changed.

After we move the above images into Apache, we will never need to run the culprit API to fetch these files, the workload on database tier will be reduced dramatically. As the images are cached in the PC's RAM, the workload on middle tier will be reduced largely as well.

What do you think?

Cheers,
Han

_____________________________________________
From: Han
Sent: Tuesday, 3 February 2009 11:34 AM
To: Ariel

Subject: RE: Culprit, again

Ariel,

Yes, it's ApEx internal API, but I don't think we can blame Oracle entirely. Now we know this API is bad, we shouldn't call it.

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
4,014 19,928 6,686 0.60 58.2 c34r978mgkrmf

This SQL was called 6,686 times in an hour. Do we really need to call it so many times?

This issue was reported in 2005 as "Severe Loss of Service" by others, Oracle closed it "not a bug". See metalink bug#4755226 for details.
This issue was occurred again in 11g ApEx 3.1.2, http://forums.oracle.com/forums/thread.jspa?messageID=3205216?
Yes, it's Oracle's API, but Oracle will not do anything about it, the only choice we have is to avoid calling it.
Cheers,
Han

_____________________________________________
From: Ariel
Sent: Tuesday, 3 February 2009 10:58 AM
To: Han

Subject: RE: Culprit, again

Han,
Well, it's Oracle ApEx's own API's that's chewing up CPU. I'm hoping that their experts would have found the problem by now and enhanced the process. Oracle 10g's PL/SQL gateway is so full of patches, that 58% of the CPU load is spent checking for potential threats because requests aren't checked on the Application Server but passed to the database directly and executed by the PL/SQL gateway. During busy period, it just doesn't cope.

When is the code executed? I don't know.. I'm guessing it's every time because every request can potentially contain malicious code injected within the URL.

Oracle 11g is a single tier system. It's mechanics would be completely different to Oracle 10g. I am only hoping.

Regards,
Ariel

_____________________________________________
From: Han
Sent: Tuesday, 3 February 2009 10:45
To: Ariel

Subject: RE: Culprit, again

Ariel,

I think we need to further isolate the issue. "This piece of SQL gets executed everytime a request is made.". We need to find out what kind of "a request" it's. As I know, not all requests will run this SQL. There are many other applications on PUMA don't run it, even DMT right now is not running it that frequently. Can you give me an example of the URL you refer to please?

Why do you think 11g ApEx 3.1 helps here? If it's network traffic, I can image, but it's CPU.

Regards,
Han

_____________________________________________
From: Ariel
Sent: Tuesday, 3 February 2009 10:34 AM
To: Han

Subject: RE: Culprit, again

Han,
This piece of SQL gets executed everytime a request is made.
The code checks for exclusion strings in the URL and halts the request if excluded strings exists in the URL.

The only way we can reduce the number of times this code gets executed is to reduce the traffic during this period (2.00pm to 3:00), or upgrade ApEx to Oracle 11g ApEx 3.1, with it's embedded Oracle Application Server, which may be less prone to these issues because the App Server is in the database.

2:00 to 3:00 pm is the busiest time of the day, because Day shift staff from 3 workcentres (Coffs, Sydney and Ballarat) are frantically booking activities off to DMT before they finish off for the day.

Regards,
Ariel

_____________________________________________
From: Han
Sent: Monday, 2 February 2009 15:49
To: Ariel
Subject: Culprit, again

Ariel,

The old issue come back again.

I checked the performance issue between 2:00pm and 3:00pm today when CPU was 100% busy all the time. The #1 SQL is:

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
4,014 19,928 6,686 0.60 58.2 c34r978mgkrmf

So 58% of CPU usage is used by this SQL. Then I find out the SQL text:

select sql_fulltext from v$sqlstats where sql_id = 'c34r978mgkrmf'

declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__,:nm__,:v__);
htp.HTBUF_LEN := 255;
null;
null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
if ((owa_match.match_pattern('f', simple_list__, complex_list__, true))) then
rc__ := 2;
else
null;
null;
f(p=>:p);
if (wpg_docload.is_file_download) then
rc__ := 1;
wpg_docload.get_download_file(:doc_info);
null;
null;
null;
commit;
else
rc__ := 0;
null;
null;
null;
commit;
owa.get_page(:data__,:ndata__);
end if;
end if;
:rc__ := rc__;
end;

Now, it looks familiar, it rings a bell. I looked around, it's actually the same culprit we identified 2 years ago (see below email).

Any chance you can limit the times of running on this piece please?

Cheers,
Han

_____________________________________________
From: Han
Sent: Monday, 27 November 2006 9:24 AM
To: Ariel
Subject: RE:

Thanks.

Any chance you can limit the times of running on this piece please:

declare
rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
begin
owa.init_cgi_env(:n__,:nm__,:v__);
htp.HTBUF_LEN := 255;
null;
null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
if ((owa_match.match_pattern('f', simple_list__, complex_list__, true))) then
rc__ := 2;
else
null;
null;
f(p=>:p);
if (wpg_docload.is_file_download) then
rc__ := 1;
wpg_docload.get_download_file(:doc_info);
null;
null;
null;
commit;
else
rc__ := 0;
null;
null;
null;
commit;
owa.get_page(:data__,:ndata__);
end if;
end if;
:rc__ := rc__;
end;

_____________________________________________
From: Ariel
Sent: Monday, 27 November 2006 9:20 AM
To: Han
Subject: RE:

Done, the process has been modified.

Regards,
Ariel

_____________________________________________
From: Han
Sent: Monday, 27 November 2006 9:15 AM
To: Ariel
Subject: RE:

Yes, it causes performance issue, please try to run it only when necessary.

_____________________________________________
From: Ariel
Sent: Monday, 27 November 2006 9:13 AM
To: Han
Subject: RE:

Yes, it's a computation that happens everytime a page loads. Is it causing a problem? I can modify it to only run once during login.

select 1
from pay_rate
where pay_rate_id = (select pay_rate from dmt_users where user_id = :DMT_USER_ID)
and to_number(to_char(sysdate - nvl(:DMT_TIME,0),'HH24')) between start_hour and end_hour

Regards,
Ariel

_____________________________________________
From: Han
Sent: Monday, 27 November 2006 9:03 AM
To: Ariel
Subject: RE:

Does this SQL ring a bell to you please?

select 1 from pay_rate where pay_rate_id = (select pay_rate from dmt_users where user_id = :DMT_USER_ID) and to_number(to_char(sysdate - nvl(:DMT_TIME,0),'HH24')) between start_hour and end_hour

Many sessions are running the same SQL.

_____________________________________________
From: Ariel
Sent: Monday, 27 November 2006 8:29 AM
To: Han
Subject:
Importance: High

Han/John,
DMT on PUMAE is running very slowly at the moment and it appears to be getting worse. Could you please investigate at you earliest possible convenience.

Thank you,

Regards,
Ariel

 

Categories
screen scrape

Accessing a Web Page Requiring NLTM Authentication Through ASP MSXML2.ServerXMLHTTP Object

Have you ever “scraped” web page contents using the following code:

<%
  Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")
  
  ' Opens the connection to the remote server.
  xml.Open "GET", "http://www.somedomain.com", False
  
  xml.Send
  v_response = xml.responseText
  response.write v_response
  Set xml = Nothing
%>

To your suprise, the web page content returned the following:


In order to access this page you must be using a browser which supports NTLM authentication. Currently, only Internet Explorer supports this

Well, this occurred because the page you accessed (http://www.somedomain.com based on the example above) requires NLTM authentication. In order to access the page successfully, you need to pass username and password credentials during the “GET” command. This can be achieved by modifying the code above to include the username and password:

<%
  Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")
  
  ' Opens the connection to the remote server.
  xml.Open "GET", "http://www.somedomain.com", "DOMAIN\user", "password"
  
  xml.Send
  v_response = xml.responseText
  response.write v_response
  Set xml = Nothing
%>

Make sure your replace “http://www.somedomain.com”, “DOMAIN\user” and “password” with values applicable to your process.

That’s it. Happy coding!

Categories
Oracle SQL*Loader

SQL*Loader and Tab Delimited Input Data

There’s a great article by Tom Kyte about loading a tab delimited file into Oracle using SQL*Loader which can be viewed here. It talks about using the hexadecimal character X’9′ (tab character) as the delimiter when implementing your control file.

Although, it failed to add that if you add the OPTIONALLY ENCLOSED BY '"' option in your control file, it WILL NOT WORK. That is, if your file does not contain the specified enclosing character.

For example the following will not work, as it will still lump all tab delimiters into one if there are null values:


LOAD DATA
INFILE *
INTO TABLE DEPT
replace
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12      SARATOGA
10  ACCOUNTING  CLEVELAND
11  ART SALEM
13  FINANCE BOSTON
21  SALES   PHILA.
22  SALES   ROCHESTER
42  INT'L   SAN FRAN

(note: there are two tabs between 12 and SARATOGA in the above)

Either change the file so each null field is enclosed by the character specified in the control file, or get rid of the option altogether (if your file values aren’t enclosed by a character). In conclusion, be careful when using the OPTIONALLY ENCLOSED BY '"' option as this appears to be an Oracle bug.

Categories
DVD Player Product Review

Divx Player with USB Input

OK, so you got a whole lotta movies in Divx format. You got it all saved in one large hard drive possibly categorized into multiple subdirectories. You’re sick and tired of watching your collection through your PC, and you want to watch it on the “big screen”. Well, you got a number of options:

  1. If you got an XBOX 360, connect it through the internet, then update XBOX LIVE so you can play Divxs. Plug your external hard drive to the 360 via USB, and away you go.
  2. Plug your PC/laptop via VGA, if your TV supports it.
  3. Get yourself a Hard Drive with a built-in Media Player.
  4. Get yourself a dvd player that support most video formats (including divx of course) that’s equipped with a USB port (supporting USB 2.0).

Higlander Divx Player + External HDDI’ve actually tried all 4 options. I’d say the best option would probably be option 4. I also highly recommend the Highlander HDMI DVD DivX Player. It’s cheap, fully functional, and supports a wide range of video formats. Here’s a run down of all it’s features:

  1. Highlander HDMI DVD Player – Multiregion
  2. 5.1 Channel MPEG-4/DiVX /HDMI DVD Player
  3. Region free
  4. Loader: Sanyo HD65
  5. Solution: MTK 1389HD
  6. Support DVD-R/DVD/VCD/HD-CD/CD-R/MPEG-4/DiVX/HDMI
  7. Support Multilingual Dialogue (up to 8 tracks) and subtitle
  8. Support NTSC/PAL playback
  9. 4x Zooming Playback
  10. Full function Remote Control
  11. Onscreen Display, Parental Control
  12. Selectable Screen Aspect Ratio (4:3 & 16:9)
  13. Dolby AC-3 5.1 Channel Audio Output
  14. Composite Video, S-Video Output
  15. Programmed/Repeat Play Remote Control
  16. Accessories: AV Cables and Remote Control

Pros:

  1. Great quality video through HDMI or Component input
  2. Zoom Playback
  3. USB and SD card input

Cons:

  1. It’s got a wide case and it’s not pretty
  2. Can’t seem to turn off the zoom onscreen display when zooming in
  3. 8 character file name limit makes it hard to determine which video to select
  4. Setting up component display requires connection via RCA first
  5. If you accidentally press reset on your remote control then it restores settings to factory default. This means that you have to hook up your device via RCA in order to switch the display to component

Higlander Divx Player + External HDD CloseupI’d give this device a 7.5/10. For its price and functionality, I’d push that up to 8/10. You can get more information about the product by going here:

http://www.dealsdirect.com.au/p/highlander-hdmi-dvd-divx-player/

Categories
ASP Captcha Alternative

Programming Alternative to Captcha on an ASP Web Form Using a Hidden Variable

Hands up who thinks “captcha”, an image based challenge-response test to ensure a response is not generated by a computer, is a big pain in the butt and you’re sick and tired of seeing it in every web form. Not only is it obstructive but most of the images are very difficult to decipher.

Well, an alternative to “captcha” is a well place hidden variable within a web form containing a random number. It doesn’t require user intervention so it’s seamless which would mean that your customers are less annoyed. It acts like a simple redundancy check which effectively identifies if the web form was submitted from the server, and not remotely invoked through a “page scrape” copy. If the web form was submitted from a remote copy, which is how most “robot” processes operate, then the form is not processed.

The example form below is a simple “contact us” page and it implements the hidden variable redundancy check. Make sure that you insert your own code in the following comment block:

'**************************************************************************************
'* Insert code here to post the message
'* Eg. Insert message in a database or send message to your email address
'**************************************************************************************

Save the following code as contact.asp:

<%
  '**************************************************************************************
  '* Store the FORM PASSWORD session value in MESSAGE_PASSWORD variable
  '**************************************************************************************
  MESSAGE_PASSWORD = Session("FORM_PASSWORD")

  v_error = ""
  if Request("posted") = 1 then
    name = Request("name")
    email = Request("email")
    message = Request("message")

    ' Validate web form values
    if name = "" then
      v_error = v_error & "Please enter your name<br />"
    end if
    if validateEmail(email) = false then
      v_error = v_error & "Please enter a valid email address<br />"
    end if
    if message = "" then
      v_error = v_error & "Please enter your message<br />"
    end if

    if v_error = "" then

      if CInt(Request(MESSAGE_PASSWORD)) = CInt(MESSAGE_PASSWORD) then
        '**************************************************************************************
        '* Insert code here to post the message
        '* Eg. Insert message in a database or send message to your email address
        '**************************************************************************************

        Response.write "Your message was sent to us successfully. We will respond to you within 24 hours of receiving this message."
        message = ""
      end if
    end if
  else
    '**************************************************************************************
    '* Generate new FORM PASSWORD value and store it in session variable
    '**************************************************************************************
    Session("FORM_PASSWORD") = randomNumber
    MESSAGE_PASSWORD = Session("FORM_PASSWORD")
  end if
%>
<h1>Contact Us</h1>
<p>Please enter your query in the message text area below</p>
<%
if v_error <> "" then
  Response.write v_error
end if
%>
<form action="contact.asp" method="post">
<table border="0" cellpadding="0" cellspacing="2">
  <tr>
    <td class="tdhead">name</td>
    <td class="spacer" />
    <td><input name="name" id="namefield" class="textbox" type="text" size="38" maxlength="50" value="<%= name %>" /></td>
  </tr>
  <tr>
    <td class="tdhead">email</td>
    <td class="spacer" />
    <td><input name="email" class="textbox" type="text" size="38" maxlength="50" value="<%= email %>" /></td>
  </tr>
  <tr>
    <td class="tdhead">message</td>
    <td class="spacer" />
    <td><textarea name="message" rows="5" cols="40" class="textbox"></textarea></td>
  </tr>
  <tr>
    <td colspan="2" />
    <td>
      <input type="hidden" name="posted" value="1" />
      <input type="hidden" name="<%= MESSAGE_PASSWORD %>" value="<%= MESSAGE_PASSWORD %>" />
      <input type="submit" value="Send Email" class="textbox2"  />
    </td>
  </tr>
</table>
</form>
<script type="text/javascript">
  if (document.forms.length > 0)
    if (document.forms[0].elements.length > 0)
      document.forms[0].elements[0].focus();
//-->
</script>

<%
'**************************************************************************************
'* Common functions
'**************************************************************************************

'**************************************************************************************
'* validateEmail
'* -------------------------------
'* Returns true if email is well formed, false otherwise
'**************************************************************************************
Function validateEmail(sCheckEmail)
  Dim sEmail, nAtLoc
  validateEmail = True
  sEmail = Trim(sCheckEmail)
  nAtLoc = InStr(1, sEmail, "@") 'Location of "@"

  If Not (nAtLoc > 1 And (InStrRev(sEmail, ".") > nAtLoc + 1)) Then
    '"@" must exist, and last "." in string must follow the "@"
    validateEmail = False
  ElseIf InStr(nAtLoc + 1, sEmail, "@") > nAtLoc Then
    'String can't have more than one "@"
    validateEmail = False
  ElseIf Mid(sEmail, nAtLoc + 1, 1) = "." Then
    'String can't have "." immediately following "@"
    validateEmail = False
  ElseIf InStr(1, Right(sEmail, 2), ".") > 0 Then
    'String must have at least a two-character top-level domain.
    validateEmail = False
  End If
End Function

'**************************************************************************************
'* randomNumber
'* -------------------------------
'* Generate random number
'**************************************************************************************
Function randomNumber
  Randomize timer
  Dim rndNum
  ' Randomizing the timer function
  rndNum = abs(int((rnd() * 3001)))
  ' To generate a prime based, non-negative random number..
  rndNum = rndNum + 53
  randomNumber = rndNum
End Function
%>

Some comments about the code:

When the form is first requested it generates a random number which acts like a form password.

    '**************************************************************************************
    '* Generate new FORM PASSWORD value and store it in session variable
    '**************************************************************************************
    Session("FORM_PASSWORD") = randomNumber
    MESSAGE_PASSWORD = Session("FORM_PASSWORD")

This password is stored in a session variable as well as in the form as a hidden item with the same name and value, being the password itself.

  '**************************************************************************************
  '* Store the FORM PASSWORD session value in MESSAGE_PASSWORD variable
  '**************************************************************************************
  MESSAGE_PASSWORD = Session("FORM_PASSWORD")

      <input type="hidden" name="<%= MESSAGE_PASSWORD %>" value="<%= MESSAGE_PASSWORD %>" />
 

When the form is submitted the session value and form value is compared, and if they match then the form is processed.

      if CInt(Request(MESSAGE_PASSWORD)) = CInt(MESSAGE_PASSWORD) then
        '**************************************************************************************
        '* Insert code here to post the message
        '* Eg. Insert message in a database or send message to your email address 
        '**************************************************************************************

        Response.write "Your message was sent to us successfully. We will respond to you within 24 hours of receiving this message."
        message = ""
      end if

That’s it! Happy coding!

Categories
ApEx Oracle

Importing Oracle ApEx 3.1 Page Export into ApEx 3.0 Application

This is a hack that worked for me on several occasions. Our development environment was upgraded to Oracle ApEx 3.1, but for whatever reason, our production environment was not (it’s still sitting on the previous version ApEx 3.0). Our server administrator wouldn’t allow the upgrade on production because of a “bug” with ApEx 3.1. He didn’t really elaborate on what that bug was, but anyway, as a developer, I needed to deploy new pages to our production environment.

To import an Oracle ApEx 3.1 Page Export into an ApEx 3.0 application, do the following:

1. Export the page from the ApEx 3.1 software.

2. Using a reliable text editor, modify the page export and replace any references to the original application ID to the app ID of the application you want to import to (replace xxx with the new application ID):

prompt  APPLICATION xxx - APP V5.6

   -- SET APPLICATION ID
   wwv_flow.g_flow_id := xxx;

3. As specified in the following thread http://forums.oracle.com/forums/thread.jspa?messageID=2380177, modify the following in the page export:

in the command

wwv_flow_api.create_page(
remove this line:
p_include_apex_css_js_yn=>’Y’,
\– \————————————————————-
in the command
wwv_flow_api.create_page_plug (
after the line
p_plug_caching=> …….
add
p_required_patch=> ” + wwv_flow_api.g_id_offset,
\– \————————————————————-
everywhere you find the command
wwv_flow_api.create_report_region (
remove line
p_ajax_enabled=> ‘N’,

In the command
wwv_flow_api.create_flow(
Remove the following line
p_date_format => ‘DD-MON-RR’,

Replace All Occurences of
PICK_DATE_USING_APP_DATE_FORMAT
With
PICK_DATE_DD_MON_RR

Replace All Occurences of
HIDDEN_PROTECTED
With
HIDDEN

In the command
wwv_flow_api.create_report_region (
Delete the following lines
p_prn_content_disposition=> ‘ATTACHMENT’,
p_prn_document_header=> ‘APEX’,
p_prn_width_units=> ‘PERCENTAGE’,

4. Import the modified file using the Export/Import utility in ApEx 3.0, remember to specify “Application, Page or Component Export” as the file type.

5. Edit the page in Application Builder page editor and restore the following: Region Templates, Button Templates, and List Types, and whatever item attribute that may have dropped off because it doesn’t exist in the meta data of the new application.

That should be it. Cumbersome, I know, but trust me, it’s a lot quicker than recreating the page in the new application.

By the way, I’ve written a utility that converts Oracle ApEx 3.1 application package exports to 3.0. You can access the utility here http://www.technologydribble.info/convert-oracle-apex3.1-package-to-3.0.asp. It should also work for converting page exports, no problems.