At 09:13 AM 3/02/2003 +0000, Paul Morris wrote:

A reasonably good summary. I hope you don't mind if I annotate it a little!

>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])

NUMERIC(s,p) where s is scale and p is precision. Precision is not
optional. See also DECIMAL(s,p).

>· Change NUMBER to FLOAT

FLOAT for single precision, DOUBLE PRECISION for...yup.

>· Change CLOB to either BLOB or VARCHAR (maximum size for VARCHAR is
>32k)

Change CLOB to BLOB SUB_TYPE 1. BLOB without a sub_type defaults to BLOB
SUB_TYPE 0, which is untyped. Sub_type 1 for character blobs.

>· Delete TABLESPACE clause
>· Delete STORAGE clause
>· Ensure all columns appearing as PRIMARY KEY or UNIQUE
>constraints have NOT NULL constraint.

And don't define a column or column set as both PRIMARY KEY and UNIQUE.

>· Remove any schema qualification from table names (e.g.
>IBERIS.ACKCODES becomes ACKCODES)
>· Change any table or column names that are Interbase reserved
>words:

For IB 6, 7 and for Firebird: Change or double-quote any table or column
names that are InterBase reserved words. SQL-92 quoted identifiers are
supported.

>· Remove any table or column COMMENT ON definitions;

Enclose DDL comments between /* */ pairs.

>· Use ; instead of / to execute script.

Not sure what this means. Each statement in a script must be
terminated. The default terminator is a semicolon, but it can be any
string, provided it is declared using a SET TERM statement.
Within PSQL declarations, it is mandatory to SET TERM to something other
than a semicolon, because PSQL requires statements within procedure and
trigger declarations to be terminated with semicolons.

>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';

Cute.
Note that RTRIM is not an internal function: it requires that the IB_UDF
user-defined function library be present in the UDF directory and that the
function be declared to the database.

cheers,
Helen

>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
>
>
>
>
>
>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/