Apex Monkeys

QTP - Getting current browser URL

2011-04-07T04:19:00.000-07:00

I'm going to show and explain how to get current URL which is opened in a browser.
For example, how to get URL "http://motevich.blogspot.com" from the following browser:

There are two solutions:

Using GetROProperty("URL") method (run-time object property)
Using "URL" property of "Object" (IE DOM Object)

Let's consider both variants.

GetRoProperty("URL") method (run-time object property)

GetRoProperty function reads properties of a run-time object in an application.
If you open QTP Help on GetRoProperty function, you 'll see that this function can work with all objects:
So, for example:
- to check whether a link is visible or not, use:
  Browser("bname").Page("pname").Link("lname").GetROProperty("visible")
- to get a page's title, use:
  Browser("bname").Page("pname").GetROProperty("title")
- to check whether a window is maximized, use:
  Window("wname").GetROProperty("maximized")
- to get width of WebButton, use:
  Browser("bname").Page("pname").WebButton("btname").GetROProperty("width")
Others properties are described in QTP Help.

In our case, to get URL of the current WebPage, we will use:
Browser("bname").Page("pname").GetRoProperty("URL")
This is a sample QTP script:
(Click the image to enlarge it)
"URL" property of "Object" (IE DOM Object)

The sample syntax is:
Browser("bname").Page("pname").Object.URL

What is "Object" in the above statement?
Actually, this is Internet Explorer Document Object Model (DOM) Object.
You can read more about DOM here and here.
Also, I recommend to read this interested article on working with IE DOM from Visual Basic.

Tip: You can use DOM Object when running QTP test on Internet Explorer only!

To make the long story short, I can say that using Object property you can get almost all elements and properties from Web page.

Thus, I use URL property of Internet Explorer's DOM Object.
All properties, collections, and methods of DOM Object are described in this MSDN article.

Tip: The number of properties, accessed via DOM Object, is more bigger, than properties accessed via GetROProperty method.

So, the result of above code is:
(Click the image to enlarge it)

Summary:
Two ways were discussed:

GetROProperty("URL") (run-time object property)
"URL" property of "Object" (IE DOM Object)

GetROProperty method supports both IE & FireFox, but IE DOM Object provides more accessible properties and methods.
Both can get URL of the current Web page.

Source

quotes in VBScript

2011-04-06T07:11:00.000-07:00

There two ways to do that:

Double the quotes (""):
MsgBox "#1: ""QTP - QuickTest Professional"""
Use 2 double quotation marks to include a quote character in the string.
So, the result is:

Use ANSI character code - Chr(34):
MsgBox "#2: " & Chr(34) & "QTP - QuickTest Professional" & Chr(34)
Since, the ANSI code if quotation mark = 34, we can use Chr function.
The result is:

Source

How to set/get system time and date?

2011-04-05T07:10:00.000-07:00

QTP allows running different types of test. And sometimes, it needs perform time and date manipulations on a computer. In this article, I will show and explain - how to get system time/date and change them.

Actually, today's lecture consists of two questions:

How to get system time and date?
How to set system time and date?

Let start training right now :)
So, the first section is:

How to get system time and date?

Actually, this is not difficult task! Use the following functions:

Now - function returns the current date and time according to the setting of your computer's system date and time.
Date - function returns the current system date.
Time - function returns a Variant of subtype Date indicating the current system time.

So, the code is very simple. I provide it with a screen shot containing results:

Well, let's deal with the second section:

How to set system time and date?

For that, I will use simple DOS commands:

time - this command displays or sets the system time:
To change the time, pass it as a parameter to time command. See the above screen shot as an example.
date - this command displays or sets the system date:
To change the date, pass it as a parameter to date command. See the above screen shot as an example for date manipulations :)

Now, I will combine all the above VBScript functions (Now, Date, Time) and DOC commands (time and date):

As you can see, initial date and time were '26.10.2007 22:45:10'. Then I added 3 days and 2 hours to initial date & time. The result of addition is - '30.10.2007 0:45:10'.

By the way, could you guess, why the result day is 30th, not 29th? :) To answer, please pay attention that we added 2 hours too :)

The last step - running of time and date commands from command line. I used Run method of WScript.Shell object.

So, as you can see - date and time manipulation is easy enough.

Source

How to minimize/maximize QTP window?

2011-04-04T07:09:00.000-07:00

This is very useful feature - minimize QTP window before script execution. It allows to observe a desktop or an application under test wholly.

I will show and describe how to minimize (or maximize) QTP window programmatically - i.e. from QTP script.
Also, provided approach can be applied to minimize (maximize) any required window - Browser, Notepad, MS Word or Excel, and so on.

There are two ways to minimize QTP window:

Using Minimize method of Window object
Using QTP Application object - QuickTest.Application

These methods will contain several lines only and I hope that my explanations will be understandable.
So, let's explore!

Using Minimize method of Window object
You can use Minimize method of Window object to minimize QTP window (or any other).

Let's see two cases - when a window is located in Object Repository (OR) and widows is not located in OR.
- If a window is located in Object Repository (OR), then minimizing can be performed using the following code:
  1. hWnd = Browser("browser_name").GetROProperty("hwnd")
  2. Window("hwnd:=" & hWnd).Minimize
  In first line, we get window handle (hWnd) of Browser object . And then we pass this handle to Window object and minimize it.
  Obviously, that we can use the same approach to maximize window (use method):
  1. hWnd = Browser("browser_name").GetROProperty("hwnd")
  2. Window("hwnd:=" & hWnd).Maximize
- If a window is not located in Object Repository (OR), then we can use some run-time object properties and descriptive programming to identify a window. For example, we can use window title.
  
  Please, see a screen shot of QTP window:QTP window title has a prefix - 'QuickTest Professional - ' followed by test name. I will use this prefix ('QuickTest Professional - ') with a mask:
  1. sTitleMask = "QuickTest Professional - .*"
  2. Window("regexpwndtitle:=" & sTitleMask).Minimize
  I use Regular Expressions.
  Mask ".*" means "match any characters zero or more times". So, this mask will identify QTP window, and this code will minimize it.
  
  The same approach can be applied to Browser.
  See a screen shot of this browser:
  
  In this case, browser has prefix 'Google - '. So, I will use it as the mask:
  1. sTitleMask = "Google - .*"
  2. Browser("regexpwndtitle:=" & sTitleMask).GetROProperty("hwnd")
  3. Window("hwnd:=" & hWnd).Minimize
  Well, you can use any approach you like. All of them work and minimize/maximize windows correctly.
Using QTP Application object - QuickTest.Application
Since this approach uses QuickTest Automation Object Model, it can be applied to QTP window only.

There is a procedure, which minimizes QTP window:
1. Sub MinimizeQTPWindow ()
2. Set qtApp = getObject("","QuickTest.Application")
3. qtApp.WindowState = "Minimized"
4. Set qtApp = Nothing
5. End Sub
To minimize QTP window, just execute MinimizeQTPWindow () procedure:
1. MinimizeQTPWindow
For detailed information, I recommend to Read QTP Help > QuickTest Automation Object Model Reference.

Since QuickTest Automation Object Model does not use UI, second approach is more stable.
But first method is more flexible - it allows working with browsers and stand-alone windows.

In any case, I recommend to read and explore both approaches.

Source

How to capture tool tip of link?

2011-04-03T07:08:00.000-07:00

How to capture a tool tip text in QTP?

For example, how to get tool tip ('Go to My Yahoo!') from the yahoo page:

This QTP tutorial shows and explains How to get tool tip in QuickTest Professional.

Actually, this is not a difficult task. The steps are:

Place mouse cursor over the link
Wait for tool tip
Get text of shown tool tip

This QTP script captures a text of a tool tip:

' Place mouse cursor over the link
Browser("Yahoo!").Page("Yahoo!").WebElement("text:=My Yahoo!").FireEvent "onmouseover"
wait 1
' Grab tooltip
ToolTip = Window("nativeclass:=tooltips_class32").GetROProperty("text")

Please, pay attention on details:

We use FireEvent("onmouseover") to simulate mouse placing over the link
Function wait(1) waits 1 second for a tool tip
To get tool tip text, we use:
Window("nativeclass:=tooltips_class32").GetROProperty("text")

Let's run our code and compare captured tool tip with expected ('Go to My Yahoo!'):
As you can see, above QTP script captures correct text of a Tool tip.
I hope, you will add this script to your QTP functions library.

Source

VBScript - Are you sure 1+1=2?

2011-04-02T07:11:00.001-07:00

QTP VBScript contains different niceties. This article shows that 1 + 1 doesn't equal 2 sometimes.

Exactly, 1 + 1 doesn't equal 2 sometimes.

Well, I've prepared this QTP script:

n1 = InputBox("Enter first number")
n2 = InputBox("Enter second number")
n1increased = n1 + 1

If n1increased = n2 Then
MsgBox n1 & " + 1 equals " & n2
Else
MsgBox n1 & " + 1 doesn't equal " & n2
End If

I will explain it step by step and show all entered data and generated result.

Read first number from user's input
I enter 1 as a first number:
Read second number from user's input
I enter 2 as a second number:
Increase first number by 1
n1increased = n1 + 1
Check whether first increased number equals to second number
If n1increased = n2 Then
MsgBox n1 & " + 1 equals " & n2
Else
MsgBox n1 & " + 1 doesn't equal " & n2
End If
And the result is:

Do you know why it is so? What is a reason?
The answer is simple. This is due to converting of VBScript types.

After you read first number (1) from user and assign it (value of course value, not user :) ) to variable, this variable has value of String type.
So, this is the same like n1 = "1"

When VBScript executes this line:

n1increased = n1 + 1

it sees, that we perform mathematical operation and converts n1 into number.
Then VBScript calculates n1 + 1 and the result (2) has a numeric type - Double (special double-precision floating-point value).

Here is the main point! 2 as Number is not the same as 2 as String. These values have different type and that's why they differ.
Simple visual example - two apples are not equal to two pears :)

I can demonstrate the above types converting.
I've added this code into our QTP script before if-then-else:

MsgBox "TypeName(n1): " & TypeName(n1)
MsgBox "TypeName(n1increased): " & TypeName(n1increased)
MsgBox "TypeName(n2): " & TypeName(n2)

And its result is:
That's why the value of number variable is not equal to the value of string variable.

Well, How to make that 1 + 1 = 2 ?
Answer: Convert string value to number with CInt function:

If n1increased = CInt(n2) Then
MsgBox n1 & " + 1 equals " & n2
Else
MsgBox n1 & " + 1 doesn't equal " & n2
End If

And the result is:
It works. It works correctly :)

I hope, you will keep in mind this issue with VBScript types converting. It can save your time during debugging of QTP scripts.

Source

Capturing tool tips of images

2011-04-02T07:07:00.001-07:00

Now, I'm going to show how to show how to capture tool tips of images located on a Web page.

Actually, the solution is simple.
To capture a tool tip of an image, we can get value of "alt" Run-time Object property with GetROProperty("alt") function:

Browser("brw").Page("pg").GetROProperty("alt")

Let's verify this code in practice. For example, let's check tooltips from Wikipedia Main page:

I've prepared QTP script, which gets all image from this page and checks their tooltips ("alt" property):

Dim descImage, listImages, attrAltText, attrSrcText

Browser("Main Page - Wikipedia,").Sync
Browser("Main Page - Wikipedia,").Page("Main Page - Wikipedia,").Sync

' Create description for all images on a Web page.
' For that we use "html tag" property and its value "IMG"
Set descImage = Description.Create
descImage("html tag").value = "IMG"

' Get all images which match the above description
Set listImages = Browser("Main Page - Wikipedia,").Page("Main Page - Wikipedia,").ChildObjects(descImage)

' Check tool tips of images
For i = 0 To listImages.Count - 1
    attrAltText = listImages(i).GetROProperty("alt")
    attrSrcText = listImages(i).GetROProperty("src")

    If attrAltText <> "" Then
        MsgBox "Image src: " & attrSrcText & vbNewLine & "Tool tip: " & attrAltText
    End If
Next

When I run this code in QTP, it shows all images containing non-empty tool tip:

(click the image to enlarge it)

The same message boxes will be shown for others images on a Web page.

So, our solution is simple - use GetROProperty("alt") function to get tool tip of image.
As you can see - it works correctly.

SOURCE

How to get current iteration number of QTP script?

2011-04-02T07:04:00.000-07:00

QTP test can run several iterations.
By default, when you run a test with global QTP Data Table parameters, QuickTest runs the test for each row in the Data Table, using the parameters you specified.

For example, the following global QTP Data Table contains 3 rows:

The iteration number of QTP test can be specified in 'File/Settings.../Test Settings dialog', 'Run' tab:

If we run our QTP test with above settings (global DataTable & 'Run' tab), the test will pass 3 iteration.
So, the question is How to determine the current iteration number withing QTP script?

Answer: We can use the value of "TestIteration" environment variable - Environment("TestIteration").

This is a sample QTP script I use to demonstrate Environment("TestIteration"):

str = "Current QTP iteration: " & Environment("TestIteration") & vbNewLine & _
"Param1: " & DataTable("Param1", dtGlobalSheet) & vbNewLine & _
"Param2: " & DataTable("Param2", dtGlobalSheet)

MsgBox str

And the result of above QTP script is:

As you can see, QuickTest Professional script works correctly.
You can use this approach to determine the current iteration number of running QTP script.

SOURCE

Adding a Context Menu to a Tree Region

2011-02-27T17:13:00.000-08:00

Adding a Context Menu to a Tree Region

One of the nice new features of Application Express is the new tree region. It is based on jsTree and supports features such as tool-tips. jsTree is the same tree used for the new tree view of the Application Builder Page Definition page. That page supports a right click context menu which is missing from the current version of the Application Express new tree region. With a little help from Patrick, I was able to add a context menu to a tree region I was working on for an update to the Document Library packaged application. The purpose of this article is to describe how to do this for your tree regions. I will use the EMP table as a simple example.

Disclaimer:
Patrick wanted me to mention that we may change the implementation of the tree in the future, so you may have to adjust the JavaScript code listed here in future versions of Application Express.

Let's start by creating a copy of the emp table, emp2, so you don't actually mess with the emp data. Use the SQL Workshop SQL Command Processor and the code in listing 1 to create emp2.

Code Listing 1

view plain copy to clipboard print ?

create table emp2 as select * from emp
/
alter table emp2 add constraint emp2_pk primary key (empno)
/
alter table emp2 add constraint emp2_fk foreign key (mgr) references emp2(empno)
/


create table emp2 as select * from emp

/



alter table emp2 add constraint emp2_pk primary key (empno)

/



alter table emp2 add constraint emp2_fk foreign key (mgr) references emp2(empno)

/

Create Application and Tree

With emp2 in place you are ready to create an application. Simply run the create application wizard and create a form and report on emp2. You will replace the report on page 1 with a tree region. After the application is created, edit page 1. Delete the report region and then create a new tree region specifying the following options:

Display Attributes, Title: Employees

Tree Template: Default

Table/View: EMP2

Confirm that all values are defaulted on the Query step

Tooltip: Database Column

Tooltip Column: HIREDATE

Run the page and confirm that your tree appears. When you right click on a node, you should only see that standard right click options of whatever browser you are using.

Create Context Menu

Now for the magic! By sprinkling a little JavaScript on the page here and there, we will get a nice right click context menu. First edit the tree region and give the region a static ID of EMP2 as in figure 1. We need this static ID to make it easier to write JavaScript and select this region with jQuery.

Figure 1

Now you just need to sprinkle a little JavaScript on the page! Edit the page definition of page one and add the code in code listing 2 into the Function and Global Variable Declaration text area and the code in code listing 3 into the Execute when Page Loads text area.

Code Listing 2

view plain copy to clipboard print ?

function doAction(pNode,pTree,a){
var l_action;
var l_id;
l_id = pNode.attr("id");
if (a=="create") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+":::2:P2_MGR:"+l_id }
if (a=="delete") { deleteEmp(pNode,l_id); }
if (a=="edit") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+"::::P2_EMPNO:"+l_id }
if (l_action != null) {document.location.href=l_action; }
}


function doAction(pNode,pTree,a){

var l_action;

var l_id;



l_id = pNode.attr("id");



if (a=="create") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+":::2:P2_MGR:"+l_id }

if (a=="delete") { deleteEmp(pNode,l_id); }

if (a=="edit") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+"::::P2_EMPNO:"+l_id }



if (l_action != null) {document.location.href=l_action; }

}

Code Listing 3

view plain copy to clipboard print ?

var lTreeContextMenu={
items:{create:false,rename:false,remove:false,
contextmenu_create:{
label:"Add Employee",
icon: "",
visible: true,
action: function(pNode, pTree){doAction(pNode, pTree, "create");}
},
contextmenu_delete:{
label:"Delete",
icon: "",
visible: true,
action: function(pNode, pTree){doAction(pNode, pTree, "delete");}
},
contextmenu_edit:{
label:"Edit",
icon: "",
visible: true,
action: function(pNode, pTree){doAction(pNode, pTree, "edit");}
}
}};
// use jsTree to render the tree
var lTreeSel = apex.jQuery("#EMP2").find("div.tree");
var lTreeId = lTreeSel.attr("id");
var lDataId = lTreeId.replace("tree","");
var lTreeData = eval("l"+lDataId+"Data");
var lTree = lTreeSel.tree({
data:{
type:"json",
async:true,
opts:{
"static":lTreeData,
isTreeLoaded:false,
method:"POST",
url:"wwv_flow.show"
}
},
root:{
draggable:false,
valid_children: "folder"
},
folder:{
valid_children: "file"
},
file:{
valid_children: "none",
max_children: 0,
max_depth:0
},
opened:["7839"],
plugins:{contextmenu:lTreeContextMenu}
});
$.showTooltip = function(pEvent) {
var lAction = apex.jQuery(pEvent.target).attr("tooltip");
if (lAction && lAction != "") {
toolTip_enable(pEvent,this,apex.jQuery(this).attr("tooltip"));
}
}; // showTooltip
// Bind Tooltips for tree nodes
apex.jQuery('a[tooltip]', lTreeSel).bind("mouseover", $.showTooltip);
// Hack for right click problem on selected node
apex.jQuery("#EMP2").find("a").live("mouseup",function() {apex.jQuery("#EMP2").find("a").removeClass()});


var lTreeContextMenu={

 items:{create:false,rename:false,remove:false,

     contextmenu_create:{

         label:"Add Employee",

         icon: "",

         visible: true,

         action: function(pNode, pTree){doAction(pNode, pTree, "create");}

     },

     contextmenu_delete:{

         label:"Delete",

         icon: "",

         visible: true,

         action: function(pNode, pTree){doAction(pNode, pTree, "delete");}

     },

     contextmenu_edit:{

         label:"Edit",

         icon: "",

         visible: true,

         action: function(pNode, pTree){doAction(pNode, pTree, "edit");}

     }

}};



     // use jsTree to render the tree

     var lTreeSel = apex.jQuery("#EMP2").find("div.tree");

     var lTreeId = lTreeSel.attr("id");

     var lDataId = lTreeId.replace("tree","");

     var lTreeData = eval("l"+lDataId+"Data");

     var lTree = lTreeSel.tree({

       data:{

         type:"json",

         async:true,

         opts:{

           "static":lTreeData,

           isTreeLoaded:false,

           method:"POST",

           url:"wwv_flow.show"

         }

       },

       root:{

         draggable:false,

         valid_children: "folder"

       },

       folder:{

         valid_children: "file"

       },

       file:{

         valid_children: "none",

         max_children: 0,

         max_depth:0

       },

       opened:["7839"],

       plugins:{contextmenu:lTreeContextMenu}

       });



     $.showTooltip = function(pEvent) {

     var lAction = apex.jQuery(pEvent.target).attr("tooltip");

     if (lAction && lAction != "") {

         toolTip_enable(pEvent,this,apex.jQuery(this).attr("tooltip"));

     }

     }; // showTooltip



     // Bind Tooltips for tree nodes

     apex.jQuery('a[tooltip]', lTreeSel).bind("mouseover", $.showTooltip);



     // Hack for right click problem on selected node

     apex.jQuery("#EMP2").find("a").live("mouseup",function() {apex.jQuery("#EMP2").find("a").removeClass()});

Now when you run the page you should see a right click context menu when you right click on a node in your tree, like figure 2. Note that the Add Employee and Edit actions are implemented and working. The delete action is not yet working, that will be added next.

Figure 2

Implement Delete Action

Technically the delete action is already implemented. You could edit the employee and just click delete on the next page. What sounds like more fun is to call an on demand process using AJAX from some JavaScript, and then use jQuery to remove the node from the tree, display a confirmation message, all without doing a page submit. First things first though, create a region that will hold the message specifying the following options:

Region Type: HTML

Title: Message Container

Region Template: No Template

Sequence: 5

Region Source: See Code Listing 4

Code Listing 4

view plain copy to clipboard print ?

<div class="success" id="success-message" style="display:none;">
<img src="#IMAGE_PREFIX#delete.gif" onclick="apex.jQuery('#success-message').hide()" style="float:right;" class="remove-message" alt="" />
<div id="theMessage">This is a placeholder for messagesdiv>
div>






This is a placeholder for messages

Now we have a place for messages. Next, create a process on page 1 with the following options:

Process Type: PL/SQL

Name: DELETE_EMPLOYEE

Point: On Demand – Run this process when requested by AJAX

PL/SQL Page Process: See Code Listing 5

Code Listing 5

view plain copy to clipboard print ?

begin
delete from emp2 where empno = apex_application.g_x01;
owa_util.mime_header('text/xml', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;
htp.p('{result:"success",message:"Employee successfully deleted"}');
exception when others then
owa_util.mime_header('text/xml', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;
htp.p('{result:"failed",message:"Error deleting employee: '||sqlerrm||'"}');
end;


begin

 delete from emp2 where empno = apex_application.g_x01;

 owa_util.mime_header('text/xml', FALSE );

 htp.p('Cache-Control: no-cache');

 htp.p('Pragma: no-cache');

 owa_util.http_header_close;

 htp.p('{result:"success",message:"Employee successfully deleted"}');

exception when others then

 owa_util.mime_header('text/xml', FALSE );

 htp.p('Cache-Control: no-cache');

 htp.p('Pragma: no-cache');

 owa_util.http_header_close;

 htp.p('{result:"failed",message:"Error deleting employee: '||sqlerrm||'"}');

end;

Finally, you add the JavaScript to do the AJAX call, display the result message, and remove the node from the tree. Edit the page attributes and add the code in code listing 6 in the Function and Global Variable Declaration text area before the existing doAction function.

Code Listing 6

view plain copy to clipboard print ?

function confirmSubmit(){
var agree=confirm("Are you sure you wish to continue and delete?");
if (agree)
return true ;
else
return false ;
}
function deleteEmp(pNode,pId){
var lTest = confirmSubmit();
if (lTest) {
var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=DELETE_EMPLOYEE',$v('pFlowStepId'));
get.addParam('x01',pId);
gReturn = get.get();
var j = eval("("+gReturn+")");
apex.jQuery("#theMessage").text(j.message);
apex.jQuery("#success-message").show();
if (j.result == "success") {
pNode.remove();
}
get = null;
}
}


function confirmSubmit(){

var agree=confirm("Are you sure you wish to continue and delete?");

if (agree)

return true ;

else

return false ;

}



function deleteEmp(pNode,pId){

var lTest = confirmSubmit();

if (lTest) {

 var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=DELETE_EMPLOYEE',$v('pFlowStepId'));

 get.addParam('x01',pId);

 gReturn = get.get();

 var j = eval("("+gReturn+")");

 apex.jQuery("#theMessage").text(j.message);

 apex.jQuery("#success-message").show();

 if (j.result == "success") {

     pNode.remove();

 }



 get = null;

}

}

Now run the page and unroll Clark. Right click on Miller and choose delete. Confirm the delete and the result should be similar to figure 3.

Figure 3

SOURCE

Building an Amazon S3 Client with Application Express 4.0

2011-02-26T17:11:00.000-08:00

Oracle Application Express (APEX), a feature of the Oracle Database 11g, combines rapid web application development with the power of the Oracle database. Application Builder features an easy-to-use browser-based interface which enables developers and non-programmers to develop and deploy data driven web applications in very little time.

Oracle Application Express doesn’t depend on any client software for developing or deploying web applications. Simple architecture, and browser-based features make the transition for developers and end-users seamless- simply provide the URL for the cloud environment.
The multi-tenant capabilities of Oracle Application Express allow multiple users and their associated applications to co-exist within one Oracle Database, minimizing cost. Only one instance is needed and users work in a dedicated work area called a workspace. An added advantage, when you create a database backup you also create backup of application source. Oracle Application Express can integrate with other applications by consuming web services.
Amazon Simple Storage Service (Amazon S3) provides highly available and highly scalable Internet storage. It was designed to sustain the concurrent loss of data in two facilities.2 Common use cases include content storage and distribution as well as backup and disaster recovery. Amazon S3 allows for storing objects (files) in containers called buckets and has web service API’s to allow for S3 interactions.

Oracle Application Express 4.0 introduces support for consuming RESTful web services. RESTful web services conform to a simpler architecture than traditional SOAP based web services. Oracle APEX 4.0’s support for consuming RESTful web services makes it possible to build a client on Amazon S3, using its RESTful web service API.

FULL DOCUMENT

Where Did My Saved Interactive Reports Go?

2011-02-25T17:23:00.000-08:00

Where Did My Saved Interactive Reports Go?

A problem I've seen reported numerous times from customers is that users' saved (or customized) interactive reports are missing after they import a new version of their application. This is a problem we've known about for a while with no adequate remedy. However, given the introduction of the APEX_APPLICATION_INSTALL API in Application Express 4.0, I can offer a solution. Granted, it's not an ideal answer but it's certainly a feasible and supported solution. Firstly, some explanations are in order.

When an APEX application is imported into a workspace, the very first thing that's done is the existing version of the application is completely deleted. All of the meta data associated with the application is deleted - the definition of the pages, the reports on the pages, the templates, the buttons, the branches, the shared components, everything - it's all deleted. Once this is complete, then the application meta data of the APEX application being imported is then inserted. This whole process is atomic - so if an error occurs, the transaction is rolled back and the net effect is no change.

In the case of saved Interactive Reports, it's a little bit different. Imagine you have a production instance running application 645 - you have numerous users who have saved many Customized Interactive Reports. Upon import of a new version of the application, all meta data associated with application 645 is first deleted except the Customized Interactive Reports. In essence, these are left "dangling" until the new application 645 is installed. Once the application import is complete, then the meta data of the Customized Interactive Reports will reference real interactive report definitions again.

But there's a catch. If the application ID changes upon import, then this results in totally new meta data IDs being generated. (This is done in an attempt to prevent collisions of meta data, so you can freely export your application and give to anyone in the world to use on their own APEX instance). A meta data offset number is randomly generated and added to all of the existing IDs. This is done uniformly across all of the application meta data (this is important, and you'll see why shortly). Since the IDs of all of the application meta data have changed, all of your users' customized reports in the previous version of the application are forever left orphaned until they're cleaned up by an internal APEX batch process. Yikes!

Let's look at an example. On apex.oracle.com, I created a simple application with an Interactive Report on the EMP table. I defined this application as application 70000. I then exported this application and imported it back as application 70001.

Using SQL Commands and the APEX Data Dictionary views, I ran the following queries:

view plaincopy to clipboardprint?

1. select tab_label, tab_id
2. from APEX_APPLICATION_TABS
3. where application_id = 70000
4.
5. tab_label: Emp
6. tab_id: 1573281607527253166
7.
8.
9. select tab_label, tab_id
10. from APEX_APPLICATION_TABS
11. where application_id = 70001
12.
13. tab_label: Emp
14. tab_id: 3146580610985521585

select tab_label, tab_id
from APEX_APPLICATION_TABS
where application_id = 70000

tab_label: Emp
tab_id: 1573281607527253166

select tab_label, tab_id
from APEX_APPLICATION_TABS
where application_id = 70001

tab_label: Emp
tab_id: 3146580610985521585
And the difference between the two IDs is 3146580610985521585 - 1573281607527253166 = 1573299003458268419

Let's do this again, but this time, for the APEX data dictionary view for page templates:
view plaincopy to clipboardprint?

1. select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000
2.
3. template_id: 1573270610302252883
4.
5.
6. select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001
7.
8. template_id: 3146569613760521302

select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000

template_id: 1573270610302252883

select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001

template_id: 3146569613760521302

If we once again compute the differences between these two IDs, we get: 3146569613760521302 - 1573270610302252883 = 1573299003458268419

This happens to be exactly the difference between the IDs of all of the application meta data, with the exception of the application and page IDs. All of the meta data is consistently "pushed" or offset to a new value.

How is this relevant to missing saved Interactive Reports? Simple. Since we're able to compute the offset which was used between the two applications, if we had a way to ensure that the same offset is used every time upon application import, then there would be no issue with the old saved Interactive Report IDs matching with the newly imported meta data. They would be married again. And how is this done? In Application Express 4.0, there is a new API named APEX_APPLICATION_INSTALL which enables you to control this offset value.

To ensure that I didn't lose the saved Interactive Reports on subsequent imports of application 70000 to application 70001, I included the computed offset before importing this application via SQL*Plus:

view plaincopy to clipboardprint?

1. begin
2. apex_application_install.set_application_id( p_application_id => 70001 );
3. apex_application_install.set_offset( p_offset => 1573299003458268419 );
4. --
5. -- set the alias so it doesn't collide with the alias from app 70000
6. --
7. apex_application_install.set_application_alias(
8. 'F' || apex_application.get_application_id );
9. end;
10. /
11.
12.
13. @f70000.sql

begin
apex_application_install.set_application_id( p_application_id => 70001 );
apex_application_install.set_offset( p_offset => 1573299003458268419 );
--
-- set the alias so it doesn't collide with the alias from app 70000
--
apex_application_install.set_application_alias(
'F' || apex_application.get_application_id );
end;
/

@f70000.sql

That's all there is to it. Note that I didn't have to call apex_application_install.set_workspace_id above, because application 70000 and 70001 are in the same workspace where I performed this test.

As I stated earlier, this isn't the most elegant solution on the planet and most people don't want or need to know about meta data IDs or offsets or any of this complexity. But for those experienced users who are stuck with this problem of losing saved interactive reports when migrating from one application ID to another or across workspaces or instances, this is a supported and feasible solution.

SOURCE

Application Express, Network ACLs and Oracle Database 11gR2

2011-02-25T17:22:00.001-08:00

Application Express, Network ACLs and Oracle Database 11gR2

In Oracle Database 11gR1, a new feature was introduced called Fine-Grained Access to External Network Services. Succinctly defined, this feature gives an administrator control over which database users are permitted to access external network services, and on which ports. If an application relied upon the PL/SQL packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP or UTL_INADDR, they would now need to be given permission to access the external network service via a Network ACL. An excellent writeup of this feature is on ORACLE-BASE.

There have been two changes in this feature in Database 11gR2 which may impact Application Express users. These aren't necessarily documented so prominently in the release notes or README of Database 11gR2, so I felt it necessary to share them here.

1. In Database 11gR2 11.2.0.1, the precedence order in evaluation of the network ACL entries has been changed to most specific to least specific. More about this below.

2. In Database 11gR2 11.2.0.2, the network ACL now applies to any use of DBMS_LDAP.

In the installation guide for Oracle Application Express, we document this feature in Oracle Database 11gR1 and also how to create a network access control list which permits the APEX engine to access any network service on any network port. The name of the ACL in our example is power_users.xml. It just so happened that someone else on our instance of Application Express needed access to an outbound HTTP proxy, so I created a separate network ACL for this user.

The Network ACL privileges looked like:

view plaincopy to clipboardprint?

1. SQL> column host format a30
2. SQL> column acl format a40
3. SQL> select host, lower_port, upper_port, acl from dba_network_acls;
4.
5. HOST LOWER_PORT UPPER_PORT ACL
6. ------------------------------ ---------- ---------- ------------------------------------
7. proxyserver.domain.com 80 80 /sys/acls/proxy_rule.xml
8. * /sys/acls/power_users.xml
9.
10.
11. SQL> column acl format a40
12. SQL> column principal format a30
13. SQL> select acl, principal from dba_network_acl_privileges;
14.
15. ACL PRINCIPAL
16. ---------------------------------------- ------------------------------
17. /sys/acls/proxy_rule.xml SOME_OTHER_USER
18. /sys/acls/power_users.xml APEX_040000

SQL> column host format a30
SQL> column acl format a40
SQL> select host, lower_port, upper_port, acl from dba_network_acls;

HOST LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------------
proxyserver.domain.com 80 80 /sys/acls/proxy_rule.xml
* /sys/acls/power_users.xml

SQL> column acl format a40
SQL> column principal format a30
SQL> select acl, principal from dba_network_acl_privileges;

ACL PRINCIPAL
---------------------------------------- ------------------------------
/sys/acls/proxy_rule.xml SOME_OTHER_USER
/sys/acls/power_users.xml APEX_040000

Prior to 11.2.0.1, if APEX_040000 needed to access the outbound HTTP proxy, this would be evaluated in terms of the least specific rule to the most specific rule. The ACL power_users.xml permitted access to all hosts on all ports, and thus, there were no issues. But in 11.2.0.1 and higher, this is now evaluated from most specific to least specific. If APEX_040000 now needs to access the outbound HTTP proxy, it is blocked and we'll encounter the dreaded error message "ORA-24247: network access denied by access control list (ACL)". A match for the host in the outbound HTTP proxy is found in /sys/acls/proxy_rule.xml, APEX_040000 does not have privileges on that ACL, and now the fine-grained access control blocks the request.

To correct this, I had to grant privilege on the proxy ACL to APEX_040000:

view plaincopy to clipboardprint?

1. dbms_network_acl_admin.add_privilege(
2. acl => 'proxy_rule.xml',
3. principal => 'APEX_040000',
4. is_grant => TRUE,
5. privilege => 'connect' );

dbms_network_acl_admin.add_privilege(
acl => 'proxy_rule.xml',
principal => 'APEX_040000',
is_grant => TRUE,
privilege => 'connect' );

To address the second point, in 11.2.0.2, access to the host and port specified by methods in the DBMS_LDAP PL/SQL package are now controlled via this same fine-grained access control to external network services.

I can understand, from a security perspective, why this behavior was changed. However, if you've come to rely upon this behavior, upgrading to Oracle Database 11gR2 may introduce some changed behavior when accessing external network resources.

SOURCE

APEX 4.0 Web Services Evaluation Guide

2011-02-25T17:20:00.001-08:00

APEX 4.0 Web Services Evaluation Guide

Application Express 4.0 will offer a number of improved and new Web services features including support for RESTful style Web services both from the consumer and provider perspective. This guide will explain these features and give you step by step instructions on how to use them.

Note: The instructions below are written assuming that you are using the classic version of page editing and not the Tree view. Also, some exercises will not work properly with Internet Explorer. You should use a browser that is supported for the evaluation such as Firefox.

Consuming RESTful Style Web Services

REST (REpresentational State Transfer) is a simpler architecture for accessing Web services than traditional SOAP style Web services. Parameters to a RESTful style Web service are typically passed as part of the URI and the operation is usually described by the HTTP method used: GET, HEAD, POST, PUT, DELETE). Responses are typically in XML or JSON. Application Express 4.0 provides a declarative wizard based method for creating references to RESTful style Web services. Once a reference is created, there are facilities to test calling the Web service as well as wizards to create forms to call the Web service within Application Express and reports to report on the result.
Consuming a RESTful Style Web Service with an XML Response
In this exercise you create a reference to the Yahoo Maps RESTful Web service and then create a form and report to interact with the service. The response returned is XML. You will need to sign up for a Yahoo developer key here.
1. Navigate to the Application Builder home page and click on an application or create a new one.
2. Click Shared Components.
3. Click Web Service References.
4. Click Create >.
5. Choose REST and click Next >.
6. Open https://developer.yahoo.com/maps/rest/V1/ in a new tab or new browser session. You will continue to create the reference to this Web service by reviewing its documentation.
7. Return to Application Express and enter Yahoo Map in the Name field.
8. Enter http://local.yahooapis.com/MapsService/V1/mapImage in the URL field.
9. Click Next >.
10. Enter appid in the Name field and click Add Parameter.
11. Enter location in the Name field and click Next >.
12. Leave Output Format as XML.

COMPLETE DOCUMENT

Automatic Time Zone support in Application Express 4.0

2011-02-25T17:06:00.000-08:00

Automatic Time Zone support in Application Express 4.0

A feature of Application Express 4.0 which hasn't received a lot of press but is useful for those building applications that span time zones is the Automatic Time Zone application attribute.

The Oracle database has this wonderfully rich data type called TIMESTAMP WITH LOCAL TIME ZONE. The elegance of this data type is that the value stored in this column will be displayed in the user's current database session time zone. Having written a PL/SQL package to do time zone conversion, it is a non-trival exercise to develop this type of functionality let alone maintain it. Wouldn't it be great if we could put this burden of maintaining constantly evolving time zone rules and daylight saving time dates on the database? Well, you get this for free with TIMESTAMP WITH LOCAL TIME ZONE.

So if all we need to do is set the database session time zone, then:

How do we elegantly derive this for each end user of our application?
How do we ensure that every page view and page submission in Application Express has its database session time zone set correctly for a particular user?

There were numerous suggestions in the past, of storing a user's preferred time zone as a preference and then authoring a PL/SQL block in the VPD attribute of an application like:

view plain copy to clipboard print ?

execute immediate 'alter session set time_zone =''' || :MY_USER_TIMEZONE || '''';

Not exactly obvious. And this still doesn't answer question #1 of how do we elegantly derive this. This is where the new Automatic Time Zone attribute is useful.

In the Application Builder, if you edit the Application Properties and navigate to the Globalization subtab, you should see something like:

By default, Automatic Time Zone will be set to 'No'. When set to 'Yes', this will now change the behavior of your application:

At the beginning of an Application Express session (which happens at the beginning each time a user runs your application ), the time zone offset will be calculated from their Web browser client.
This time zone offset information will be sent to Application Express and recorded in the APEX session information for that user.
Then, each and every page view for the duration of their APEX session, the Application Express engine will read this value and set the database session time zone to this value.

All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

To demonstrate this, I created a simple application using the following DDL:

view plain copy to clipboard print ?

create table tz_log(
id number primary key,
username varchar2(255) not null,
tz varchar2(512) not null,
created_ts timestamp not null );
create or replace trigger tz_log_trg1
before insert on tz_log
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
--
:new.created_ts := localtimestamp;
end;

Then, I just built an application with a SQL report on this table and added an on New Instance PL/SQL Process of:

view plain copy to clipboard print ?

insert into tz_log (username, tz) values(:APP_USER, apex_util.get_session_time_zone );
commit;

You can run this sample application here. Just keep in mind - it will require you to authenticate with your oracle.com credentials (the same credentials you use if you login to the OTN discussion forum) and it will record your visit in a log table, which others can view. Here's what it looks like - nothing fancy:

If you pay close attention, immediately after authentication, you'll see a URL like:

https://apex.oracle.com/pls/otn/f?p=27207:1:127976719236631&tz=-4:00

Obviously, your APEX session identifier and time zone value will be different than what I show above. But you'll see that there is a new parameter 'tz' to the 'f' procedure. And it is through this interface that you can create a URL to an APEX application and explicitly set the APEX session time zone to a different value. After you login, change the time zone value in the URL to something else (e.g., tz=0:00) and watch the values in the "Inserted into the Log Table (in your local time zone)" report column automatically adjust to that time zone. The underlying report definition didn't change - we're still simply selecting the TIMESTAMP WITH LOCAL TIME ZONE column out of the database, just now the database is automatically converting that value to display in the current session time zone.

You can also programmatically set and get the APEX session time zone setting using two new APIs in Application Express 4.0, namely APEX_UTIL.SET_SESSION_TIME_ZONE and APEX_UTIL.GET_SESSION_TIME_ZONE.

Oracle APEX Tutorial – A Web Service – Part 1

2011-02-09T08:48:00.001-08:00

Oracle APEX Tutorial – A Web Service – Part 1

Introduction

As the world moves forward, there is a higher need for more information. However, it is a near impossible task to create and manage all this information yourself. Sometimes, it’s easier to use freely availible services and information feeds provided by others. One such ‘feed’ is a Web Service. By sending a properly formed request to another group over the web, you can receive information back, and display it on your screen. A popular and easy to visualize example of this is getting the local Movie Theater times and titles for current movies. This tutorial will show how to do this.

Part 1 – Creating the Base Application and Services

High Level Steps

1) Create Base Application (0:30)
2) Specify Proxy Settings (optional) (2:18)
3) Create a Web Service by WDSL (2:53)
4) Dreate a Web Service Manually (6:00)

The times in parenthesis are approximate start times for that step

Notes and Resources

Web Services

Web Services are somewhat like the web itself, and can be transient in nature. Due to this, the information presented in this tutorial is accurate as of the time the tutorial was made. However, there may be changes in the Web Service link, procedures, returned data and other factors since the creation of this document. Web Services can by found by either manually entering the information needed (as we are doing in this tutorial) or by use of the UDDI (Universal Description, Discovery and Integration) registry, which is a directory where businesses have submitted thier available Web Services information for others to browse and use.

SOAP

SOAP is the abbreviation for Simple Object Access Protocol. It is an attempt to provide a platform neutral way to exchange requests and results across the Internet. The SOAP standards are maintained by the W3C, and may be superceeded in future protocol standards.

Testing Manual Services

Whenever you create and test a manual service it accomplishes two things. One it tells you if you are getting back the correct information, and two, it allows you to find the information refrences you may need later when building the displayed report. In our case, embedded in the returned results for the Movie Info service there are a few things we need to take note of. One is the ‘base node’, another is the ‘namespace’, also there is the ‘XPath’ and the ‘interesting elements’ All of these will be used in the creation of the end report, so it’s important to know them. Of course, this means that you should have a good idea of the data you need from the web service before you go invoking it.

Code and Pasted Text

WSDL Location Field

http://www.ignyte.com/webservices/ignyte.whatsshowing.webs
ervice/moviefunctions.asmx?wsdl

Manual Service Creation URL and Action

http://www.ignyte.com/webservices/ignyte.whatsshowing.webs
ervice/moviefunctions.asmx

http://www.ignyte.com/whatsshowing/GetTheatersAndMovies

Manual Creation SOAP Envelope

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tns="http://www.ignyte.com/whatsshowing"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<tns:GetTheatersAndMovies>
<tns:zipCode>#ZIP#</tns:zipCode>
<tns:radius>#RADIUS#</tns:radius>
</tns:GetTheatersAndMovies>
</soap:Body>
</soap:Envelope>

SOAP Response

MOVIE_RESULTS

Oracle APEX Tutorial - A Web Service – Part 2

2011-02-09T08:48:00.000-08:00

Oracle APEX Tutorial - A Web Service – Part 2

Introduction

As the world moves forward, there is a higher need for more information. However, it is a near impossible task to create and manage all this information yourself. Sometimes, it’s easier to use freely availible services and information feeds provided by others. One such ‘feed’ is a Web Service. By sending a properly formed request to another group over the web, you can receive information back, and display it on your screen. A popular and easy to visualize example of this is getting the local Movie Theater times and titles for current movies. This tutorial will show how to do this.

Part 2 – Creating the End Reports from the Web Service

High Level Steps

1) Create page, Buttons and Items (0:30)
2) Create the Call Process (4:38)
3) Create and test the Web Service Result Report (5:39)
The times in parenthesis are approximate start times for that step

Notes and Resources

Web Services

Web Services are somewhat like the web itself, and can be transient in nature. Due to this, the information presented in this tutorial is accurate as of the time the tutorial was made. However, there may be changes in the Web Service link, procedures, returned data and other factors since the creation of this document.Web Services can by found by either manually entering the information needed (as we are doing in this tutorial) or by use of the UDDI (Universal Description, Discovery and Integration) registry, which is a directory where businesses have submitted thier available Web Services information for others to browse and use.

Testing Manual Services

Whenever you create and test a manual service it accomplishes two things. One it tells you if you are getting abck the correct information, and two, it allows you to find the information refrences you may need later when building the displayed report. In our case, embedded in the returned results for the Movie Info service there are a few things we need to take note of. One is the ‘base node’, another is the ‘namespace’, also there is the ‘XPath’ and the ‘interesting elements’ All of these will be used in the creation of the end report, so it’s important to know them. Of course, this means that you should have a good idea of the data you need from the web service before you go invoking it.

Code and pasted Text

Result Node Path

/GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater/Movies/Mov
ie

Namespace

http://www.ignyte.com/whatsshowing

Parameter Names

Name
Rating
RunningTime
ShowTimes

Source - http://www.mandsconsulting.com/

Oracle APEX Tutorial - Access Controls - Part 2

2011-02-09T08:47:00.000-08:00

Oracle APEX Tutorial - Access Controls - Part 2

Walkthough of adding an Access Control system to an APEX application. This part shows how to actually place the access restrictions in place, and tests the application.

Introduction

Security. We hear about it everywhere. Make this more secure, make this less secure (okay, maybe we don’t hear that second part as often). In the computer industry this is also true, and a constant need for any system or application. APEX allows for basic security by user login, but there is more you might need to do. For this, we have Acess Control.

This tutorial will walk you though adding a custom authentication control setup to an application. If you haven’t already, we reccomend you review our article OracleAPEX Tutorial Preparation and OEHR Sample Data Install to familiarize yourself with APEX.
This tutorial also uses a second downloaded file, which the link is below.

Part 2 – Setting access restrictions and testing.

Location of ACL_EMPLOYEE.zip file
http://www.oracle.com/
Simply download the .zip file and extract it, keeping note of where you put the extracted file.

High Level Steps

1) Restrict access for View Users (0:30)
2) Restrict access for Edit Users (1:16)
3) Restrict access for Administrative Users (3:15)
4) Test Restrictions (4:15)
Times in parenthesis are approximate start times for that step

Notes and Resources

Authentication Options

APEX offers you three primary ways you can authenticate your users. The built in APEX option, no authentication, and by database account. All of our tutorials so far have used the APEX built in option. Within this option you can build more layers of controls, as we are in this example, or leave it as is. When this boils down to is wether or not you want to have to create APEX level accounts or program level accounts for each user. Inthis example, we are creating program level accounts based off the acl_employees data. Using this method, we don’t have to create additional APEX system accounts, and you can better contain access to the program and to the APEX system. The actual needs may vary and you should consult your IT deparment to ensure you are complying with any in-house security standards you may have in place.

Application Modes

We have the option of four modes in which the application can run in. You can choose to run a given application in one of ‘Full Access to all’ , ‘Restricted Access’, ‘Public Acess’, or ‘Administrative Access Only’. We are using the second of these, as it allows us to define view, edit and administrative levels of access and assign users to one of those groups. The mode you need can vary by application and the needs and policies of your group. Each level of access except for Full Access still allows some restriction, based on the level you chose.

Access Inclusion

When you set a specific access level as we are in this example, it means that is the minimum level needed to use that item. Any access above that is included in the permissions. IE If you go to a page that is set for view, if you have view, edit or admin rights you can use it, but if you try and go to a edit restricted apge, anyone with view rights is locked out, and only edit or higher (admin) will be able to proceed.

Source - http://www.mandsconsulting.com/

Oracle APEX Tutorial - Access Controls - Part 1

2011-02-09T08:46:00.000-08:00

Oracle APEX Tutorial - Access Controls - Part 1

Walkthough of adding an Access Control system to an APEX application. This parts builds the base application, the access controls, and the authorization functions.

Introduction

Security. We hear about it everywhere. Make this more secure, make this less secure. In the computer industry this is also true, and a constant need for any system or application. APEX allows for basic security by user login, but there is more you might need to do. For this, we have Access Controls. This tutorial will walk you though adding a custom authentication control setup to an application. If you haven’t already, we reccomend you review our article Oracle APEX Tutorial Preparation and OEHR Sample Data Install to familiarize yourself with APEX. This tutorial also uses a second downloaded file, which the link is below.

Part 1 – Creating the Base Application and Controls

Location of ACL_EMPLOYEE.zip file

http://www.oracle.com/

Simply download the .zip file and extract it, keeping note of where you put the extracted file.

High Level Steps

1) Creating and Testing the Base Application (0:30)
2) Create the Access Controls (3:30)
3) Add User to Access Control List (4:56)
4) Create and Link the Custom Authentication Function (6:10)
Times in parenthesis are the approximate start time for that step

Notes and Resources

Authentication Options

APEX offers you three primary ways you can authenticate your users. The built in APEX option, no authentication, and by database account. All of our tutorials so far have used the APEX built in option. Within this option you can build more layers of controls, as we are in this example, or leave it as is. What this boils down to is wether or not you want to have to create APEX system level accounts or program level accounts for each user. In this example, we are creating program level accounts based off the acl_employees data. Using this method, we don’t have to create additional APEX system accounts, and you can better contain access to the program and to the APEX system. The actual needs may vary and you should consult your IT deparment to ensure you are complying with any in-house security standards you may have in place.

Application Modes

We have the option of four modes in which the application can run in. You can choose to run a given application in one of ‘Full Access to all’ , ‘Restricted Access’, ‘Public Access’, or ‘Administrative Access Only’. We are using the second of these, as it allows us to define view, edit and administrative levels of access and assign users to one of those groups. The mode you need can vary by application and the needs and policies of your group. Each level of access except for Full Access still allows some restriction, based on the level you chose.

Access Inclusion

When you set a specific access level as we are in this example, it means that is the minimum level needed to use that item. Any access above that is included in the permissions. IE If you go to a page that is set for view, if you have view, edit or admin rights you can use it, but if you try and go to a edit restricted page, anyone with view rights is locked out, and only edit or higher (admin) will be able to proceed.

Code and Pasted Text

PL/SQL fuction

CREATE OR REPLACE FUNCTION acl_custom_auth (
p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
FOR c1 IN (SELECT 1
FROM acl_employees
WHERE UPPER(userid) = UPPER(p_username)
AND UPPER(last_name) = UPPER(p_password))
LOOP
RETURN TRUE;
END LOOP;
RETURN FALSE;
END;
/

Authorization Function Command

return acl_custom_auth

Source - http://www.mandsconsulting.com/

Reset INTERNAL password in APEX 3.0

2011-02-07T14:41:00.000-08:00

If you forget your APEX password of the INTERNAL workspace, here's what you can do:

Connect to your database AS SYS DBA (or a user with high privileges): sqlplus sys as sysdba
SQL> alter user FLOWS_030000 account unlock;
Connect to your database as FLOWS_030000: sqlplus flows_030000/password@your_db
Run following procedure from SQL>
begin www.flow_api.set_security_group_id (p_security_group_id => 10); www.flow_fnd_user_api.create_fnd_user( p_user_name => 'admin1', p_email_address => 'email@ae.com', p_web_password => 'admin1'); end; / commit;
Connect again AS SYS DBA
SQL> alter user FLOWS_030000 account lock;

That should have fixed your problem. Try again to login into APEX.

Creating A Popup Window

2011-02-07T14:36:00.002-08:00

Standard behaviour in APEX is for a branch to link to a page using the current window (or tab). However there are times when it is desirable for the application to link to another page in a popup window. This may be popup help, like the standard help in APEX, or maybe a print preview page. The following is how to create a popup window.

To create the popup window I am going to use Javascript. This can be placed behind a button or text link, or it can be placed in an HTML region.

The code to create the popup is very simple

window.open (URL, windowName[, windowFeatures]);

windowName
The name of the popup window. This name is not visible by the user, and is only there for reference by Javascript. For the example below I have decided to use the name popup1. If window.open was called again, using "popup1" as the windowName, then the contents of the popup would be refreshed with this new call. For this reason it is a good idea to use unique names for each window

URL
In the example code below I will simply call page 200 without passing in any page item values. If you wish to pass in values, or pass in a request value then please check out the post on URL parameters

windowFeatures
This allows the developer to define specific features for the popup window. For example, no scrollbars, the size of the window and whether certain menu bars are visible. For a full list of features visit here

The Javascript

window.open("f?p=&APP_ID.:200:&SESSION.","popup1");

The above line of code will open a new window (or tab depending on browser settings) called popup1, containing page 200. Easy.

A couple of things to note when using popups.

You will see that I have passed in the current session value to the new popup. This is useful as it allows the window to have access to all your current session values, as well as knowing that you have passed authentication. One issue with using the same session is that the two windows could be able to access(or more importantly, alter) the same session values at the same time. This can cause unexpected results.

Therefore it is sensible to have the popup as a deadend in your application. For example this could be a single page containing a print preview of the current page, with no links to any other page. For this reason it is a good idea to use a template specific to popup pages. This template should be as blank as possible and have little or no navigation items in it.

Another issue is the use of popup blockers. In most cases it is not known whether the end user is using a blocker, and how that blocker will behave when the popup is called. Unfortunately there is no easy answer to this and must be addressed on a case by case basis.

Clearing Cache

2011-02-07T14:36:00.001-08:00

In another page I explained the format of the URL.

f?p=1:2:3:4:5:6:7:8:9

Parameter number 6 allowed you to reset the cache for specific pages. Sometimes however, there is a need to reset the cache for the whole application. This can also be done using this parameter with the following two options

APP
Clears cache for all pages and all application-level items in the current application and removes sort preferences for the current user.

SESSION
Achieves the same result as the APP keyword, but clears items associated with all applications that have been used in the current session.

There is also a way to reset the cache using PL/SQL. The following code would clear the cache of application 100 for the current session

APEX_UTIL.CLEAR_APP_CACHE('100');

and the following code would clear the cache of page 100

APEX_UTIL.CLEAR_PAGE_CACHE('100');

URL Parameters

2011-02-07T14:34:00.002-08:00

In most cases the URL is automatically handled by APEX and the developer does not need to understand it. However with more advanced use of APEX comes the need to manually specify certain aspects of the URL. The following is a quick explanation of how the URL is constructed.

For this explanation we will focus on the f?p= part of the URL and explain each specific parameter

f?p=1:2:3:4:5:6:7:8:9

1 - The number or alias of the application you wish to run. The alias can be set in shared components -> definition

To access the current application id, use &APP_ID.

2 - The page number or page alias. The page alias can be set in the page attributes

3 - The session. to access the current session, use &SESSION.

4 - The request term that you want to pass to the destination page

5 - DEBUG. Decide whether the page is run in debug mode or not. either YES or NO

6 - This parameter can accept two values. RP = resets the pagination of the destination page followed by a comma separated list of pages where you wish to reset the cache. So for example if you want to reset pagination and reset the cache for pages 1 and 2 you would include "RP,1,2" for this parameter

7 - Comma separated list of page items to assign values to.

8 - Comma separated list of values to assign to the page items specified at position 7. The values must be in the same order as the page items are

9 - Printerfriendly. Determines if the page is being rendered in printer friendly mode. YES or blank

Basics of Ajax

2011-02-07T14:33:00.002-08:00

In Application Express(APEX) it is possible to communicate with the database without submitting the entire page. This is extremely useful when your APEX application is being run on a very slow network. This on demand communication is possible in APEX using Asynchronous JavaScript and XML(Ajax). Using this method sends only the minimum information required to retrieve a value from the database. I will explain using a simple example involving the famous emp table.

lets say I have a page that has three items on it

P1_EMPID. this will be a textfield used to enter the emp_id
P1_ENAME. this will be used to display the ename
SUBMIT. this will be a button that will submit the page. this page submission will fire off a process that will populate P1_DISPLAY with the relevant ename for the given emp_id

The issue with the above example is that the entire page has to be reloaded when in fact only one small section will be updated, namely P1_DISPLAY. the following example will demonstrate how to retrieve the name from the database without refreshing the page.

1. Move the database code from the page process and place it in an application process. Name the process "GET_ENAME" and give it a type of "On Demand". example code is below

declare
v_ename emp.ename%type;
begin
select ename
into v_ename
from emp
where emp_id = :P1_EMPID;
htp.prn(v_ename);
exception when others then htp.prn('An error occurred retrieving the name');
end;

2. Create the following javascript function.

function get_ename(){

/*
retrieve the emp_id from the page
*/var emp_id = document.getElementById('P1_EMPID').value;

/*
define the Ajax call. The only variable of note in this example is the application_process, which I have set to be the same name as step 1.
*/
var get = new htmldb_Get(null,html_GetElement('pFlowId').value, 'APPLICATION_PROCESS=GET_ENAME’,0);

/*
add the value in P1_EMPID into the session value for P1_EMPID. this is important as without this step the APEX server would not know
what value the user had entered
*/
get.add('P1_EMPID',emp_id);

/*
call the ondemand process and accept the returning ename
*/
var gReturn = get.get();

/*
set the field on the page to equal the ename retrieved from the database
*/
document.getElementById('P1_ENAME').value = gReturn;

}

3. Finally alter the SUBMIT button so that it uses a URL redirect and in the URL field enter

javascript:get_ename();return false;

and thats it, when you press the submit button it will now send the emp_id to the database and populate P1_ENAME with the relevant name. and all without refreshing the page.

obviously this is a trivial example but it gives an idea of the possibilities of Ajax.

File Browse - filename length limit

2011-02-07T14:33:00.001-08:00

I use the built in "File Browse" item in many of my applications and for a long time I had not experienced any issues. However, recently I noticed that there was an issue when using large filenames. If I selected a large filename and submitted the page a HTML error page was displayed. What surprised me was that this was not the usual APEX error page. After some investigation I found an interesting post on the APEX forums at Oracle.

http://forums.oracle.com/forums/message.jspa?messageID=2437324

It seems that there is a limit of 70 characters for the filename. It should be noted that this limit is solely for the filename, and does not include the path. This limit is purely down to how APEX internally stores the file in wwv_flow_files.

I have implemented the validation check described in the thread, with very slight changes to better suit my needs. The following is how I have done this.

Firstly I created the Javascript

<script type="text/javascript">
function checkFileName(pThis){
if (pThis.value != "") {
//get path value (including file name)
var fileAndPath = pThis.value;
//find the index of the last "\"
var lastPathDelimiter = fileAndPath.lastIndexOf("\\");
//get everything after the last "\"
var fileNameOnly = fileAndPath.substring(lastPathDelimiter+1);

if (fileNameOnly.length >70) {
alert("File name " + fileNameOnly +
" is too long. Filename can be maximum of 70 characters in length");
return false; //filename is not valid
}
else
return true; //filename is valid. so continue
}
}
</script>

In addition to displaying an error message if the filename is greater than 70, I decided to return a Boolean value to indicate whether the filename had passed the test or not.

This boolean could then be used along with a button to submit the page when the filename passed the test, or remain on the page if the name fails.

I created a button called UPLOAD with the following code within the URL Redirect.(F1_FILE_NAME is the name of the file browse item)

javascript:if(!checkFileName($x('P1_FILE_NAME')))return false;doSubmit('UPLOAD');

The above example is very simple but I think that implementing something similar is essential if the built in "File Browse" button is used in a production application.

Thank you goes to Mike and Arie for their examples in the aforementioned thread

How to stop the user from clicking a page submit button more than once

2011-02-07T14:31:00.001-08:00

The standard way to push the page data back to the APEX server is to use an HTML button. One issue with HTML buttons is that they can be clicked more than once, and under certain circumstances this can cause issues. To ensure that the button can only be pressed once you can do the following.

alter button settings so that inside the URL redirect section the URL target value is set to

javascript:this.disabled=true;doSubmit('SUBMIT')

Note that the value inside doSubmit('') is the same as the button name.

You can even go one step further and replace the text of the button to something relevant like "Submitting....". The following code does this

javascript:this.disabled=true;this.value='Submitting....';doSubmit('SUBMIT');

The button now looks like this when it has been clicked

Thats it, the button is now coded so that the user can only click it once to submit the page. Any further clicks are ignored.