Apps To Fusion

.......Our Journey from Apps To Fusion

 
  • Increase font size
  • Default font size
  • Decrease font size



Oracle APEX - Person Details Audio Video Tutorial 01 (Search Page)

Wednesday, 08 April 2009 03:32 Kishore Ryali
E-mail
User Rating: / 2
PoorBest 

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

  1. Create database objects with sample data in workspace schema.
  2. 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).


Search page created in APEX looks like in below screenshot. 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.


Comments (38)add
Thanks so much
written by Nawfal Mami , April 08, 2009
Hi,

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?

Oracle DB : 10gR2
Apex: 3.2
windows XP SP2

rgds
Nawfal
report abuse
vote down
vote up
Votes: +0
Re: Deploy apex application
written by Kishore Ryali , April 08, 2009
Hi Nawfal,

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?

Kishore
report abuse
vote down
vote up
Votes: +0
hi
written by Mohanrao , April 08, 2009
hi this is very good tutorial which u kept for us.

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?


report abuse
vote down
vote up
Votes: +0
Re: apex.oracle.com registration
written by Kishore Ryali , April 08, 2009
Mohan,

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.

Kishore
report abuse
vote down
vote up
Votes: +0
Re: Deploy apex application
written by Nawfal Mami , April 08, 2009
Hi my friend,
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

where I'm missing steps?

Thanks a lot for updates.

rgds
Nawfal
report abuse
vote down
vote up
Votes: +0
Re: Deploy apex application
written by Kishore Ryali , April 08, 2009
Nawfal,

You need a static IP add***s 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/p******s/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.

Kishore
report abuse
vote down
vote up
Votes: +0
Great Article and Great Going
written by abdul , April 09, 2009
Hi RK,

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.
report abuse
vote down
vote up
Votes: +0
Re: Accessing EBS tables/views in APEX workspace
written by Kishore Ryali , April 09, 2009
Hi 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_CUSTOMERS_V on APPS.AR_CUSTOMERS_V

If you have different databases for EBS and APEX, run below script (has dblink) in XXAPEX schema
CREATE SYNONYM XXAPEX.AR_CUSTOMERS_V on APPS.AR_CUSTOMERS_V@ebs_dblink

2. Grant privileges to XXAPEX. run below script in APPS schema
GRANT SELECT ON APPS.CHP_BI_NEWS_V TO XXAPEX;

Unfortunately you cannot practice these scripts in Oracle apex hosting site (apex.oracle.com). I will do an article on it.

Thanks
Kishore
report abuse
vote down
vote up
Votes: +0
...
written by abdul , April 09, 2009
Hi Rk,

Thanks for the quick reply and i am eagerly waiting for that article..smilies/smiley.gif.
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
Abdul.
report abuse
vote down
vote up
Votes: +0
hi Rk
written by Mohanrao , April 09, 2009
hi Rk,

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 .
report abuse
vote down
vote up
Votes: +0
Re: Authentication Scheme
written by Kishore Ryali , April 09, 2009
Abdul,

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
report abuse
vote down
vote up
Votes: +0
Re: Mohan roa
written by Kishore Ryali , April 09, 2009
I will create and publish video on Delete functionality tonight when I go home.

Kishore
report abuse
vote down
vote up
Votes: +0
Packages
written by SaravananSeshan , April 13, 2009
Hi Anil,

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.
Saravanan S.

report abuse
vote down
vote up
Votes: +0
Re: Packages
written by Kishore Ryali , April 13, 2009
Saravanan,

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
report abuse
vote down
vote up
Votes: +0
...
written by abdul , April 14, 2009
Hi RK,

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,customer_id etc., i want my search only on one column how do i achieve that...?

Thanks
Abdul
report abuse
vote down
vote up
Votes: +0
Re: Search Orders
written by Kishore Ryali , April 14, 2009
Abdul,

Please modify WHERE clause of the report query to compare your search field with only order_id.

Kishore
report abuse
vote down
vote up
Votes: +0
publishing apex on a local network
written by Jeff` , April 17, 2009
Hi,
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
report abuse
vote down
vote up
Votes: +0
Re: publishing apex on a local network
written by Kishore Ryali , April 17, 2009
Jeff,

127.0.0.1 is localhost IP. So when you try from another component on LAN, use your IP add***s. 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
report abuse
vote down
vote up
Votes: +1
...
written by Jeff` , April 22, 2009
Hello,
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 add***s is:http://127.0.0.1:8080/apex/f?p=100:1:1469026532281424:::::.I did IPCONFIG and got my IP add***s as 172.12.11.41,replaced 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.

Anticipating ur usual prompt response

report abuse
vote down
vote up
Votes: +0
Re: publishing apex on a local network
written by Kishore Ryali , April 22, 2009
Jeff,
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
report abuse
vote down
vote up
Votes: +0
...
written by Jeff` , April 23, 2009
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


report abuse
vote down
vote up
Votes: +0
Re: publishing apex on a local network
written by Kishore Ryali , April 23, 2009
Jeff,

Please check point 14 in page 22 of this document. http://www.scribd.com/doc/5456...on-Windows

Let me know if it solves your issue.

Kishore
report abuse
vote down
vote up
Votes: +0
Re: publishing apex on a local network
written by Kishore Ryali , April 23, 2009
Jeff,

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
report abuse
vote down
vote up
Votes: +0
...
written by Jeff` , April 24, 2009
Thx...Ur d Man!! It worked !! I just had 2 use the command to enable the access: execute dbms_xdb.setListenerLocalAccess(l_access => FALSE) ;

Another one, can it talk 2 excel worksheet,whereby i update the excel doc n it automatically updates on my application in apex???


report abuse
vote down
vote up
Votes: +0
...
written by Jeff` , April 24, 2009
Also I`ll like to know if apex can be accessed over the internet with the same add***s ie. http://172.17.11.41:8080/apex/f?p=100:1
report abuse
vote down
vote up
Votes: +0
Re: APEX integration with MS Excel
written by Kishore Ryali , April 24, 2009
Jeff,

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
report abuse
vote down
vote up
Votes: +0
Re: Accessing your apex application over Internet
written by Kishore Ryali , April 24, 2009
Jeff,

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/p******s/database/application_express/html/apex_com_hosting.html

Kishore

Kishore
report abuse
vote down
vote up
Votes: +0
Deploying an application
written by JohnJavier , May 02, 2009
Kishore, I would like to thank you personally for sharing your valuable experience with us. My question is, could you please explain to me how I can deploy an application to a different schema so that users don't have access to the (Home,Application 314,Edit Page 101,Create,Session,Activity,Debug,Show Edit Links) that usually appears at the bottom of the application page?
Thanks.
John
report abuse
vote down
vote up
Votes: +0
Problem in accessing over other m/c
written by ashish12345790 , May 05, 2009
jeff,

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
report abuse
vote down
vote up
Votes: +0
Re: Problem in accessing over other m/c
written by Kishore Ryali , May 14, 2009
Ashish,

You have to execute command in SQL plus.
execute dbms_xdb.setListenerLocalAccess(l_access => FALSE) ;

Please refer this document http://www.scribd.com/doc/5456...on-Windows

Kishore
report abuse
vote down
vote up
Votes: +0
Thanks Kishore
written by missy , August 03, 2009
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.

Thanks again
Missy
report abuse
vote down
vote up
Votes: +0
...
written by Marcel , December 08, 2009
Hi , I'd like to create a search field , but the based table is diferent from search table field,
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
report abuse
vote down
vote up
Votes: +0
Re: Marcel
written by Kishore Ryali , December 08, 2009
Hi,

I'm not sure if I understand your question. Can you put an example on apex.oracle.com?

Kishore
report abuse
vote down
vote up
Votes: +0
...
written by Marcel , December 08, 2009
I have a Reference Table called EMP with the fields emp_cod, emp_name, add***s and I have a new table called EMP_SURVEY
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.

Thanks.
report abuse
vote down
vote up
Votes: +0
Re: Marcel
written by Kishore Ryali , December 08, 2009
Hi

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
report abuse
vote down
vote up
Votes: +0
...
written by Marcel , December 08, 2009
Hi , thank you for you fast response,

I think it is what I'd like to do,
do you have an example?

thank you very much.
report abuse
vote down
vote up
Votes: +0
Re: Marcel
written by Kishore Ryali , December 08, 2009
Hi

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
report abuse
vote down
vote up
Votes: +0
Download tutorials
written by Ankur_Sar , September 09, 2010
Hi 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 .
report abuse
vote down
vote up
Votes: +0
Write comment
quote
bold
italicize
underline
strike
url
image
quote
quote
smile
wink
laugh
grin
angry
sad
shocked
cool
tongue
kiss
cry
smaller | bigger

security image
Write the displayed characters


busy
Last Updated ( Wednesday, 01 December 2010 20:34 )