I use Oracle Database 10g Express Edition to …

I like Oracle DB XE, not only because it is free and has all the power of an Oracle database, but also because it comes with Application Express, or APEX (formerly HTMLDB). I plan to learn APEX and use it as an ad hoc application builder for such applications that can be shared among team members or on the company’s Intranet.

I installed the demo Web Services application that comes with APEX. For testing purposes, I wanted to allow my coworkers access to this demo application on my Oracle DB XE instance on my PC. Not a big deal, I just gave them the URL to the application, something like this: http://10.10.2.132:8080/apex/f?p=100. But, they could not connect to the application.

Well, I discovered that there was a setting called “Manage HTTP Access” under the Administration section of APEX. By default, this setting was set to “Available only from local server”. I switched it to “Available from local server and remote clients”. Now anyone who is on the same network as my PC, is able to access the application.

Of course, if you are exposing access to the whole Internet, you may think twice before doing this. But since I’m sharing the application only among my team members and only inside the company’s firewall, I was not paranoid about security.

Another thing I find useful is to use my Oracle DB XE instance as a tool to load CSV or XML data to another non-XE database (or even XE) through database links.

Assuming you have a non-XE Oracle database instance called ENTORA, and you have a table in a schema in the ENTORA database that you want to populate from data in a CSV file. There are many ways to do that, here is one way to do it using your local Oracle XE instance.

First, in XE, you create a database link to ENTORA. Something like:

create database link ENTORA
  connect to <username> identified by <password>
  using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
    (HOST=ENTORA_db)(PORT=1521))(CONNECT_DATA=(SID=ENTORA)))'
/

Then, using APEX, you load the CSV file into either a new table or an exiting table. Loading the data into an Oracle XE user table is just a few clicks away.

Once you have the data loaded in the table, you could do this (connected to user@XE):

insert into t@ENTORA select * from t;
commit;

I’m assuming that both t@ENTORA and t@XE have the same structure and DB users have the right privileges.

Poof! Your CSV file is loaded into a table on your non-XE Oracle database.

For more Oracle DB XE benefits, check out Lewis Cunningham’s article on OTN: Oracle Database 10g Express Edition: Not Just for Learners.

If you have downloaded and installed Oracle DB XE, what do you use it, or plan to use it, for?

5 Comments | Filed in Oracle, Tips | Tags: application-express, Security, tool, xe

---