Adding Lookup table in oracle

Adding a Lookup Table to Your Application
You have also done the steps in this section before, so you can go through them quickly this time through.
1. Return to your application and click on the master-detail page.
2. Click on the item for CUSTOMER_NAME.
You will want to make three changes to the attributes of this field.
3. In the Name section of the page, change the Display As value to Select List by clicking on
the Select List link below the field.
4. Click on Source at the top of the page to isolate the attributes of the item.
You can see that this item is bound to the CUSTOMER_NAME column in the ORDERS table. If you tried to
run this page now, you would get an SQL error, since this column no longer exists in that table. You
have to change the source of the item, and you could also change the name of the item to reflect the new
source. For the sake of simplicity, the examples in this book will leave the name of the item as it stands.
Change the Source value or expression to CUSTOMER_NAME_ID, the name of the new column
in the ORDERS table.
6. Click the LOV button to bring up the section of the attributes concerning LOVs.
7. Click on the Create Dynamic List Of Values link at the bottom of the section.
8. In the wizard for creating dynamic LOVs, accept the default schema and click Next.
9. Select the CUSTOMERS table in the next page, and click Next.
10. Select CUSTOMER_NAME as the Display Column and CUSTOMER_NAME_ID as the Return
Value, and click Next.
11. Accept the generated SQL, and click Finish. Click Apply Changes.
You have added the LOV to the edit page for the ORDERS table. As with the use of the lookup table in the
previous chapters, you will also want to add the new column to the initial report by modifying the SQL
source for that report.
12. Return to the main development page for the application, and click on the initial interactive
report created for the ORDERS table. If you are doing the exercises for this book in order, this
page should be Page 1.
13. Click on the Region Definition tab.
14. Change the SQL in the Source section from:
select
“ORDER_ID”,
“ORDER_TIMESTAMP”,
“CUSTOMER_NAME_ID”,
from “ORDERS”
to the following code:
select
“ORDER_ID”,
“CUSTOMERS”.”CUSTOMER_NAME”,
“ORDER_TIMESTAMP”
from “ORDERS”, “CUSTOMERS”
where “ORDERS”.”CUSTOMER_NAME_ID” = “CUSTOMERS”.”CUSTOMER_NAME_ID”

Exporting table In application Express

Exporting the Table
Now that you have set up the DSN, the Access Export wizard can use it. If you select a table in
your Access database and choose ODBC Databases from the Export menu, you will see the dialog
box shown in Figure 2-25. This dialog box allows you to name the table that will be created
by the Export wizard. For this example, we selected the Customers table and accepted the
default name.
Next, the wizard will present you with a list of available DSNs. For this example, we
selected the one we created in the previous section, DBTEST, as shown in Figure 2-26. When
you select the DSN, another dialog box appears, where you enter the username, password, and
server. The username field will be filled in with the default value if you supplied one when you
created the DSN. All you need to do here is to enter the password and click OK. The wizard will
migrate the table across to your Oracle database.
If you experience any problems at this stage, the error message that you get from the
Access Export wizard may help you figure out what went wrong. Double-check that you have
entered the correct username and password, and that the Server entry you used matches the
TNSNAME entry you configured.
You can repeat the process to export the data from each table that you want to migrate.
Once you have migrated all the data, you will then be able to create your application using the
Application Builder.

Installing the Oracle Client Software

To be able to configure the ODBC connection, you first need to have installed the Oracle client
software and libraries on the machine from which you are exporting the data (the Windows XP
machine in this example). You can use many different ODBC drivers, including ones from
Microsoft, Oracle, or third-party suppliers.
We found that the easiest way to get up and running was to install the Oracle client software
so that we could connect from the Windows machine to the database machine using the
Oracle client tools, such as SQL*Plus. We then set up a TNSNAME entry on the Windows machine
to point to the database running on the Linux machine. Figure 2-21 shows connectivity being
tested with the Oracle TNS Ping Utility, followed by a SQL*Plus session established from the
Windows machine to the database on the Linux machine.
It is extremely important to test each step of a configuration like this independently. If you
don’t do this, it becomes very difficult to diagnose where the problem lies if the export fails.
Setting Up the Data Source Name
Now that we can connect from the Windows machine to the database, the next step is to set up
an ODBC connection to the database. For this, we use the ODBC Data Source Administrator
application. As shown in Figure 2-22, Data Sources (ODBC) is available as a Control Panel
applet within the Administrative Tools section.

Creating New Page

At the top of t his page, you can see four icons used to run your applicat ion and deal with administrative
tasks connected with the application. You will learn more about the three icons for Supporting Objects,
Shared Components, and Export/Import later in t he book.
You can also see the pages t hat were automatically generated for you when you c reated the application.
You could double-click on any of t hese pages to modify its appearance or functionality, which you will
be doing in the next chapter. Right now, you want to create a new page for t he application.
2. Click Create Page to bring up the page shown in Figure 2-27, which is where you will begin t he
process of creating a new set of pages to handle the ORDERS and ORDER_ITEMS tables together.
3. The page shown in Figure 2-27 gives you a number of choices for the type of pages you want to
create. Select the Form choice and click Next.
4. The next page gives you a number of choices for the type of form you want to create. Select
Master Detail Form and click Next, or simply click on the Master Detail icon.
You will have to go through a few steps to completely de ne the functionality implemented in the
master-detail forms. Although each step is quite easy, the left-hand panel displays a list of the steps,
and where you are in the process, to keep you informed of your progress.
5. The next page, shown completed in Figure 2-28, collects informat ion about the master table.
Select the ORDERS table from the selection list.
6. Select all the columns in t he Available Columns list, and click on the right arrow button to
move t hem to the Displayed Columns list. Click Next.
7. The next page asks you for t he same information about the detail table. Select the ORDER_ITEMS
table in the selection list and move all the columns to the Available Columns list. Click Next.
The next t wo pages will con rm the source of t he primary key value for each of the tables. Bot h tables
use a sequence to produce the primary key.
8. On the next page, shown in Figure 2-29, select Existing Sequence and t he ORDERS_SEQ sequence
from the selection list t hat appears. Click Next.

Building an Application with Application Express

You can accomplish a lot on this page. You can specify any number of pages that will be part of your
application with just a few clicks. Since you only have one table available right now, you will only create
one set of pages but with a more fully populated database, you could create fairly sophisticated applica-
t ion systems with the point-and-click methods of this page.
That’s it! You will be presented with the Summary page shown in Figure 2-15, listing the defaults
applied to your application. These defaults include specifying one level of tabs, a standard method of
authenticating users, and a standard template. You can change any of these, or you could have selected
t hem during t he application creation process by clicking Next on the Create Application page shown
in Figure 2-14. You can also access the edit pages for these choices by using the Previous button on
the page.
This page gives you t he option of changing the template for the application, which provides the basic
look and feel for the end user. The examples in this book will use Theme 13. Later in this book, you will
learn more about templates and how to use them.

Building an Application with Application Express

Building an Application with Application Express
1. Log into your APEX-hosted account
2. Click on the Utilities icon, and use the cascading menus to select Data Load/Unload, and thenthe Load choice
3. Click on the Load Spreadsheet Data icon in the Load page, which will bring up the page shown
4. On this page, accept the (Load To) New table choice, but change the Load From choice toUpload File. Click Next once you have made this change.
5. The next page, shown in Figure 2-5, prompts you for the name of the file to use as a data source.
6. Click Browse and locate the ORDERS.csv file. The default values for other choices on this pag can be left with their default values for this file. Click Next to bring up the next page.

You can also see a dropdown box asking for a schema. In Oracle terms, a schema is equivalent to a database
–– a set of tables that can be directly accessed, without further name qualification. You defined a
schema when you created your online account in the last chapter.
The next page, shown in Figure 2-7, has a few choices that control how APEX will implement logic for
this particular table. In relational terms, every row in a table must have a primary key. The primary key
is used to identify the particular row of the table. On this page, you identify either a new or existing
column to hold the primary key value. APEX also asks for the name of a constraint to use to implement
the primary key. A constraint is simply a database specification that indicates, in this case, that the particular
column is a primary key.
Finally, the page asks if you want to use a sequence to generate primary keys. APEX applications
require that each row in a table will have a unique primary key value. The Oracle database includes
an object that is ideal for generating unique values — a sequence, which is roughly equivalent to an
Autonumber data type field in Access. A sequence object performs a simple task––each time you ask
the sequence for a number, the sequence provides the next consecutive number.
The page shown in Figure 2-7 allows you to specify the creation of a new sequence to generate a primary
key, use an existing sequence, or not generate a sequence, which requires you to provide your

Sign Up for a Hosted APEX Account

How t o S ign U p f or a H osted A PEX A ccount
Oracle Corporation provides free hosted APEX accounts for your trial use. Signing up is an easy process
that will take you less than 2 minutes.
Open your browser and go to http://apex.oracle.com, which is shown in Figure 1-4.
Figure 1-4
Click on the ❑ Sign up for an account link underneath the big red Login button, which will
bring up the first page of the Signup Wizard, shown in Figure 1-5. This page is also your first
view of an APEX application.
Click on Next to move from the Welcome page to begin the process. ❑
Your first step, shown in Figure 1-6, is to identify the person who will be the administrator of ❑
the hosted Oracle APEX account. Enter your name and email address, and click Next.
Oracle Application Express can support multiple workspaces to give you and your organization the ❑
ability to segregate work on different applications. The next page of the Signup Wizard prompts you
for a workspace. Enter a name you can remember, and click Next. If the workspace name you enter
is not unique, you will receive an error message and be prompted to enter the name again.

In the next step, you are prompted for the name of a ❑ schema. Oracle Application Express exists
within an Oracle database. An Oracle schema is a collection of tables and objects — similar to
the concept of a database in Access. Give your schema an appropriate name, the easiest being
the same name as your workspace.
The schema name must not exceed 30 characters, cannot contain spaces or quotation marks, and must
be unique for the hosted version. If you select a schema name that violates any of these restrictions, you
will get an error and be prompted to select another name.
Leave the default initial space allocation of 10MBs. Although an Oracle database can scale to ❑
handle many terabytes of data easily, you will not need that much space for your initial explorations
of APEX. Click Next.
The final step in your signup process is to let the folks at Oracle know why you want to obtain ❑
a hosted APEX account. Enter your reason, which is to work on the exercises in this book, and
click Next.
The final step should be familiar to web users. You are asked to enter a verification code, which ❑
ensures that a human is making the request, rather than an automated program. Match the text
and click Submit Request