Re: Need help importing / exporting a schema

On 3 jun, 06:01, "Blake McBride" <b..._at_mcbride.name> wrote:
> Greetings,
>
> I have an application which runs under Postgres & MSSQL and I have a
> new client who wants to run it under Oracle 10g. I have no experience
> with Oracle but all I need is some basic abilities in order to port
> the app. The things I need to do are as follows:
>
> 1. Import a database schema and data from a text file containing SQL
> statements. The exact format of the SQL isn't important because I
> can change my program to output whatever syntax. So basically I
> am trying to import from a text file which looks like:
>
> CREATE TABLE abc (
> ...
> ...
> )
> INSERT INTO abc .....
>
> and so on.

If that file would be called "dump.txt", simply start an SQL session and use
@dump.txt

>
> I would also create primary keys, indexes, foreign keys, etc.

Done from the same "dump.txt", I presume? The @dump.txt will take care of that - it allows for DDL (Data Definition Language) as well as
DML (Data Manipulation Language)
>
> 2. I need to be able to export the Oracle schema and/or data in a
> similar format as my import. Again, the exact (Oracle specific)
> format is not important but is must be a text file with SQL
> statements.
>

No standard tool - Oracle uses exp.exe, but the export is in an internal format.

> 3. I'd like to be able to easily drop all the data, tables, indexes,
> foreign keys, etc. with a few simple commands in order to be able
> to easily clear it all out and start over again. In other DB
> systems I usually drop the whole database and recreate it with two
> simple commands or clicks in the GUI.

A "Database" is not the same in Oracle - in Oracle speak, you drop a schema. As a schema is connected to a user, "drop user abc cascade;" will do the trick, as long as you are logged on with a priveleged account,
such as system.

>
> In order to avoid the cost while testing my app locally I am
> attempting to avoid a purchase and just use Oracle's express edition
> (XE). Of course the client has an enterprise license or some such. I
> fear my difficulties may be associated with the limitations of the XE
> version. If so, what is the minimum version I can buy to get the type
> of facilities I am looking for?

XE only has size limitiations, not functional (some options may not be available, though) Received on Mon Jun 04 2007 - 03:31:03 CDT