How do you create a datasource that connects to an Oracle DB

[Jatrix]

I have coldfusion apps that currently link to an access database, I would like
to upgrade my database to oracle. How easily can this be done?
is there something special that has to be done for coldfusion to sync up with
oracle?
I have read that you need entrerprise edition, is this true?

Any assistance would be great.

[rmorgan]

No migration from one database to another is "easy". There is always a problem,
but with patience and some forethought it can be made to be as painless as
possible. One example is that you may have to change all your queries to work
on Oracle because of syntax differences. But here is a link a spec sheet on
CF's capabilities.
http://www.adobe.com/products/coldfusion/editions/

[Adam Cameron]

Post by Jatrix
I have read that you need entrerprise edition, is this true?

To use the Oracle JDBC drivers that CF ships with: yes one needs
enterprise. There's nothing to stop one downloading some other Oracle JDBC
drivers and using those ones, though.

As for porting the data from Access to Oracle: that's probably beyond the
scope of these forums. It shouldn't be too hard to write a "data pump" to
shift the data from Access to Oracle via CF though, if a bit of googling
doesn't turn up a better approach though.

As rmorgan said, though, Oracle SQL is different from Access SQL, so you'll
need to revisit your queries and translate them too. It might be a good
opportunity to shift your DB layer to Reactor or Transfer (or Hibernate) or
something that is DB neutral...

[getoverit]

If your Access tables are small, you could probably go into the Access IDE and
create link tables to the new Oracle tables and then use Access to move data
into them. Using VBA, you could do practically any kind of manipulation you
need to to convert data types, etc.

For a more "industrial-strength" solution, it might be better to export all
the data you need from Access and then use SQL Loader to populate the Oracle
tables. Also, other languages that provide ADO/ODBC/JDBC type support could be
used. If there's something like that that you're familiar with, go with it.

One other little tip: Write CREATE TABLE, CREATE VIEW, etc. for each of the
Oracle objects you will need. It might be tempting to go into Enterprise
Manager and use it, or some type of wizard tool to do that, but you'll pay the
price if 1) you ever need to make changes (probabvly will happen) or 2) you
have to migrate to another Oracle instance, such as a production deployment.

HTH