Book Review: Oracle 11g Anti-hacker's Cookbook

I am deeply privileged to have contacted by Packt Publishing for reviewing their new book intended to teach recipes for securing Oracle 11g database against a typical hacker.

The book is authored by Adrian Neagu. The authors profile is available in the publishers website. A sample chapter is available for free reading in this link or for download as PDF file.

You may buy either ebook (all formats such as Kindle, PDF, epub are available) or print version which includes ebook if you buy online from this website.

The book is meant for mid level DBAs who has basic understanding of security concepts. Its not at all an entry level DBAs book.

Oracle APEX : Input Field with ALL Capital Letters

This is a mini-tip on changing characters entered in a text/input field to all Capital letters. A little use of Javascript is required. Navigate to the page where the changes is required. Click on the field/Item for which all capital characters is required. Scroll till you reach Element section and in "HTML Form Element Attributes" mention the following (including quotes):

onKeyUp="this.value=this.value.toUpperCase();"

Now Click on Apply Changes button and run the page. For the field this attribute is applied, whenever a key is typed the case is changed. But if you dont want this type of animation and just want all characters to be in upper case no matter what, add the following to the text field:

style="text-transform: uppercase;" onKeyUp="this.value=this.value.toUpperCase();"

To learn more about text-transform click here.

The style attribute is a CSS property and applies an all UPPER CASE property to the text field. Even though the style attribute is applied the value which will be passed to server may not be uppercase. Only if the user has CAPS lock turned on the value will be passed to server as UPPERCASE, otherwise it will be case in which user typed in. So to make sure that the user typed value also gets modified to UPPERCASE we have a javascript to change value.

Edit: June 1st, 2018

In Apex 5 and later the attribute to put the above is not "HTML Form Element Attributes", but "Custom Attributes" below Advanced section.

Oracle APEX - Navigation bar current item

Apex Version 4.0.2

I am using a template where my contents are appearing in one side (left) and navigation in another side (right) of the webpage. I created a "Vertical Sidebar List" template wherein all my navigation items will be embedded inside an un-ordered List. I created this very much easily.

First created a List (Application Builder > My Application > Shared Components > Lists (Below Navigation)). The List template as I mentioned earlier is "Vertial Sidebar List". I entered a few pages in the navigation items and created the list.

Later on I manually added a List Region to all my pages. I chose the Display Point of "Page Template Region Position 2" for my page contents, and for Menu I chose "Page Template Region Position 3". Thus I was able to list both regions side by side.

But the problem I landed on was that when I click on a page the list was not displaying current attribute (or a highlighting). Even after lot of googling I was not able to find out this. (Maybe my search keywords suck). But I got a small tip from one of my existing pages for which the highlighting was working. After long time of searching each and every property for this, finally the missing part stuck my eye.

Within every List item there is a property "Current for Pages". Its below (Application Builder > My Application > Shared Components > Lists > Entries by List (Click on the list) > Click Grid Edit button on top.

Enter the page number of the page which the list item denotes. Thats it!

It was simpler but had to spent some time in identifiying the property.

Leave Management System for APEX (LeaveLite)

If you are new to Oracle APEX, well you are opening up a new world. Otherwise you know what APEX can do for you. With the latest version of 4.2 released at least I have lot of dreams with it.

I began developing in the age where Client-server applications were dominant. Oracle Forms provided features where it is easy to develop an application within days. With the advent of Web applications Oracle provided various frameworks including ADF, APEX among others. I do not know Java, so I left behind from ADF. APEX seems to be perfect fit for me because it is pure PL/SQL that I have to know. (Though Java script/jQuery skills will be hugely beneficial).

To add to this list for a year or so I have been developing website for our client with pure PL/SQL Web Toolkit. With this skill with me it was easy to delve deeper into APEX. With APEX 4.2 I have got more than what I have dreamed of, Mobile Applications.

I am sharing with all my readers an application which I have designed and developed using APEX 4.1 and now upgraded to APEX 4.2.

The application is called LeaveLite and its features are follows:

This application supports three modules and following functions
1. Administration
  a. Employees List
  b. Holidays List
  c. Add Leave Balance
2. Manager Services
  a. Approve/Reject Leave
  b. My Team
  c. Employee-wise Leave Taken
3. Employee Services
  a. Apply for Leave
  b. Cancel Leave
  c. Leaves Report
  d. Holidays List

• Download Readme
• Download Source cum Apex Installer (v1.1)
• Download Source cum APEX Installer (v1.0) (older version)

Installation Instructions
To Install this software you must install Oracle APEX. Once that is installed make sure you upgrade your APEX version to 4.2. An alternate for you to try this is to use APEX cloud for trial purposes. Register/Log on to http://apex.oracle.com and use Application Import feature.

After APEX is upgraded to 4.2 you may now install this application using Application Import feature within APEX.

The install script comes with all tables script and necessary objects. Once installed browse the table LEAVE_MEMBER and make sure there are some members added to this table (administrator user) and then login using this user id and password.

Oracle APEX 4.2 released

Oracle Application Express 4.2 is released for production. The first and foremost features that got my attention was Application Builder for Mobile. Oracle has understood the importance of mobile web and has scaled to the need very quickly.

I was able to create a mobile application without learning much. With only seeing this 2 minute video from Oracle Learning you will also understand how easy it is to create Business standard applications within minutes.

The next feature (if you can call it) was the long list of packaged applications within Apex 4.2. I was able to just install a Bug Tracking software and run within 5 minutes. It also includes a Mobile interface. For more information on other features refer Oracle Website.

Oracle Form - SOLVED - ORA-24813: cannot send or receive an unsupported LOB

I have a form with some text fields and a BLOB (storing image) column. For storing the BLOB column I was using webutil_file_transfer.client_to_db_with_progress procedure to transfer and store to BLOB column. In essence the BLOB column in the data block was only for querying the image to be shown to users.

But when I modify the text column and save the record I got an error message which I have never encountered till now.

ORA-24813: cannot send or receive an unsupported LOB

Thanks to my colleague Haris, I modified the data block property Update Changed Columns Only to "Yes" from default No. The error is now gone.

The error is solved by this simple property setting. 

Oracle APEX: Redirect to a page after submit processing

How can I redirect to a page after submit button's process has completed in Oracle APEX?

• Click on a page where you need to do the change. You must have atleast 1 submit button in your page already added.
• Click on Utilities Menu in the Page Toolbar.
• Select Page Events
• In the right hand side below Page Processing click on Create Branch in case a new redirect has to be created. If you need to modify a redirecting/branching click on the URL displayed along side Go To Page: and modify the page you want to be redirected.

APEX Version: 4.1.1.00.23

biTracker - A free Administrative Intranet Portal in PL/SQL

I am very pleased to announce a project which is very close to my heart. "The biTracker." It uses the PL/SQL Web Toolkit for generating HTML pages which can be used by an organization for its internal needs.

About Pl/SQL (Oracle) Web Toolkit

A programming language now-a-days must be capable of producing web content using HTTP protocol. PL/SQL is no exception, and it does this job in style. Oracle supplies some packages called HTP, HTF etc which can generate HTML content to HTTP stream which can be opened by a browser. The flow of data can be in the following tiers:

1. PL/SQL Procedure or Package (Database Layer)
2. Web Server (Middle-Tier)
3. Browser (Client Side)

As Oracle handles the flow of data from Database Layer to Middle Tier to Client side it gives us time to develop feature rich applications using Database Layer. 

Consider the following example:

CREATE PROCEDURE helloworld IS

BEGIN

  HTP.htmlOpen;

  HTP.print('Hello World');

  HTP.htmlClose;

END;

The above procedure as you would have guessed will print the message "Hello World" onto the Browser page. If you know HTML you can takeover from this and build feature-rich HTML pages which are dynamic in nature. (Tip: I used jQuery for making HTML pages dynamic).

About biTracker

The story started some months ago and this application includes the following in its version 0.0.3

1. Admin Screens for various Master screens
• Project, Role, Member, Issue Status
2. Defect Tracking
• Issue Listing, Raise an Issue
3. Leave Management
• Apply for Leave & Leave History
4. A dashboard which displays a calendar

At this stage this software is not capable of handling every aspect of an organizations need. You may download the source of this from sourceforge

I am looking for peers who can develop/test this and to make it a good internal website.