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.
Kamis, 18 Juni 2009
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.
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.
Rabu, 17 Juni 2009
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
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
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
Introducing Oracle Application Express
Introducing Oracle Application Express
Welcome to the world of Oracle technology. The book you are holding will give you all you need
to start creating powerful, flexible, highly productive applications for yourself and your users.
And the software included with this book will give you the path to even greater capabilities through a
broad range of Oracle software. You are going to like it here.
This chapter will introduce you to the core technology you will be using to create applications, Oracle
Application Express. The rest of the book will walk you through the process of creating an application
with a hosted version of this product, finishing up with information on expanding your environment
with packaged applications and migrating existing applications to your new platform.
But first, a few basic questions and their answers.
What I s O racle A pplication E xpress?
The focus of this book, like its title, points to Oracle Application Express. What exactly is Oracle
Application Express, also known as Oracle APEX?
Oracle Application Express is a rapid application development (RAD) tool built on Oracle technology. Oracle
APEX runs in an Oracle database instance and comes as a no-cost option with all Oracle databases.
APEX runs entirely in a browser and does not require any software to be loaded onto your client machines.
You develop your applications from a browser-based environment, as shown in Figure 1-1, and deploy
your applications to a browser-based environment.
Oracle Application Express is a declarative development tool. As you will see throughout this book,
APEX development consists of assigning values for properties which describe the various components
of your applications. The Oracle APEX environment does the rest for you.
For most components, you will use wizards to guide you through the process of creating those components.
After this initial creation, you have access to these properties to modify or shape the operation of
your applications, combining ease-of-use and productive maintenance in the same development tool.
All APEX development is data-driven. Tight integration with the Oracle database which hosts APEX
means that your applications will naturally flow from your data designs. For instance, APEX applications
automatically understand and implement relationships between tables, generating fully-featured applications
without additional programming overhead.
Oracle Application Express is an all-in-one development tool. You use the same techniques to create
forms, reports, and charts, as well as integrating these components together with navigation methods.
Your Oracle APEX environment gives you all you need to create virtually any application through an easy
declarative development process. However, APEX is also extensible, so you can use HTML to modify the
presentation of your APEX-generated client interfaces and Oracle’s standard PL/SQL procedural language
to supply additional logical operations on the back end.
When you start to use Oracle APEX, you will quickly discover that you are not alone. The Oracle
Technology Network (OTN) has a vibrant community of APEX users and experts, who regularly contribute
leading to continual improvement in best practices and technical solutions. In addition, you will
find a large collection of application packages that have already been created with APEX, readily available
for you to integrate in with your own applications.
Last, but certainly not least, Oracle Application Express gives you a way to protect the investment you
have in all those personal applications spread throughout your organization. You can upload data from
an Excel spreadsheet with a few simple mouse clicks, moving crucial information into an Oracle database.
You can even generate a complete application to interact with that data with a few more clicks,
completing the task in less than 10 minutes.
Oracle also provides a migration tool to help you move your Access applications to Oracle APEX, migrating
from a personal database with limited scalability to the world’s most powerful database.
Ready to start yet? This book contains all you will need to jump into the world of APEX-created applications.
But first, a little bit about the application you will be creating in the remainder of this book.
Welcome to the world of Oracle technology. The book you are holding will give you all you need
to start creating powerful, flexible, highly productive applications for yourself and your users.
And the software included with this book will give you the path to even greater capabilities through a
broad range of Oracle software. You are going to like it here.
This chapter will introduce you to the core technology you will be using to create applications, Oracle
Application Express. The rest of the book will walk you through the process of creating an application
with a hosted version of this product, finishing up with information on expanding your environment
with packaged applications and migrating existing applications to your new platform.
But first, a few basic questions and their answers.
What I s O racle A pplication E xpress?
The focus of this book, like its title, points to Oracle Application Express. What exactly is Oracle
Application Express, also known as Oracle APEX?
Oracle Application Express is a rapid application development (RAD) tool built on Oracle technology. Oracle
APEX runs in an Oracle database instance and comes as a no-cost option with all Oracle databases.
APEX runs entirely in a browser and does not require any software to be loaded onto your client machines.
You develop your applications from a browser-based environment, as shown in Figure 1-1, and deploy
your applications to a browser-based environment.
Oracle Application Express is a declarative development tool. As you will see throughout this book,
APEX development consists of assigning values for properties which describe the various components
of your applications. The Oracle APEX environment does the rest for you.
For most components, you will use wizards to guide you through the process of creating those components.
After this initial creation, you have access to these properties to modify or shape the operation of
your applications, combining ease-of-use and productive maintenance in the same development tool.
All APEX development is data-driven. Tight integration with the Oracle database which hosts APEX
means that your applications will naturally flow from your data designs. For instance, APEX applications
automatically understand and implement relationships between tables, generating fully-featured applications
without additional programming overhead.
Oracle Application Express is an all-in-one development tool. You use the same techniques to create
forms, reports, and charts, as well as integrating these components together with navigation methods.
Your Oracle APEX environment gives you all you need to create virtually any application through an easy
declarative development process. However, APEX is also extensible, so you can use HTML to modify the
presentation of your APEX-generated client interfaces and Oracle’s standard PL/SQL procedural language
to supply additional logical operations on the back end.
When you start to use Oracle APEX, you will quickly discover that you are not alone. The Oracle
Technology Network (OTN) has a vibrant community of APEX users and experts, who regularly contribute
leading to continual improvement in best practices and technical solutions. In addition, you will
find a large collection of application packages that have already been created with APEX, readily available
for you to integrate in with your own applications.
Last, but certainly not least, Oracle Application Express gives you a way to protect the investment you
have in all those personal applications spread throughout your organization. You can upload data from
an Excel spreadsheet with a few simple mouse clicks, moving crucial information into an Oracle database.
You can even generate a complete application to interact with that data with a few more clicks,
completing the task in less than 10 minutes.
Oracle also provides a migration tool to help you move your Access applications to Oracle APEX, migrating
from a personal database with limited scalability to the world’s most powerful database.
Ready to start yet? This book contains all you will need to jump into the world of APEX-created applications.
But first, a little bit about the application you will be creating in the remainder of this book.
Langganan:
Postingan (Atom)
