In my next series of articles starting with this, I will create Anil's OA Framework tutorials in Oracle Application Express. I start
with Simple Search page and add Delete, Add, Update Person functionalities. I will also share Packaged application for each of these articles, so you can import it in APEX and run it without going through step-by-step approach for creating the application from scratch. The video demo at the end of this article shows how to deploy packaged application into Oracle Application Express.
The underlining motive is to give you a perspective on how both Oracle technologies (OAF and APEX) perform in rapid application development. If you are new to Oracle Application Express (APEX), recommended readings before going any further are APEX Introduction and Basic Concepts.
Objective: Create Simple Search page using APEX 3.2. At the end of tutorial page should look similar to below screenshot.
Implementation Steps
- Create database objects with sample data in workspace schema.
- Create APEX application with Simple Search Page.
Step 1: Create database objects in Workspace Schema
I need underlying database objects available to APEX workspace schema before building application. Workspace Schema is a database schema attached to APEX workspace. A workspace can use one or more database schemas. As APEX meta data is stored in workspace schema, it is advisable to separate custom data objects for the application by storing them in different schema and create synonyms, grants to workspace schema. For example, if I'm building Sales Order report of the view oe_order_headers_v in APPS schema, I will create a private synonym in workspace schema and grant privileges to it from APPS schema.
For simplicity of this tutorial, I'm creating database objects directly in workspace schema. I'm using same scripts from OA Framework Tutorial 01 with few modifications for creating xx_person_details table, and populating it with sample data. I replaced per_people_s sequence with custom sequence xx_person_id_s in create person API and populating bigger sample data.
The scripts can be downloaded from this link.
Video for creating database scripts in APEX (4:34 min).
Step 2: Create APEX Application using Application Builder
Application Builder is used to create application and pages. I will create simple search page using wizard and edit the page to my requirement.
Video for creating and editing Simple Search page (12:04 min).
Video for explaining Page Rendering and Processing (8:10 min).
My application can be accessed using the url http://apex.oracle.com/pls/apex/f?p=62577:1
This tutorial gives you an idea of how simple and fast developing web applications can be with Oracle Application Express.
Packaged Application for Simple Search page
My Packaged applications are created using APEX 3.2 version, you can only import them into APEX with same version. This packaged application has supporting objects i.e. table and sample data, along with apex application. You can import and run it without going through the above steps.
Download Tutorial 01 Packaged Application
Video for deploying packaged application (2:41 min). This video is applicable for deploying packaged applications for my next articles as well.
The next article I will implement delete Person functionality as in OA Framework Tutorial 02.
Apps2Fusion Articles 
Comments
Thnaks so much for this valuable training posts. Please keep updated.
One question : I have developped a small application but I don't know how to deployed?
Orac le DB : 10gR2
Apex: 3.2
windows XP SP2
rgds
Nawfa l
I believe you've apex installation at your end. Where are you trying to deploy application ... does target environment has apex installation with workspace and workspace users setup? If yes, export application from source environment, save sql file and import it in target environment. Deploying packaged application video shows you show to do it.
Do you have custom images, CSS, javascript files for your application?
K ishore
when i registered in apex. oracle. com , after completing the registration i got only userid and workspace and i got one link belongs to application express. If i go through that link, iam getting serve not available. i didnt get password.
can i know what is reason?
After registration, you will get email with subject ''Approved: account request for XXXX" with workspace, user id and the URL to complete approval process. Clicking on the URL confirms workspace creation status and send another email with workspace, user id and password.
I pulled my account URL from the archive, it showed workspace creation status and when oracle sent confirmation email. If the URL doesn't respond, try again after few mins. Also make sure that those mails do not end up in your email spam folder.
Kishor e
As of know I have developped one small application on my machine (local) using my own database with APEX 3.2 my question is how I deploy this application so other end user can access it from their machines?
wich URL I have to give them?
localy I'm using this URL://127.0.0.1 :7777/pls/apex/ f?p:101:1 it works and I can access to my application
wh ere I'm missing steps?
Thanks a lot for updates.
rgds
Nawfal
You need a static IP address to expose your apex applications to public. You can contact your ISP provider or find APEX hosting websites. The following URL has list of apex hosting companies.
http://www.oracle.com/technology/products/database/application_express/html/apex_com_hosting.html
If you are learning APEX or doing prototypes, you can use apex.oracle.com to build prototype/demo applications and share with the audience.
Kish ore
I am new to this application and your article has been really great to start with for the new developers.
I have a small question here for you. When we access the oracle express we have the workspace created for us with few sample tables. I wanted to work with few of the standard table of an AR application, do we have any feature to connect to my own database and access those tables or any way where i can import those table data into my workspace? Basically i wanted to work and create screens based on those applications.
Thanks
Abdul.
You are right, workspace schema comes with tables and views for APEX meta-data and demonstration application. To access EBS tables, you have to create synonym in workspace schema and give grants to it.
Suppose you want to built a report on view AR_CUSTOMERS_V in Oracle Application Express (workspace schema is XXAPEX), steps involved before creating report are:
1. Create synonym on AR_CUSTOMERS_V in XXAPEX schema.
If you are using same database for EBS and APEX, run below script in XXAPEX schema
CREATE SYNONYM XXAPEX.AR_CUSTO MERS_V on APPS.AR_CUSTOME RS_V
If you have different databases for EBS and APEX, run below script (has dblink) in XXAPEX schema
CREATE SYNONYM XXAPEX.AR_CUSTO MERS_V on APPS.AR_CUSTOME RS_V@ebs_dblink
2. Grant privileges to XXAPEX. run below script in APPS schema
GRANT SELECT ON APPS.CHP_BI_NEW S_V TO XXAPEX;
Unfort unately you cannot practice these scripts in Oracle apex hosting site (apex.oracle.co m). I will do an article on it.
Thanks
Kis hore
Thanks for the quick reply and i am eagerly waiting for that article..:).
One additional thing ,Lets say i have developed an application using Oracle Apex me as a user i can run the page and see the functionality, when i want to deploy
this to users how do we do that, i mean how will they login and how to check that feature with our application. Can you please explain the flow of it.
Thanks
Abd ul.
Thanks for ur reply regarding my question. Please keep updating of the next. Yes, i got the confermation mail and password and workspace name after few minu........... ...
Now i am able to run the application. Thanks for ur quick reply RK...........
Bye .
I assume you want only a set of users to access or run your apex application. If that is the requirement, you have to implement Authentication Scheme for your application. Follow these steps.
1. Go to Shared Components > Authentication Scheme (Under Security section). Create a scheme from pre-configured schemes.
No Authentication scheme uses user name/password in Database Access Descriptor (DAD) file. PUBLIC_USER is user_name. That is the reson you see PUBLIC_USER on top left in Search Person application.
Application Express scheme uses your apex user name and password.
To use EBS users (fnd_users), define custom authentication scheme.
2. Go to Shared Components > Definition (Under Application section). Click on Security tab, Under Authentication click on Authentication Scheme.
3. This takes you to Authentication schemes (step 1). Click on Change Current tab and select scheme to want.
Now you have authentication scheme set for your application. More articles coming on this.
Kishore
Kishore
Foremost , thanks for sharing all your valuable experience and for spending your time.
Could yo please explain the usage of packages which we are mentioning in all the phases of creating BC4J?
Thanks in advance.
Sarava nan S.
I hope you are referring to Business Components Package in OA Framework, if not please state your question clearly. Business Components Package is a container for all BC4J components (VO, EO, AM, View Link, etc.). It helps with modular approach for development.
Kishore
I have question on the search page implementation, when we search using any of the column in the resulting table we get results based on the values present in all the column, example if i search by order_id with value 1, the page is searching with value 1 in all the columns like order_id,custom er_id etc., i want my search only on one column how do i achieve that...?
Thank s
Abdul
Please modify WHERE clause of the report query to compare your search field with only order_id.
Kish ore
How do I deploy an apex so inorder to access it over a local area network,though i can acess the application on my machine via the URL: http://127.0.0.1:7777/pls/apex/f?p:101:1. and this link doesnt work on other machines on the network.
Jeff
127.0.0. 1 is localhost IP. So when you try from another component on LAN, use your IP address. Assume your IP on LAN (Use ipconfig command), is 10.12.34.1. Try http://10.12.34.1:7777/pls/apex/f?p:101:1.
Kishore
Thx for ur response to my last inquiry but I tried it and couldn`t connect to the application login page.Just like I said earlier,I created the application on my machine and the login page address is:http://127.0 .0.1:8080/apex/ f?p=100:1:14690 26532281424:::: :.I did IPCONFIG and got my IP address as 172.12.11.41,re placed 127.0.0.1 with it but still couldn`t connect.
I`d like to know how I would be able to access the same application from another machine,either over the LAN or internet.
Anti cipating ur usual prompt response
Try pinging your private IP i.e. 172.12.11.41 from another machine over LAN. Is your port number 8080 or 7777? In your first question, you mentioned it is 7777.
Kishore
my port no. is 8080,i just used 7777 as an example.am using oracle xe and am wondering if it has oracle http server bundled with it.If http server has anything to do with the problem,can i use apache cos` its more simplier to download n instal as a standalone.
Any other suggestions from are welcome
THX
Please check point 14 in page 22 of this document. http://www.scribd.com/doc/5456221/Stepbystep-guide-to-install-Oracle-APEX-on-XE-on-Windows
Let me know if it solves your issue.
Kishore
Were you able to ping to your IP from other machine? I installed APEX on XE to simulate your issue. I found my Mcafee firewall is blocking ping requests. I turned firewall off, and I could access apex sample application over LAN. Hope this helps.
Kishore
Another one, can it talk 2 excel worksheet,where by i update the excel doc n it automatically updates on my application in apex???
You know create apex application based on spreadsheet. You can upload data in existing table or a new one in the process. What you asked for .. requires syncing apex application and spreadsheet which I haven't come across. It can be worked out by uploading your updated spreadsheet into apex application and Apex updates corresponding table.
Kishore
To make your apex application accessible over Internet, you need use public IP for which you have to contact your ISP provider (or) APEX hosting sites. The following URL has list of apex hosting companies.
http://www.oracle.com/technology/products/database/application_express/html/apex_com_hosting.html
Kishore
Kishore
Thanks.
J ohn
where u execute that command dbms_xdb....... ... i am not able to access my application over other m/c with in LAN..help me plz
You have to execute command in SQL plus.
execute dbms_xdb.setLis tenerLocalAcces s(l_access => FALSE) ;
Please refer this document http://www.scribd.com/doc/5456221/Stepbystep-guide-to-install-Oracle-APEX-on-XE-on-Windows
Kishore
After watching 3 of your tutorials I was inspired to learn more about Oracle and its Application Express. I was always intimidated by Oracle but no more.
I am about to watch the others . I am impressed with your delivery of the tutorials.
Tha nks again
Missy
so , I will search the data in table X and fill the fields with these returning data , but these form fields will insert
into another table,
could you help me
I'm not sure if I understand your question. Can you put an example on apex.oracle.com ?
Kishore
with the fields : emp_cod , emp_name ( it will be duplicated ) , sports_choose ( radio item ) , color_choose, etc,
the emp_name , emp_cod ( hidden field ) will be filled automatic after the search emp_cod from emp table and will not allow to change , to insert into EMP_SURVEY , also after search in emp_cod I like to go to the form EMP_SURVEY.
Th anks.
Looks like you search using different search fields for employees from EMP table. After the search results, you may use column link on emp_name to drill to different page to insert/update record in EMP_SURVEY for that employee.
Is that what you want to do?
Kishore
I think it is what I'd like to do,
do you have an example?
thank you very much.
Person Details demo (above article) does the same thing except that edit page is built on same table. For testing this application, you may use the url http://apex.oracle.com/pls/otn/f?p=a2fmain , login to the app and choose Person Details responsibility.
Kishore
Is there any link from where I can download the video for search page etc . I cannot connect to Internet full time so I need to check them when I am not connected .
RSS feed for comments to this post