Oracle Application Express (APEX) is a rapid web application development tool for Oracle database. Before building or running apex application, there are few concepts and terminology you need to familiarize with. The outline of this article is to cover
- Understanding User roles
- Components in Application Express
- Useful Terminology in APEX
- Running Sample application
- Understanding APEX URL Syntax
Assuming that you already have account in apex.oracle.com (If you don't, please follow instructions from APEX Introduction article), go to login page of apex.oracle.com and enter workspace name, user name and password.
Once you login successfully, home page shows different components of APEX depending upon user role assigned to you. Users are divided into four primary roles:
- Developers are users who create or edit applications
- Workspace Administrators are users who perform administration tasks specific to workspace like managing user accounts, services and monitoring workspace activity
- Oracle APEX Administrators are superusers that manage an entire hosted instance using the Application Express Administration Services application.
- End Users have no development privileges
You are given Workspace Administrator role for oracle.apex.com account. Once you login successfully, home page looks similar to below screenshot.
Four components which are of interest to us are Application Builder, SQL Workshop, Utilities and Administration.
- Application Builder is where you build apex applications. You can also import / export applications from sql files.
- SQL Workshop is like web version of SQL Developer. You can browse database objects in your schema, run SQL commands, create SQL scripts and use Query Builder to create a sql query using its GUI.
- Utilities has numerous useful tools for monitoring database, viewing meta data about APEX.
- Administration is used to manage sessions, caching, users and monitor activity.
Most part of development time is spent in Application Builder building pages, applications or SQL Workshop running queries. SQL Workshop is self explanatory if you use SQL Developer or any SQL, PL/SQL interface tool. I will focus mainly on Application Builder.
Application Builder Concepts
Application Builder has collection of apex applications. Your oracle.apex.com account comes with a sample application. You can run or modify this application.
The following terminology are important to know when working with Application Builder:
- Workspace: A workspace allows multiple developers to work within the same Oracle Application Express installation. It can be based on one or more database schemas. As a rule workspaces should be organized such that they contain applications that are related to each other.
- Application: It is a collection of pages and branches connecting them. Its attributes include authentication scheme, default UI templates. Application is synonymous to project in JDeveloper.
- Page: A page is the basic building block of an application. When you build an application in Application Builder, you create pages that contain user interface elements, such as tabs, lists, buttons, items, and regions.
- Region: Content is displayed in regions, which are logical subsections of a page. Each page can have any number of regions of several different types. These types include: HTML text, SQL Queries, PL/SQL-generated HTML, and charts. Each region is rendered using a region template. Regions are positioned on the page using display points defined in the page template.
- Item: An item can be a text field, text area, password, select list, check box, and so on. Item attributes affect the display and behavior of items on a page. For example, these attributes can impact where a label displays, how large an item is, and whether or not the item is displayed next to, or below the previous item. The value of an item is automatically stored into the application's session state, which can be referenced at any point within the user's session.
Running sample application
By default you have a sample application in your workspace. It can be used to demonstrate various features offered by Application Express like charts, reports, forms, etc., Run the sample application by clicking on 'Run Application' icon as shown in above screenshot. In the login name, enter user name as demo (or) admin with password as your workspace name in lower case.
1. URL of the page. Every page in apex is accessed through browser URL, but authorization schemes control user access. Authorization schemes can be specified for an entire application, a page, or a specific page control such a region, item or button. For example, you could use an authorization scheme to selectively determine which tabs, regions, or navigation bar entries a user sees.
URL of the home page was http://apex.oracle.com/pls/otn/f?p=44970:1:1618122951801106::NO
It includes important information about application, page, session and few more. I will cover in detail in later section.
2. Company logo/text is an attribute of Application definition. It appears on all pages in application. You can use an image or text.
3. Navigation tabs simplifies navigation to specific pages. Type of tabs (One-Level /Two-Level / None) is defined while creating application. Sample application is using One-Level tabs. Graphical representation can be used when defining tabs. Tabs can be conditionally displayed as well.
4. Navigation bar entries provide hypertext based navigation. By default it has Print and Logout entries. Placement (right / left) is determined by page templates.
5. Breadcrumb provide hierarchical navigation to any number of levels. Clicking on breadcrumb entry is associated with a page and also a parent page. Breadcrumb builds up with breadcrumb entries the deeper the user navigates to the pages. Try navigating to Add/Modify customers page by clicking on create button in Customers page, breadcrumb now shows 'Home > Customers > Add/Modify Customers' with hyper links to parent breadcrumb entries i.e. Home, Customers.
Breadcrumb adds another level for easy navigation between pages. You are familiar with it in Oracle EBS Self-Service modules.
6,7,8,9. Home page has four regions (My Quota, My Orders, Sample Application and Tasks). Each region uses different region type. My Quota demonstrates the use of Flash Dial Chart. My Orders is an interactive report which is a feature introduced in Apex 3.1. Both of these regions are based of SQL query. Sample Application region is HTML with simple text. Tasks is a list region using navigation list to branch on other pages in the application.
Interactive report gives users a plethora of personalization options like adding filters, computations, highlight columns based on conditions, save report, search by column values etc.
You can play around with Sample application to get a feel of APEX's rich features. One of the prominent feature is ease of navigation for end users using breadcrumbs, navigation tabs, drill down column links, navigation lists etc.
Once you get a hang of pages and navigation, you can look at how a page is defined by clicking 'Edit Page 1' link at the bottom of the page (or) navigating from Application Builder > Sample Application > 1 - Sample Application page.
Page Definition:
The page definition is divided into three main sections:
- Page Rendering lists user interface controls and logic that are executed when a page is rendered. Page Rendering is the process of generating a page from the database.
- Page Processing lists logic controls like computations, processes that are evaluated or executed when page is processed.
- Shared Components lists common components that are used by one or more pages within the application.
The Application Express engine dynamically renders and processes pages based on data stored in Oracle database tables. To view a rendered version of your application, you request it from the Application Express engine. When you run an application, the Application Express engine relies on two processes.
- Show Page is the page rendering process. It assembles all the page attributes (including regions, items, and buttons) into a viewable HTML page.
- Accept Page performs page processing. It performs any computations, validations, processes, and branching.
When you request a page using a URL, the engine is running Show Page. When you submit a page, the Application Express engine is running Accept Page or performing page processing during which it saves the submitted values in the session cache and then performs any computations, validations, or processes.
Page Rendering:
Each page is associated with attributes such as name, title, template, etc. In the above screenshot page template used is 'Application Default'. It is the default page template attached to application. You can check template name by going to Shared Components (second icon on top right navigation) > Application Definition > Template Defaults (section). Application Default is substituted with One Level Tabs template.
Page template defines how header, body and footer sections of the page are displayed. It has bunch of HTML tags with Substitution Strings. When the page is rendered, Application Engine replaces substitution string with actual value. Substitution strings alone takes an article to explain them in detail.
Regions are the actual containers of items in a page. Region type determines the source of the region whether it is flash chart, form, report, HTML etc. Source field of the region has either SQL query or HTML depending on region type. Region Display point determines where a region is displayed on the page. They are available depending on the page template.
Items are basic building blocks of a page. They are variety of item types to choose from such as check box, text box, radio button, display item, hidden item, etc.
Computations and Processes can have PL/SQL block for assigning values to items in the page. You can also specify at what point the logic should be executed like On Page Load, Before header. These are like triggers in Forms 6i.
Page Processing:
Global Page (Page Zero):
The URL that displays for each page identifies the location of Oracle Application Express, the address of Oracle Application Express, the application ID, the page number, and the session ID.
For Example, take the URL of Home page in Sample Application http://apex.oracle.com/pls/otn/f?p=44970:1:1618122951801106::NO
This example indicates:
- apex.oracle.com is URL of server. It could like : when run in your environment.
- pls indicates apex is using mod_plsql cartridge. If your apex installation is based of pl/sql gateway
- otn is database access descriptor (DAD) name. DAD describes how HTTP Server connects to the database server so that it can fulfill an HTTP request. The default value is apex.
- f?p= is a prefix used by Oracle Application Express
- 44970 is application being called. You can also use Application alias rather than application id.
- 1 is the page within the application to be displayed
- 1618122951801106 is session number.
- NO is debug mode.
Understanding URL syntax is useful when defining navigation to pages using navigation lists or hypertext links. I will cover more on this in coming article. There is so much ground to cover in Application Express, once you start building applications apex is mostly wizard driven and really simple.
In the coming articles, I will do tutorials possibly video tutorial to build a simple application for searching, creating, updating and deleting Person details.
Comments
APEX can be integrated with Oracle EBS to build reporting on Oracle tables, call Apps APIs, use Apps FND security or SSO to login into APEX using Apps user name and password, call web services infact BPEL, and so on.
Below article may get into details of it.
www.oracle.com/technology/products/database/application_express/pdf/Extend_Oracle_Applications_11i.pdf
Kishore
OA Framework is a platform for developing Oracle Self Service applications (web-based) using JDevelper IDE. . OA Framework needs middle-tier to be setup and deployment between environments usually require multiple setups such as uploading java classes to server, bouncing apache, running JPXImporter/XML Importer etc. OAF Developers does require knowledge of Java and understand MVC architecture. OA Framework is seemlessly integrated with EBS to use DFFs, KFFs and security rules.
Kishore
your APEX article is very useful. Currently I am learning APEX.
I am trying to create a parent tab page, but it shows the error "Tab set or new tab set must be specified.". What could be the fault ? This is my first tab page.
Kindly suggest.
When you are creating first tab, follow bottom up approach by creating standard tab first and then parent tab. Also make sure that you change page template from 'No Tabs' to 'One-Level Tabs'/'Two Level Tabs' after creating tabs.
Below links are useful.
http://proskudin.blogspot.com/2007/06/two-level-tabs.html
http://forums.oracle.com/forums/thread.jspa?messageID=1682816
http://forums.oracle.com/forums/thread.jspa?messageID=2721419
Kishore
How do we deploy the apex project from one instance to another
Thanks
J
APEX application may includes custom images, CSS for styling apart from the pages. In Application Builder, you can create separate export files for Applicaton, CSS and Images using appropriate export type. Save them in local drive or maintain them in version control repository. These export files are .sql files.
When deploying to another environment, use Import feature with previously saved file and proper import type. Export/Import is completely done through web browser.
Kisho re
Also when I run/open a Form, it's retrieving the database values like a report.
What type and template should I use while creating forms to open it with null fields ??
regards,
Ni rmal
Which form type are you using? If you use 'Form on a Table or View', Apex creates NULL text fields for selected columns. In page definition, from the select list of View field choose 'Events'. (Default is Definition). This shows events that happen during page rendering and page processing, from here you can if sql query is being executed in page rendering to populate form fields.
Kishor e
Thanks a lot for your prompt reply
Jay
Thank you. My form is appearing now with NULL fields. Earlier my form type was "Report".
I saw your video tutorial on "search page". Its really helpful. Indeed a much needed material for beginners on APEX like me.
Nirmal.
i am just beginner to Oracle application, what are the prerequisites to learn this Oracle APEX, what is ur sugessitions for begineers abt this.?
thanks,
ann
SQL and PL/SQL skills are required for APEX developers. For customizing look and feel, and for advanced features HTML, CSS, JavaScript and AJAX knowledge is helpful.
Learning APEX nicely compliments Oracle technical skills. Though APEX is not mainstream technology like OAF for EBS or ADF for Fusion Middleware, APEX cannot be ignored for its high usability and rapid web application developement. For Beginners, APEX is easy to learn and they can quickly become productive. They have to still focus mainly on domain knowledge and EBS modules.
Kisho re
Thank s a lot for ur kind reply.
Thanks,
Ann
Thank s for your tutorial and its great.
Right now we are using workspace provided by oracle at apex.oracle.com , how can we integrate this product with database on our own server.
Regard s,
JP
It is not possible to use your own database server with apex.oracle.com . You can rather install Oracle XE database and APEX in your local machine. Check the below links.
http://www.scribd.com/doc/5456221/Stepbystep-guide-to-install-Oracle-APEX-on-XE-on-Windows
http://blogs.oracle.com/SanthoshK/2008/09/oracle_xe_apex_3x_installation.html
Kishore
Thank s for your reply, any way I still have few questions.
1)W e use Oracle 9i in our company; can we install APEX on existing 9i database? If yes how can I do that?
2)How different is APEX from OAF (on terms of functionality), is OAF superior that APEX if yes how.
3)How can we visualize APEX for future ( as OAF is used in next versions of Oracle)
Thanks in advance.
Regar ds,
JP
1)We use Oracle 9i in our company; can we install APEX on existing 9i database? If yes how can I do that?
Yes. See this link http://www.oracle.com/technology/products/database/application_express/install_faq.html
2)How different is APEX from OAF (on terms of functionality), is OAF superior that APEX if yes how.
APEX uses different architecture compared to OAF (MVC Architecture). More differences b/w them are written in the above comments.
Func tionality wise APEX does whatever OAF can do, in faster development cycles.
3)How can we visualize APEX for future ( as OAF is used in next versions of Oracle)
Oracle is truly dedicated towards APEX by rebuilding some of their sites in APEX and releasing new versions annually. I totally understand no one wants to risk on a technology that vanishes in a couple of years. This Statement of Direction of APEX from Oracle, will help to build confidence of APEX.
http://www.oracle.com/technology/products/database/application_express/apex_sod.html
Oracle recently launched new Oracle Store in APEX. Check Latest Highlights section in http://www.oracle.com/technology/products/database/application_express/index.html
Kishore
Thank you for a nice article.
I have few questions.
Doe s APEX has any advantage of retreiving information from Database in comparison with OA Framework.
Do we have any modules currently on Oracle Applications which are developed on APEX.
1. Does APEX has any advantage of retrieving information from Database in comparison with OA Framework?
I have not compared APEX and OAF on performance. But APEX can built high performance applications as it resides in database. I developed applications on BI (Reporting) database of EBS, so it is smoking fast. APEX administration tools
include a lot of monitoring reports to identify bottlenecks and tune those specific pages in application.
2 . Do we have any modules currently on Oracle Applications which are developed on APEX?
No. I wouldn't think any EBS modules will be built on APEX as technology stack is changing in new EBS versions like R12. Unlike EBS 11i, R12 does not include mod_plsql as part of its technology stack. What that means to APEX applications running in 11i? Install separate Apache server with mod_plsql or use Embedded PL_SQL gateway that comes with Oracle 11g.
Kishore
HTP package can be used to build a report based for PL/SQL code. In APEX page, create a region of PL/SQL (anonymous block) type with region source using HTP package.
I created this page ( http://apex.oracle.com/pls/otn/f?p=15944:9 ) using HTP package. My region source is given below.
This comments section is removing html tags, so I had to replace '' with ')'.
BEGIN
htp.p('(table)' );
htp.p('(tr)');
htp.p('(th)Dept (/th)');
htp.p('(th)Name (/th)');
htp.p('(th)Sala ry(/th)');
htp.p('(/tr)');
FOR C IN (SELECT DEPARTMENT, NAME, SALARY FROM XX_PERSON)
LOOP
htp.p('(tr)');
htp.p('(td)' || c.DEPARTMENT || '(/td)');
htp.p('(td)' || c.NAME || '(/td)');
htp.p('(td)' || c.SALARY || '(/td)');
htp.p('(/tr)');
END LOOP;
htp.p('(/table) ');
END;
Pls let me how it worked for you.
kishore
I'm learning apex with oracle 10g express edition and I've uploaded images that I would like to use as a logo. However, when I go to application attributes->edi t application attributes->Log o and enter /i/.gif, I do not see the image when I run the application. When I browse to http://127.0.0.1:8080/i/ on my browser I cannot see the image. Can you help me? I cannot put in the path in the logo entry field either.
thx, Mark
I've to check my apex installation about using "/i/" as images. Alternatively, You can upload image in Shared Components>Imag es. Then use it as logo using substitution string as #WORKSPACE_IMAG ES#Image.gif
K ishore
-Mark
Drill down in APEX sql report can be achieved by column links. For example. Page 1 shows Sales Order report with columns like Order Number, Customer Number, Order Date and so on. If you want to drill down on Order Number to see its Order lines. Create another page 2 for Order Lines using sql and bind variable for Order Number. On page 1, create column link on order number column to navigate to page 2 and set value for Order number bind variable on page 2.
I will create a simple drill down report to show above concept.
Thank s
Kishore
Here is simple drill down report to put forward. Go to Apps2Fusion - Main application using url
http://apex.oracle.com/pls/otn/f?p=a2fmain > Login successfully > Select Random Examples application > Select Sales Order Drill Down report.
Thanks
Kishore
I have to open a new link when I click on one of the TAB.
Kindly help me. whether it is possible?
Tabs are used to navigate to page in the application. You can assign a page to standard tab, when you click on the tab apex would direct to you to that page.
I'm not sure what your requirement is? If you want to open a new link like opening google page in new window, you may have to use navigation bar items or similar items where URL target can be given.
kishore
Can anybody help me out explaining the difference in performance & concurrency capabilities of ApEx, OAF and oracle forms.It's an urgent need?
I have develop a small intranet application using Oracle-APEX. Now, I want to track client PC name and IP Address from where s/he had punched the data into my custom oracle-apex application for some auditing reason. Can you please show me any example or code to achive the task. any related example reference will also be very great help from you.
When and how do you plan to capture your client IP? Is it outside of APEX? Will you be using apex for only audit reporting?
For examples, check APEX Packaged applications @ http://www.oracle.com/technology/products/database/application_express/packaged_apps/packaged_apps.html
Kishore
Actually, I am developing a central help desk system using apex for our company. Using this system, Every end user will be able to report about bug to the help desk... Now, The main requirement here is when the user will be reporting the details of bug and press the submit button in apex application, the system should smart enough to track the actual machine name or IP from where the original issue was reported along with the reported bug.
Actually, In our ourganization the computer name is in a very logical manner. its like ___ and IP address also help us to double check.
Regards,
Tarun Sharma
with my last comment...
comp uters names are like: 78FF20_XXX. Here 78 is building number, FF means First Floor, 20 means Seat number twenty and XXX is employee code. If I would able to track the machine name then I will be able to track the location of the person who reported the problem. I know this task is not easy but help from your side will be appreciated.
Th anks & Regards,
Tarun Sharma
So you want to capture machine name not IP. You can get user session id from substitution string &SESSION. (It is also part of URL when an user logs in). In v$session table, machine column might help when tracked down using above session id.
You may also check Active sessions application report in Application Builder. This may give you some more ideas.
Kishore
Article is too good.
i am working in Oracle 10g and i have one doubt to ask.I have created one application.In that many pages exists.Now i have created one new page lets say page 1,which is under tab1.Now i want the same page to be displayed from another tab 2.I dont want to create cpy of the same page.I want the same page to appear from 2 differnt tabs at a time from only one.
Is this possible.
If possible can u give some suggestions.
T hanks
Swapna
I am newbie..Just now started learning Oracle apex..please tell me how to make a particular page as a start up page?Actually i have created a sample HOME page,that has symbol of home..i want to change that symbol to next page or start up that page as start page..Thanks in advance..If anyone knows Please answer to my mail id ashok.tbtgmail.com.
If you want to display a page say page 1 from two different tabs, then set the target for those tabs as page 1.
Kishore
If your application uses authentication, then go to page 101 (Login page) > Process Login. Under that process, change the parameter p_flow_page. Suppose you want page 9 to be your first page to be displayed after successful login. Your 101 page login process would like below.
wwv_flo w_custom_auth_s td.login(
P_UNAME => :P101_USERNAME,
P_PASSWORD => :P101_PASSWORD,
P_SESSION_ID => v('APP_SESSION' ),
P_FLOW_PAGE => :APP_ID||':9'
);
Kishore
K ishore
For an application, you can add installation scripts like table creation etc. , under Supporting objects. When you import the application in another environment, you will be prompted with whether you want to install supporting objects.
Kisho re
I created a report page in which most of the items, branches and report source is conditional and i can enter in to this page through two different buttons in two different pages so based on the branching of that two buttons i provided some values in to some items and based on that item values i made my report page conditional so every thing works fine... The problem comes at the breadcrumb region i want to display the bread crumb region based on the button through which iam entering in to this page...so its like making breadcrumb dynamic.. I tried to create a breadcrumb entry on my existing bread crumb but it doesn't allow me to do that it throws this ERROR "This Menu Identification Page already has an associated Menu Option. You must select another page." so i tried to create a new breadcrumb region and make it conditional still it allows me to create a new breadcrumb but its using the same existing breadcrumbs values.. after i created a new breadcrumb and try to edit the new one it edits the old as well as the new one ... so could you please provide me a solution for this problem...thank s in advance...
I am new to Apex and following ur blogs.. which is helping me a lot.
I have 2 basic concepts
1. Could you please explain these 3 variable usage ---- :P2_ID, &P2_ID and #P2_ID#.
2. What are Onblur, Onload process ? Can I get a list of all of them.
Thanks in advance
1. Say if P2_ID is an item in page 2. To get its value in in PL/SQL or SQL, you use bind variable : P2_ID or v('P2_ID'). v is an apex function to retrieve page item saved state. &P2_ID. is a substitution string to display P2_ID in HTML. Say if you want to display P2_ID value in an error message, you use &P2_ID. #P2_ID# is substitution string to access value of a report column P2_ID. This cannot be used outside a report.
2. Onload event is similar to WHEN-NEW-FORM-I NSTANCE trigger in Oracle Forms. This is fired during rendering of page/item. Onblur is fired when an item looses focus. For example pressing tab to navigate to next item. Check w3schools link for On events for objects.
http://www.w3schools.com/jsref/dom_obj_event.asp
Kishore
1. I was developing my skill for a "Oracle Apex Developer" role. Could you please let me know what all do I need to prepare for?
2. What are wwv_flow... and where can I find basics / list of them?
Thank s for your posting
I like to get an idea about timesheeet system.
Can uypu please get come article regarding that.
I am newbee to oracle apex.I have gone through thepackaged applications.I like to create a own system.can you give some idea please??
regar ds,
Devika
thank you
condition is found. I am using Apex 4.0.
thanks.
i tried in the region definiition areas of a statement like " ' ' || Due_Date|| '' as Due_Date, but it moves it to a string and doesn't display it correctly,
I know in the report attributes area there is a Number/Date Format line one can put some code in there but i don't see where you can make the text larger and bold.
thanks
It can be done in many ways. Easiest way is to use HTML Expression under 'Column Formatting' section in Report column attributes.
Ki shore
i have been going out of my mind can u pls help me
i have got a report with a button at the end of each row to update the row but i dont know how to call the procedure to update the row when the button is clicked
hope u will reply me back
thanks heaps in advance
I would do it in a different way by having checkbox as first column for all rows and update button at the bottom of the region.
I can check the rows to update, finally update button to update those records.
Check box can be given a field name 'f01' and value as a primary key say EmpId in html expression. Then in the update button procedure, you can loop through apex_applicatio n.g_f01 to get the rows with checkbox checked and do your logic. Check the below pseudo code.
For i in apex_applicatio n.g_f01.count
Loop
update emp set ename = where empid = apex_applicatio n.g_f01(i);
End Loop;
Kishore
I have been developing an APEX application as my final year project for a company I work for using my laptop. I need to get the application to work on my work computers which dont have Oracle. How would I go about this?
Thank you
I have an apex report with 2 columns:
Sales _Amount and Returns_Amount
Requirement: I want to highlight the Sales_Amount column to the Sales_details Report(Say Page2) and Returns_Amount to the Returns Details Report (Say Page3).
Please help.
Thanks in advance.
James
can u please help me out how to disable a button when a validation fires.
In brief,
i created one validation for duplicate record,that means if i try to create a record which is already existing then it fires that validation but i have delete button as well so instead of clicking cancel if i click on delete then original record getting deleted.so i want to try with this option once if that condition fires,then i want to disable delete button..
thank s in advance...
Is it possible to create dynamic header in a report?
Thanks
Menka
I AM FIRST TIME LEARNER IN APEX
CAN YOU TELL ABOUT APEX .I HAVE MORE INSERTED IN WED APP DENP
RSS feed for comments to this post