msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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?


Filed in Oracle, Tips on 03 Apr 06 | Tags: , , ,


Reader's Comments

  1. |

    I use 10G XE primarily on my laptop to have a local database present when I want to try some stuff out. I would like to have some more features, such as VPD, available though. But it’s a nice sandbox environment ;-).

  2. |

    Yes, I use 10g XE to try and test things out too. I like the fact that I am the DBA and the developer at the same time.

  3. |

    Hi, on my corporate laptop i sweared no native db install on it! i wanted to keep my system ‘clean'; So whenever a database runs it runs under VMware on Linux/Win2003; But with XE i changed that. Some small installation steps – no db control – simply easy. XE covers allmost all important aspects of the 10G. Thats great!!

    Karl

  4. |

    I’m still trying to figure out how to get my select lists to work in XE. It’s driving me crazy!

  5. |

    Monitor batch jobs with Oracle XE…

    Since installation, the Oracle 10g XE on my desktop has mostly been used for quickly testing how database stuff works. Last week I finally got around to digging a bit deeper into the HTMLDB/Application Express part. Lets say you have batch jobs on your…