Skip to main content
J
Jatrix
Inspiring
March 31, 2009
Question

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

  • March 31, 2009
  • 2 replies
  • 607 views
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.
    This topic has been closed for replies.

    2 replies

    N
    Newsgroup_User
    Inspiring
    March 31, 2009
    > 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...

    --
    Adam
    G
    getoverit
    Inspiring
    April 2, 2009
    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
    R
    rmorgan
    Inspiring
    March 31, 2009
    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 as long as you dont rush it and take your time. 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. Nevertheless, you are certainly going in the right direction by leaving Access.
    http://www.adobe.com/products/coldfusion/editions/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices