PL/SQL developer from allround automations
http://www.allroundautomations.nl/index.html has facilities for
exporting both DDL and data from oracle databases. The data can be
exported as insert statements, which should work unchanged against
firebird. The DDl will however require some tweaking, see below for some
of the major differences:

Table Definitions
-----------------
� Change VARCHAR2 to VARCHAR
� Change NUMBER(n,[n]) to NUMERIC(n,[n])
� Change NUMBER to FLOAT
� Change CLOB to either BLOB or VARCHAR (maximum size for VARCHAR
is 32k)
� Delete TABLESPACE clause
� Delete STORAGE clause
� Ensure all columns appearing as PRIMARY KEY or UNIQUE
constraints have NOT NULL constraint.
� Remove any schema qualification from table names (e.g.
IBERIS.ACKCODES becomes ACKCODES)
� Change any table or column names that are Interbase reserved
words:
� Remove any table or column COMMENT ON definitions;
� Use ; instead of / to execute script.

Sequences
---------
These are known as generators under Interbase:
CREATE GENERATOR .....
GEN_ID(generator_name,n) - this increments generator_name by n and
returns the new value;
The following SQL will create a script to create a generator for every
primary key
select DISTINCT 'CREATE GENERATOR '||RTRIM(i.rdb$field_name)||'_SEQ;;'
from rdb$relation_constraints RC,
rdb$index_segments i
where rc.rdb$index_name = i.rdb$index_name
and rc.rdb$relation_name not like 'RDB%'
and rc.rdb$constraint_TYPE = 'PRIMARY KEY';

Triggers
--------
Remove OR REPLACE from definition
Trigger definition format changes from

CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE INSERT ON <table name> FOR EACH ROW
to
CREATE TRIGGER <trigger_name> FOR <table_name>
BEFORE INSERT AS
Change :OLD and :NEW to OLD and NEW respectively.


Hope this helps

Paul

-----Original Message-----
From: Todd Jonker [mailto:tvj@...]
Sent: 24 January 2003 15:30
To: IB-Conversions@yahoogroups.com
Subject: Re: [IB-Conversions] How to do basic Oracle-to-FB conversion


Claudio, I guess I wasn't as clear as I thought I was. Please forgive
my
lax terminology, I am not a DBA, just a developer with enough knowledge
to
be dangerous. :-)

We need to clone both the table structures (= metadata?) and the data.

The vendor of this legacy product is resistant to giving us (a
third-party
developer) the DDL, so we have no scripts to process. If they do give
us
the DDL, we will still need to get the data across somehow, and it's
terribly unclear to me how to do that via an intermediate file format.
So
copying DB->DB directly seems to be the easiest route. (Note that this
should be a one-time process.)

The HSQL tool used JDBC to read the source database's metadata, create
similar tables in the target, and then copy all the records over.
That's
what I'm looking for now. I will probably try the HSQL tool again, but
was
looking for something a bit more mature and reliable.

Again, there's no stored procedures or other programmatic SQL, so I
think
there's not much more to worry about that the metadata and data.

Thanks,
.T.

On 1/24/03 3:56 AM, cvalde@... wrote:

> ----Original Message----
> From: Todd <tvj@...> [mailto:tvj@...]
> Sent: Jueves 23 de Enero de 2003 17:03
>>
>> This is all well and good, except for the fact that I need to
>> bootstrap the process by cloning the oracle schema (and data) into
>> Firebird.
>>
>> Can anyone suggest a tool that can do this? A year or so ago, I used
>> a simple migration tool that was part of the HypersonicSQL
>> distribution, but it was a bit clumsy and had a hard time copying
>> tables in the proper order. I'm hoping that Firebird/InterBase
>> devlopers can suggest something appropriate.
>
> Do you want to clone only metadata?
> Well, www.datanamic.com has a tool that can reverse engineer almost
any
> script to the format used by that CASE tool. Then, the CASE can
generate a
> script for other db engine.
> For USD 149 + 41, it's not bad. I've used the two tools for over a
year for
> my needs and it's enough. I don't need the complexity of ER/Studio and
in
> this way, I don't need a live connection to a db. I can reverse
engineer any
> supported engine's script without having that engine installed. Hence,
> someone could give me an Oracle script (I don't have Oracle), I
convert it
> with ImportER Scripts and then load the resulting file in Dezign.
Finally, I
> tell Dezign to generate the script for FB. Done. (I'm not associated
with
> Datanamic; I'm only a happy customer.)
>
> C.


To unsubscribe from this group, send an email to:
IB-Conversions-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/






This mail has been scanned for viruses.






******************************
Confidentiality Notice
******************************
The information contained in this E-mail, and any attachments, is intended for the named recipients only. It may contain confidential and/or privileged information.

If you are not the intended recipient, you must not copy, distribute, or take any action in reliance on it. Any views expressed do not necessarily reflect the views of the company.

If you receive this E-mail by mistake, please advise the sender by using the reply facility in your E-mail software and then delete it.

Please note that whilst the company takes steps to protect against viruses it cannot accept liability for any virus accidentally transmitted.

Aralia Systems Ltd, 13 North Parade, Horsham, West Sussex
RH12 2BT, United Kingdom. Tel +44 (0)1403 240303